Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seo , Search Engine Optimizer , Seo Search engine Optimization , search engine optimization services, SEO Consulting | Excel Worksheet Functions | |||
Seo , Search Engine Optimizer , Seo Search engine Optimization , search engine optimization services, SEO Consulting | Excel Discussion (Misc queries) | |||
Seo , Search Engine Optimizer , Seo Search engine Optimization , search engine optimization services, SEO Consulting | Setting up and Configuration of Excel | |||
Seo , Search Engine Optimizer , Seo Search engine Optimization , search engine optimization services, SEO Consulting | Links and Linking in Excel | |||
Seo , Search Engine Optimizer , Seo Search engine Optimization , search engine optimization services, SEO Consulting | Charts and Charting in Excel |