make sure you turn off calculation and screen updating before running your code:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

' your code

Application.ScreenUpdating =True
Application.Calculation = xlCalculationAutomatic


--


Gary


"MJKelly" wrote in message
...

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