vba 在循环中更改 For 循环的长度

Change length of For loop while in the loop

提问by H0ckeyfr33k99

I have a bunch of code that goes through a set of data and then finds the row where the data goes to the next value step (5 unit increments). When this location is found, 5 rows are inserted and in those rows, the STDEVP, MIN, MAX, and AVERAGE are calculated over the range of that block of data. After this is entered, the loop continues to go through the data until it hits the last row (lastRow variable). The code for this is shown below:

我有一堆代码，它们遍历一组数据，然后找到数据进入下一个值步骤的行（5 个单位增量）。找到此位置后，将插入 5 行，并在这些行中计算该数据块范围内的 STDEVP、MIN、MAX 和 AVERAGE。输入后，循环继续遍历数据，直到到达最后一行（lastRow 变量）。代码如下所示：

Sub sectionBlocking()
Dim lastRow As Integer
Dim lastColumn As Integer
Dim sectionLastRow As Integer
Dim initialValue As Double
Dim cellDifference As Double
Dim stepSize As Integer
Dim firstCell As Integer
Dim lastCell As Integer
firstCell = 2
lastCell = 2
initialValue = 84
stepSize = 5
lastRow = Range("A1").End(xlDown).Row
lastColumn = Range("A1").End(xlToRight).Column
For x = 2 To lastRow
    cellDifference = Range("B" & (x)).Value - initialValue
    If Abs(cellDifference) > 1 Then
        lastCell = x - 1
        Range("B" & (x)).EntireRow.Insert
        Range("A" & (x)) = "StdDev"
        For y = 2 To lastColumn
            Cells(x, y).FormulaR1C1 = "=STDEVP(" & "R" & firstCell & "C" & y & ": R" & lastCell & "C" & y & ")"
        Next y
        x = x + 1
        Range("B" & (x)).EntireRow.Insert
        Range("A" & (x)) = "MIN"
        For y = 2 To lastColumn
            Cells(x, y).FormulaR1C1 = "=MIN(" & "R" & firstCell & "C" & y & ": R" & lastCell & "C" & y & ")"
        Next y
        x = x + 1
        Range("B" & (x)).EntireRow.Insert
        Range("A" & (x)) = "MAX"
        For y = 2 To lastColumn
            Cells(x, y).FormulaR1C1 = "=MAX(" & "R" & firstCell & "C" & y & ": R" & lastCell & "C" & y & ")"
        Next y
        x = x + 1
        Range("B" & (x)).EntireRow.Insert
        Range("A" & (x)) = "AVG"
        For y = 2 To lastColumn
            Cells(x, y).FormulaR1C1 = "=AVERAGE(" & "R" & firstCell & "C" & y & ": R" & lastCell & "C" & y & ")"
        Next y
        x = x + 1
        Range("B" & (x)).EntireRow.Insert
        x = x + 1
        firstCell = x
        initialValue = initialValue + stepSize
        'lastRow = lastRow + 5
    End If
Next x
lastCell = x - 1
Range("B" & (x)).EntireRow.Insert
Range("A" & (x)) = "StdDev"
For y = 2 To lastColumn
    Cells(x, y).FormulaR1C1 = "=STDEVP(" & "R" & firstCell & "C" & y & ": R" & lastCell & "C" & y & ")"
Next y
x = x + 1
Range("B" & (x)).EntireRow.Insert
Range("A" & (x)) = "MIN"
For y = 2 To lastColumn
    Cells(x, y).FormulaR1C1 = "=MIN(" & "R" & firstCell & "C" & y & ": R" & lastCell & "C" & y & ")"
Next y
x = x + 1
Range("B" & (x)).EntireRow.Insert
Range("A" & (x)) = "MAX"
For y = 2 To lastColumn
    Cells(x, y).FormulaR1C1 = "=MAX(" & "R" & firstCell & "C" & y & ": R" & lastCell & "C" & y & ")"
Next y
x = x + 1
Range("B" & (x)).EntireRow.Insert
Range("A" & (x)) = "AVG"
For y = 2 To lastColumn
    Cells(x, y).FormulaR1C1 = "=AVERAGE(" & "R" & firstCell & "C" & y & ": R" & lastCell & "C" & y & ")"
Next y
'lastRow = lastRow + 4
End Sub

This worked perfectly until I tried to execute the last 24 lines of code only to find that the macro had inserted the new lines in the middle of the last block of data instead of the end, as intended. I went through the debugging and realized that the row value variable "x" in the For loop was stopping at the original "lastRow" location and not the new location after the new rows had been input into the chart. This led me to try and put in the line of code below (commented above to show my progress in debugging myself):

这非常有效，直到我尝试执行最后 24 行代码才发现宏已按照预期将新行插入到最后一个数据块的中间而不是末尾。我通过调试并意识到 For 循环中的行值变量“x”停止在原始的“lastRow”位置，而不是在新行输入图表后的新位置。这让我尝试放入下面的代码行（上面的注释是为了显示我自己调试的进度）：

lastRow = lastRow + 5

This was put inside the For loop to have the "lastRow" value increase by the number of rows that were added, every time rows were added. Unfortunately, this did not work either. The "lastRow" variable was increasing its value (as found through stepping through the macro), but the For loop still ended at the same spot as without that line.

这被放在 For 循环中，每次添加行时，“lastRow”值都会随着添加的行数而增加。不幸的是，这也不起作用。“lastRow”变量正在增加其值（通过逐步执行宏发现），但 For 循环仍然在没有该行的情况下结束。

My TL;DR version of the questions is: Is there a way to increase the length of the For loop while you are already inside the loop itself or is there a more elegant way to perform this same operation?

我的 TL;DR 版本的问题是：当您已经在循环内部时，有没有办法增加 For 循环的长度，或者是否有更优雅的方法来执行相同的操作？

I can provide some of the data if the explanation is confusing. Thank you for your time.

如果解释令人困惑，我可以提供一些数据。感谢您的时间。

回答by Portland Runner

I did a simple test and found the same problem:

我做了一个简单的测试，发现了同样的问题：

Sub Macro1()
    Dim x As Integer: x = 10

    For i = 1 To x
        If (i = 5) Then
            x = 15
        End If
    Next

    MsgBox (i)
End Sub

Looks like excel does precompile the limit of a for loop. You can change your loop to a whileinstead.

看起来 excel 确实预编译了 for 循环的限制。您可以将循环更改为一段时间。

See this post

看到这个帖子

x = 2

While x <= lastRow
    cellDifference = Range("B" & (x)).Value - initialValue
    If Abs(cellDifference) > 1 Then
        'your code

        lastRow = lastRow + 1
    End If
    x = x + 1
Wend

回答by HexaGamnon

I would try a Do_While_Loop

我会尝试一个 Do_While_Loop

Do While x <= lastRow
    'Code that triggers insert
    lastRow = lastRow + 5
    '.....
    x = x + 1
Loop

回答by CommonGuy

I think it should work if you change your for-statement to

我认为如果您将for语句更改为

for x = 2 to Range("A1").End(xlDown).Row

This way, you always use the last row. At the moment, you save the "old" last row in your variable lastRow, which makes your for-loop "non-dynamic".

这样，您总是使用最后一行。目前，您将“旧”最后一行保存在变量lastRow 中，这使您的 for 循环“非动态”。

You need to do the same for your column-loop...

你需要为你的列循环做同样的事情......