Faster way to loop through two ranges

[Guest]

I need a faster way to loop through two columns. I have two columns on two
separate worksheets, and I need to see if a cell value in column1 matches any
cell value in column2. I have 50,000 cells in Column1 and 230 cells in
column2. Using the Sub below takes me 3 minutes to loop through all cells,
and I need a way to cut down a chunk of the processing time.

Anyone has a better idea?

Thanks,

YH

Sub Trial()

Dim dLastR As Long
Dim sLastR As Long
'sWS is the source worksheet
'dWS is the destination worksheet
Dim sWS As Worksheet
Dim dWS As Worksheet
Dim s_RowCt As Long
Dim d_RowCt As Long

Set sWS = Worksheets("VF45 Pivot Table")
Set dWS = Worksheets("DF TMP")

sLastR = sWS.Cells(sWS.Rows.Count, "A").End(xlUp).Row
dLastR = dWS.Cells(dWS.Rows.Count, "A").End(xlUp).Row

For d_RowCt = 2 To dLastR
For s_RowCt = 1 To sLastR
If dWS.Cells(d_RowCt, 8).Value = sWS.Cells(s_RowCt, 1).Value Then
dWS.Cells(d_RowCt, 2).Value = "Y"
Exit For
End If
If s_RowCt = sLastR Then
dWS.Cells(d_RowCt, 2).Value = "N"
End If
Next
Next

end Sub

 

[Tim Williams]

Read each range into an array and loop through the arrays.
Or use the MATCH() worksheet function.

Or both.

Tim

 

[Norman Jones]

Hi YH,

In addition to Tim's excellent suggestions, turn off ScreenUpdating,
automatic calculation and PageBreak display at the start of the procedure
and restore the required settings at the end.

 

[Guest]

YH,

Since you loop writes a "y" or "n" result over and over, the display
updating alone is probably holding you back.

Try adding these lines before and after your loops.

Application.ScreenUpdating =False
Application.Calculation =xlCalculationManual
ActiveSheet.DisplayPageBreaks = False

your looping code here

Application.ScreenUpdating =True
Application.Calculation = xlCalculationAutomatic

Roy

 

[Tim Williams]

Made a few changes for my test data, but this should give you an idea.

Tim

'***************************************
Sub Trial2()

Dim dLastR As Long
Dim sLastR As Long
Dim sWS As Worksheet
Dim dWS As Worksheet
Dim arrVals

On Error GoTo haveError

Set sWS = Worksheets("long")
Set dWS = Worksheets("short")

sLastR = sWS.Cells(sWS.Rows.Count, "A").End(xlUp).Row
arrVals = Application.Transpose(sWS.Range(sWS.Cells(1, 1),
sWS.Cells(sLastR, 1)))

dLastR = dWS.Cells(dWS.Rows.Count, "A").End(xlUp).Row

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

For d_RowCt = 2 To dLastR

If Not IsError(Application.Match(dWS.Cells(d_RowCt, 1).Value,
arrVals, 0)) Then
dWS.Cells(d_RowCt, 2).Value = "Y"
Else
dWS.Cells(d_RowCt, 2).Value = "N"
End If

Next

haveError:
'make sure to turn these back on whatever happens....
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub
'*********************************************

 

[Guest]

I modified the code to use match function and turned off screen updating, but
it still takes about 3 minutes to loop through. Would find(What:=) save me
some time? Not sure about the exact syntax of find(what:=) to fit with my
code.

Need help. Thanks!

YH


Sub Trial ()

Dim dLastR As Long
Dim sLastR As Long
Dim sWS As Worksheet
Dim dWS As Worksheet
Dim s_RowCt As Long
Dim d_RowCt As Long
Dim sRange As Range
Dim C As Variant

Set sWS = Worksheets("VF45 Pivot Table")
Set dWS = Worksheets("DF TMP 2")

sLastR = sWS.Cells(sWS.Rows.Count, "A").End(xlUp).Row
dLastR = dWS.Cells(dWS.Rows.Count, "A").End(xlUp).Row

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
ActiveSheet.DisplayPageBreaks = False

Set sRange = sWS.Range("A5:A" & sLastR)
For d_RowCt = 2 To dLastR
C = Application.Match(dWS.Cells(d_RowCt, 8).Value, sRange, 0)
If IsError(C) Then
dWS.Cells(d_RowCt, 2).Value = "N"
Else
dWS.Cells(d_RowCt, 2).Value = "Y"
End If
Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[Guest]

Tim,

I tried your suggestion by reading column2 into an array, and I got the
results within 10 seconds! That is huge improvement. Thanks.

By the way, I thought the syntax to read a range into an array is:

arrVals = sWS.Range(sWS.Cells(5, 1), sWS.Cells(sLastR, 1)).Value

You used:

arrVals = Application.Transpose(sWS.Range(sWS.Cells(5, 1), sWS.Cells(sLastR,
1)))

What is the difference?

Thanks!
YH

 

[Bob Phillips]

Normally, when loading an array from a range, you get a 2 dimensional array
(rows and columns). By transposing it, it turns it to a single dimension
array for the rows.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thanks, Bob.

YH