I'm hoping someone can help me optimize this code. It works, just rather slowly (it took ~7 minutes to run against a rather small data set). Essentially, I'm comparing data
from one workbook (sI), to data in anotherworkbook (dI). Where the values exist inboth workbooks, I’m bringing over certain values from sI; to dI. In order for the match to work, I have to getrid of the spaces in a column on the sI workbook, which seems to take quite awhile.
Suggestions on how to make his cod run faster? I'll have to repeat it on another data set, so I really don't want this code to take 15 minutes to run.
from one workbook (sI), to data in anotherworkbook (dI). Where the values exist inboth workbooks, I’m bringing over certain values from sI; to dI. In order for the match to work, I have to getrid of the spaces in a column on the sI workbook, which seems to take quite awhile.
Suggestions on how to make his cod run faster? I'll have to repeat it on another data set, so I really don't want this code to take 15 minutes to run.
Code:
Sub CompIssueData()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Dim d, s As Workbook
Dim i, j As Long
Dim Rng, cCell As Range
Dim RngList As Object
Set d = ThisWorkbook
Set dI = ThisWorkbook.Sheets("Issues") 'Has about 500 records
Set s = Workbooks.Open("C:\Users\NBKDA6K\Desktop\Daily Inventory\Input Files\BKR123")
Set sI = s.Sheets("BKR123_BKInventory") 'Has about 13K records
Set RngList = CreateObject("Scripting.Dictionary")
dlR1 = dI.Range("B" & Rows.Count).End(xlUp).Row
sLR1 = sI.Range("A" & Rows.Count).End(xlUp).Row
Set cCell = sI.Range("H2:H" & sLR1)
cCell.Value = Application.Trim(cCell)
For Each Rng In sI.Range("A2", sI.Range("A" & sI.Rows.Count).End(xlUp))
If Not RngList.Exists(Rng.Value & "|" & Rng.Offset(0, 7)) Then
RngList.Add Rng.Value & "|" & Rng.Offset(0, 7), Rng
End If
Next
For Each Rng In dI.Range("B2", dI.Range("B" & Rows.Count).End(xlUp))
If RngList.Exists(Rng.Value & "|" & Rng.Offset(0, 5)) Then
dI.Range("F" & Rng.Row).Value = sI.Range("J" & Rng.Row).Value
dI.Range("H" & Rng.Row).Value = sI.Range("AC" & Rng.Row).Value
End If
Next
RngList.RemoveAll
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub