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

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

That should have read...

replace SUMIF with SxUMIF
-then-
replace SxUMIF with =SUMIF
--
Jim Cone


"Jim Cone" XX
wrote in message
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

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