暂停VBA循环，直到重新计算工作表

问题描述

我搜索了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循环，直到重新计算工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！