Hi,
Loooking to make this macro faster
Code
Sub STEP9()
Dim Val As String, wb1 As Workbook, wb2 As Workbook, srcWS As Worksheet, desWS As Worksheet
Dim i As Long, v1 As Variant, v2 As Variant, rngList As Object
Dim lastRow As Long
Set wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\HotStocks\1.xls")
Set wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\HotStocks\AlertCodes.xlsx")
Set desWS = wb1.Worksheets(1)
Set srcWS = wb2.Worksheets(1)
lastRow = desWS.Range("B" & desWS.Rows.Count).End(xlUp).Row
If lastRow = 2 Then
ReDim v1(1 To 1, 1 To 1)
v1(1, 1) = desWS.Range("B2").Value
Else
v1 = desWS.Range("B2:B" & lastRow).Value
End If
v2 = srcWS.Range("A2", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp)).Resize(, 2).Value
Set rngList = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(v1, 1)
Val = v1(i, 1)
If Not rngList.Exists(Val) Then
rngList.Add Key:=Val, Item:=i + 1
End If
Next i
For i = 1 To UBound(v2, 1)
Val = v2(i, 1)
If rngList.Exists(Val) Then
desWS.Cells(rngList(Val), 9) = v2(i, 2)
End If
Next i
Application.ScreenUpdating = True
wb1.Save
wb2.Save
wb1.Close
wb2.Close
End Sub
Display More
Thnx