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


"YH" wrote:

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