Recalc Time
 

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

Recalc Time
 

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.

--
Regards,
Tom Ogilvy


"CLR" wrote in message
...
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

Recalc Time
 

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" wrote:

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.

--
Regards,
Tom Ogilvy


"CLR" wrote in message
...
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

Recalc Time
 

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.

--
Regards,
Tom Ogilvy


"CLR" wrote in message
...
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" wrote:

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.

--
Regards,
Tom Ogilvy


"CLR" wrote in message
...
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

Recalc Time
 

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" wrote:

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.

--
Regards,
Tom Ogilvy


"CLR" wrote in message
...
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" wrote:

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.

--
Regards,
Tom Ogilvy


"CLR" wrote in message
...
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

Recalc Time
 

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

--
Regards,
Tom Ogilvy


"CLR" wrote in message
...
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" wrote:

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.

--
Regards,
Tom Ogilvy


"CLR" wrote in message
...
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" wrote:

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.

--
Regards,
Tom Ogilvy


"CLR" wrote in message
...
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