Hi, The code below loops through each cell in one column of data and
finds the same value in another column (on another spreadsheet). This
works fine for a 100 cells, but the final version will check 600 rows
in seven worksheets, and this takes an age. How can I do this more
efficiently? Would it be better to use an array (not sure how to do
this), and I assume that when the correct value is found, the
remaining cells are still searched? Could I use a function which finds
the match and so skips checking the rest of the column?


For Each r In Workbooks("02 - Scheduler
Plan.xls").Worksheets(SchedulerDayName).Range("B2: B100").Cells
If IsNumeric(r.Value) Then
For Each c In Workbooks("00 -
MDS.xls").Worksheets(MDSDayName).Range("C4:C1000") .Cells
If c.Value = r.Value Then
c.offset(0, 2).Resize(1, 145).Copy
r.offset(0, 4).PasteSpecial xlPasteValues
End If
Next c
End If
Next r

hope you can help.

regards,
Matt