Calculations not keeping up with macro

[xl@lf]

Hello,

I have a spreadsheet with 51 tabs. Tab_1 is a summary of data
contained on the remaining 50 sheets (such as averages and sums). A
macro iterates through values of 1 - 100. During each iteration a
variable with dependents changes on each of the 50 sheets and
resulting data for each iteration is output on the summary tab (output
such as the averages and sums for each iteration).

The macro works great when I step through it, the problems occur when
I run it at full speed. The output data at each iteration does not
keep up with the macro, thus the outputs are inaccurate. It's like
the macro runs faster than all the calculations are performed and
output.

Any suggestions to help alleviate this challenge would be greatly
appreciated.

Thanks!!

 

[Tim Williams]

Try adding

Application.DoEvents

at the end of each loop (or wherever you're triggering a recalculation).

Tim

 

[xl@lf]

I tried the Doevents and it appeared to put the spreadsheet in manual
calculation mode. The macro processed at a blazing speed and all the
output data was identical. When the macro was finished I pressed F9
and it updated all the values. Since I'm constantly updating values
manual calculations won't work for me in this macro. Thanks Tim.

 

[Tim Williams]

I've never seen DoEvents put the application in manual calculation mode.

In any event, you might be better off putting Excel in manual calculation
mode at the start of the macro, and calling Application.Calculate just
before you want to pick up any results. Don't forget to turn it back to
automatic at the end of the routine.

Tim