Find/Replace randomly taking forever

[arooney88]

Hi everyone! Thanks for checking into my issue and I appreciate any responses I'll get.

I have a database that has thousands of cells that I need to find/replace monthly because the formula needs to reference the correct month's data folder from another workbook every month.

My issue is that I'll do a find/replace on 1 tab and it'll do 20,000 changes in about 10 seconds, and then I'll do THE EXACT SAME THING on the 2nd tab immediately after the 1st find/replace, and it will take 30 minutes!

I'm wondering if anyone else has had similiar problems and what I can do to try to make it consistent. It just seems to slow down randomly and I can't figure out what I'm doing wrong.

Any help would be GREAT!

Thanks!

 

[Rick Rothstein]

Can you show us some examples of what is in your cells and what you are replacing in them?

 

[arooney88]

This is pretty standard.

='http://hcfg-portal.homestead.com/sites/QA/QA Workshop/Mar/[Alex QA.xlsm]Quality'!A4

This formula would be in say 20,000 cells. So I'll find/replace /Mar/ with /Apr/ and on the first tab it'll take 10 seconds. Then when I do the same exact thing on my 2nd tab, it will take 20-30 minutes. It seems to switch off every other time going faster then slower then faster then slower.

 

[arooney88]

It seems to do this quick/slow calculation for every other time I use Find/Replace. I'm not sure why and I can't figure out how to speed it up at all.

 

[Rick Rothstein]

It seems to do this quick/slow calculation for every other time I use Find/Replace. I'm not sure why and I can't figure out how to speed it up at all.

What happens if you change the Calculation Mode to Manual before you do your find/replaces? (Don't forget to set it back to Automatic when done.)