Pausing VBA execution to allow for full recalculation

[Mike Thomas]

Would like to slow down processing so full recalculation can occur before moving to the next step. Following is my subroutine and an indication of where I would like the pause to occur:

Dim dlr As Long
Application.ScreenUpdating = False 'turn windowsoff
Do
Range("AS9") = ActiveCell
ActiveCell.Offset(, 0) = ActiveCell.Value
ActiveCell.Offset(, 2).Value = Range("CP57").Value
ActiveCell.Offset(, 3).Value = Range("CQ57").Value
ActiveCell.Offset(, 4).Value = Range("CR57").Value
ActiveCell.Offset(, 5).Value = Range("CS57").Value
ActiveCell.Offset(, 6).Value = Range("CT57").Value
ActiveCell.Offset(, 7).Value = Range("CU57").ValueActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell)
Beep
Application.ScreenUpdating = True 'turn windowson
End Sub

Thanks for your help.

 

[joeu2004]

Would like to slow down processing so full recalculation
can occur before moving to the next step.

Unless you set Manual calculation mode, what makes you think recalculation
is not completed before returning control to VBA after each assignment to a
Range variable?

Do you have some special Excel objects, e.g. tables?

I do know that assignment to some parts of Chart objects is not completed
before returning control to VBA. In such cases, it seems to be sufficient
to call DoEvents.

Alternatively, you could cause a recalculation cycle by invoking the method
Application.Calculate. I presume control does not return to VBA until the
recalculation cycle is completed.

Following is my subroutine and an indication of where I
would like the pause to occur

Aside.... I believe the algorithm can be improved significantly. Try the
following (untested).

Dim r as Range
Application.ScreenUpdating = False
set r = ActiveCell
Do
Range("AS9") = r
r = r.value ' no-op; not needed?
r.Offset(0,2).Resize(1,6) = Range("CP57:CU57")
DoEvents ' why need?
Set r = r.Offset(1, 0)
Loop Until IsEmpty(r)
r.Select ' not needed?
Beep
Application.ScreenUpdating = True
End Sub