Find optimization
 

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.

Find optimization
 

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.

Find optimization
 

I have some option market prices and I also have a VBA model based on 5
parameters which calculates the prices of options.

I want solver to find those 5 optimal parameters which minimizes the
differences between the observable market prices and my calculated
theoretical prices! There must be a away to set solver `s target cell"s" to
be the differences between individual options? Ahy help? Is there a VBA code
I could use to perform the minimization problem?

I have tried setting as target the sum of the squared differences but that
doesnt find the opimal parameters either. Thx


"JLGWhiz" wrote:

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.