暂停VBA循环，直到重新计算工作表 [英] Pause VBA loop until sheet is recalculated

问题描述

我搜索了Google直到第10页，但找不到相应的解决方案.我在VBA中有一个循环，但希望它在继续进行操作直到重新计算工作表之前等待.

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.

大多数人建议使用 DoEvents .但是，这对我不起作用.

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 

推荐答案

使用 Do While – If 是没有用的.对于 If ，该代码将评估一次，然后继续前进. Do While 将使其循环播放，直到满足条件为止.

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

这篇关于暂停VBA循环，直到重新计算工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！