CODEWITHSUNDEEP
excelvbaloopsrefresh
freddy888
freddy888

Reputation: 1010

Pause VBA loop until sheet is recalculated

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 

NumRows = Range("CF16", Range("CF16").End(xlDown)).Rows.Count 

Range("CF16").Select 

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

Does anyone know a different solution than this one?:

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

Upvotes: 2

Views: 4775

Answers (1)

cyboashu
cyboashu

Reputation: 10433

Use Do While – If is of no use. For If the code will evaluate once and then move forward. Do While will keep it looping until the condition is satisfied.

Do
    DoEvents
Loop While Not Application.CalculationState = xlDone

Upvotes: 4

Related Questions