More efficient alternative to Find-Replace Loop?

[Paul]

I was tasked with looking through a 10,000 row by 20 column spreadsheet for 628 different ID Codes and replacing them with their Descriptions. The ID Codes could appear individually in any of these cells (200,000 cells!).

The list of ID's and Descriptions hardly ever changes, so I decided to create the two-dimensional array as part of the find-replace macro shown below (only a few lines of each dimension are shown).

My question isn't related to that (although if you can suggest a better alternative please do). The main "work" of the macro is the loop at the end of the array declarations, which essentially loops through all 628 ID codes in array dimension 1 and does a Find-ReplaceAll with its corresponding Description from array dimesion 2.

This task, manually, could take days. My macro has whittled the task down to 2.5 minutes on a 5-year-old laptop, but I was hoping one of the gurus might suggest an even better method than 628 loop iterations. If not, so be it, the end users will appreciate what I've done and then have to find something to do with their "free time."

Please Login or Register  to view this content.

Happy Excelling!

[shg]

No wonderful suggestion here.

If you had more iterations, I'd suggest doing a dummy Find that sets all of the saved parameters of the Replace, and then pass just What and Replacement arguments in the loop.

Similarly, two parallel arrays (Find and Replace), instead of one 2D array, might index slightly faster.

Still, for 628 iterations, I think the difference would be negligable if even measurable.

This task, manually, could take days. My macro has whittled the task down to 2.5 minutes on a 5-year-old laptop

Wow, this software stuff might catch on, huh?

[Leith Ross]

Hello Paul,

This method uses the Dictionary Object to make the comparisons. A variant array holds the cell values and is load using a block transfer. On my machine, this takes only a about 1 second to make 200,000 (10,000 rows x 20 columns) replacements.

I have 3 macros. One to create a list of 628 IDs and descriptions, one to lfill "Sheet1" with random IDs, and the last one to make the comparisons and replace the data. I will include them all here. "Sheet2" is used for the ID and descriptions and "Sheet1" is the test sheet. The total time is calculated at the end of the macro. You see the total time you can use a break point or Debug.Print or copy it to cell.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Paul]

Thanks, SHG and Leith. I'll play with this later today and see how it goes. Happy Groundhogs Day!

[Paul]

Leith... amazing. With some adjustments I have the entire macro (creating array, adding it to new sheet, creating DSO, replacing values) running in about 3 seconds.

I'll definitely have to use DSO more in the future. Thanks a ton!

Adjusted code shown below. Only thing I noticed is that for cells that weren't replaced, if they were numeric format they were converted to currency format. To get around that I just formatted the entire replacement range as text ahead of the replacement step.

Please Login or Register  to view this content.

[shg]

Good job, Leith!