Recalc Time

[Guest]

Hi All...........
Could anyone please tell me that when I send a formula to a cell of an
ActiveSheet with VBA and Excel does a "Recalc", is it only Recalculating that
one sheet or every sheet in the workbook?......If it does them all, how might
I be able to turn off the Recalc at the beginning of my macro for "all
sheets" and then only turn it on for the ONE SHEET I want to recalculate with
each segment of the macro and then turn the whole thing back on when the
macro finishes?

TIA
Vaya con Dios,
Chuck, CABGx3

 

[Tom Ogilvy]

Possibly set calculation to manual(application.Calculation = xlManual)


then use the
Activesheet.Calculate

command in your macro after you "send a formula to a cell".

at the end, set application.Calculation = xlAutomatic

might work.

 

[Guest]

Thanks Tom..........seemed like a really good suggestion, but I couldn't seem
to realize any improvemet. I tried shuffeling things around but nothing
helped. The "application.calculation = xlmanual" didn't seem to shut
anything off, or rather if it did, then one of the outside macros I called in
to place the formulas before I wanted the "Activesheet.calculate" must have
reset it somehow. At any rate, by the time it got to "activesheet.calculate"
it had already done the recalc and so just did it again.......the whole
process actually took longer........

Odd request, thanks for trying.........I reckon I'm stuck with the long
processing time.
Vaya con Dios,
Chuck, CABGx3

 

[Tom Ogilvy]

You could put in your formulas as String s

activecell.value = "ZZXXSum(A1:B1000)"

then after they are all entered, use the replace command

columns(3).Replace "ZZXX", "="

in your code.

 

[Guest]

Hmmmm.......interesting.........what I am doing is filling a 1200 cell
NamedRange with VLOOKUP formulas, which when using the TEXT method all fill
in with the same text, .....in other words do not "step" over to be the
correct cell addresses for the next cell........the find and replace worked
fine, but i wound up with the same formula in all 1200 cells........and the
recalc seemed to take the normal long time anyway.

Thanks for the tip tho, I might can use it somewhere else..........
Vaya con Dios,
Chuck, CABGx3

 

[Tom Ogilvy]

As Clint Eastwood/Dirty Harry said, "A man's got to know his limitations!"
<g>