ExcelBanter - Find & Replace Very Slow in VB But Not Manually

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Find & Replace Very Slow in VB But Not Manually (https://www.excelbanter.com/excel-programming/382146-find-replace-very-slow-vbulletin-but-not-manually.html)

Find & Replace Very Slow in VB But Not Manually

Hi All

I have VB logic that massages a lot of data. At one point I do a find
and replace call:

Range("C5:DI186").Select
Selection.Replace What:="SUMIF", Replacement:="=SUMIF",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False

to activate some formulaes that were dormant until this time.

When the code runs this search and replace it takes a *very* long time
to execute. I have calculation set to manual and screen updating
turned off.

The weird thing is when I run my macro to the line just before the
above code (terminated with an inserted "End") for find and replace
and then run the find and replace manually - it takes no time at all -
about 1/20th of the time that my code is taking to do the same thing.

I am sure that this must have something to do with the different
settings I have turned off or on in my VB code but I have only really
set calculation to manual and screen updating off.

Any ideas why the code is running so slow? Any thoughts apprciated.

Chrisso

Find & Replace Very Slow in VB But Not Manually

Something to experiment with...
Run it twice.
replace SUMIF with xxSUMIF
-then-
replace xxSUMIF with =SUMIF
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

"Chrisso"
wrote in message
Hi All
I have VB logic that massages a lot of data. At one point I do a find
and replace call:

Range("C5:DI186").Select
Selection.Replace What:="SUMIF", Replacement:="=SUMIF",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

to activate some formulaes that were dormant until this time.
When the code runs this search and replace it takes a *very* long time
to execute. I have calculation set to manual and screen updating
turned off.
The weird thing is when I run my macro to the line just before the
above code (terminated with an inserted "End") for find and replace
and then run the find and replace manually - it takes no time at all -
about 1/20th of the time that my code is taking to do the same thing.
I am sure that this must have something to do with the different
settings I have turned off or on in my VB code but I have only really
set calculation to manual and screen updating off.
Any ideas why the code is running so slow? Any thoughts apprciated.
Chrisso

Find & Replace Very Slow in VB But Not Manually

Hi Jim

Thanks for the suggestion. I tried this out by there was no noticeable
decrease in execution time.

I cannot exactly understand why this takes so long but I guess it is
all the calculations that are required when I ressurect my dormant
formulaes - there are 14560 of them!

However it should only need to calculate them once which is after the
search and replace as I have calculation turned off.

Oh well - I might have to rewrite my whole approach.

Chrisso