Pause VBA loop until sheet is recalculated

[Megalodn]

Hi All,

I searched google until page 10, but could not find a solution for this.
I have a loop in VBA, but want it to wait before proceeding until the sheet has recalculated.
What most people suggest is employ DoEvents. However, that does not work for me.

Here is my code so far, which does not wait until the sheet calculated:

Sub Replaceifrebalance()
Dim x As Integer

' Set numrows = number of rows of data.
NumRows = Range("CF16", Range("CF16").End(xlDown)).Rows.Count
' Select cell a1.
Range("CF16").Select
' Establish "For" loop to loop "numrows" number of times.
For x = 1 To NumRows
If Range("CF" & x).Value > 0 Then
Range("AW15:BF15").Select
Application.CutCopyMode = False
Selection.Copy
Range("AW1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AW" & 1 & ":BF" & 1).Copy Worksheets("Timeseries").Range("BI" & x & ":BR" & x)

Application.Calculate
If Not Application.CalculationState = xlDone Then
DoEvents
End If
End If

Next

End Sub



Any help is greatly appreciated.

[Jan Karel Pieterse]

The Calculate method in VBA is synchronous, so should be done before the next statement is executed. What precisely is your problem?

[Megalodn]

The problem is that the loop does not wait until the calcution is finished. For example,

I have a loop that goes through this column:
0 1
1 1
1 1

If first column is >0 it should change the 1 to 2.

After the copy paste and AFTER all calculations are finished it should look like this (note that the rows are interconnected between each other and when 1 becomes 2 the 0 from the 1 from the row below should become 0, too.:
0 1
0 2
0 1

And the loop should not touch the LAST row (the one that turned from 1 to 0) because it is <=0. However, clearly the code is too fast and touches this row and treats it as it is still 1:
0 1
0 2
0 2


The second 2 is my problem as it should stay one.

[Jan Karel Pieterse]

If you step through the code, do the values in the cells behave as expected?

[Jan Karel Pieterse]

What formulas are in CF and its precedents? Any User Defined Functions?

[Megalodn]

Yes, they behave fine. No user defines formula, instead very simple sums and plus minus, but all dependent on each other (a row depends on the precedent row and so on...).

However, the sheet works fine if I do all steps manually and the macro works fine as well - except for the mentioned issue of course.

[Paul_Hossler]

not tested, but maybe just turning off calculation until the end




Sub Replaceifrebalance()
Dim x As Integer

' Set numrows = number of rows of data.
NumRows = Range("CF16", Range("CF16").End(xlDown)).Rows.Count

Application.Calculation = xlCalculationManual

' Establish "For" loop to loop "numrows" number of times.
For x = 1 To NumRows
If Range("CF" & x).Value > 0 Then
Range("AW15:BF15").Copy

Range("AW1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("AW" & 1 & ":BF" & 1).Copy Worksheets("Timeseries").Range("BI" & x & ":BR" & x)

End If

Next

Application.CutCopyMode = False
Application.Calculation = xlCalculationManual
Application.Calculate

End Sub