It would probably speed things up if you could restrict you range to Find in
to one or two columns rather than the entire used range. Also, when
practicable, set your find statement in an IF...Then statement to exclude as
many cell checks as possible like If retRange < "" Then
Set blah, blah...
However, if your search criteria might be found randomly throughout the used
range, then you might be stuck with the time factor.

"adimar" wrote:

I have some code that searches for a string in two other workbooks.
I set ranges like this:
Set retRange = ActiveWorkbook.Sheets("RawData").UsedRange
Set rLegacyProd1 = Workbooks("Legacy Shipped -
Prod1.xls").Worksheets("RawData").UsedRange
Set rLegacyProd2 = Workbooks("Legacy Shipped -
Prod2.xls").Worksheets("RawData").UsedRange

I use €œfind€� to look for the value, like this: If first character in
sernoStr matches a certain pattern then I search in one workbook or the other:
Set mCell = rLegacyProd1.Find(sernoStr, LookIn:=xlValues)

Since the workbooks Im searching in are pretty large (~40K records) the
macro takes a long time to run.

Im looking for suggestions for speeding up the code.


Thank you.