Megalodn
08-17-2016, 12:03 AM
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.
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.