在循环中更改For循环的长度 [英] Change length of For loop while in the loop
问题描述
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
对于x = 2 To lastRow
cellDifference = Range（B&（x））Value - initialValue
如果Abs（cellDifference）> 1然后
lastCell = x - 1
Range（B&（x））。EntireRow.Insert
Range（A&（x））=StdDev
对于y = 2 To lastColumn
单元格（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
对于y = 2 To lastColumn
单元格（x，y）.FormulaR1C1 == MIN（& &C& y&：R& lastCell&C& y&）
Next y
x = x + 1
Range（B&（x））。EntireRow.Insert
Range（A&（x））=MAX
对于y = 2 To lastColumn
单元格（x，y）.FormulaR1C1 == MAX（&R& firstCell&C& y&R&C& y&
下一个y
x = x + 1
范围（B& （x））EntireRow.Insert
Range（A&（x））=AVG
对于y = 2 To lastColumn
单元格（x，y）.FormulaR1C1 = = AVERAGE（&R& firstCell&C& y&：R& lastCell&C&）
下一个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
对于y = 2 To lastColumn
单元格（x，y）.FormulaR1C1 == STDEVP（& R& firstCell&C& y&：R& lastCell&C& y&）
Next y
x = x + 1
范围（B&（x））。EntireRow.Insert
范围（A&（x））=MIN
对于y = 2 To lastColumn
细胞（x，y）.FormulaR1C1 == MIN（&R& firstCell& C& y& ：R& lastCell& C& y& ）
下一个y
x = x + 1
范围（B&（x））EntireRow.Insert
范围（A&（x） ）=MAX
对于y = 2 To lastColumn
单元格（x，y）.FormulaR1C1 == MAX（&R&C&C& ;：R& lastCell&C& y&）
Next y
x = x + 1
范围（B&（x）） .EntireRow.Insert
范围（A&（x））=AVG
对于y = 2 To lastColumn
单元格（x，y）.FormulaR1C1 == AVERAGE &R& firstCell&C& y&：R& lastCell&C& y&）
Next y
' lastRow = lastRow + 4
End Sub
这样很好，直到我试图执行最后24行代码只发现宏已经在最后一个数据块的中间插入新行，而不是结束，如预期的那样。我经历了调试，意识到For循环中的行值变量x在原始lastRow位置停止，而不是在新行被输入到图表之后的新位置。这导致我尝试并放在下面的代码中（上面提到了显示我在调试自己的进度）：
lastRow = lastRow + 5
这被放在For循环中，使lastRow值增加每次添加行时，添加的行数。不幸的是，这也没有。 lastRow变量正在增加其值（通过遍历宏）发现，但是For循环仍然在没有那条线的同一个位置处结束。
我的TL; DR版本的问题是：有没有办法增加For循环的长度，而您已经在循环本身，还是有更优雅的方式来执行相同的操作？
如果解释很混乱，我可以提供一些数据。谢谢你的时间。
我做了一个简单的测试，发现了同样的问题：
Sub Macro1（）
Dim x As Integer：x = 10
对于i = 1 To x
如果（i = 5）然后
x = 15
结束If
下一个
MsgBox（i）
End Sub
看起来excel会预编译for循环的限制。
看到这个 / how-to-iterate-over-dynamic-increasing-dictionary-seem-excel-vba-precompil> post
x = 2
虽然x <= lastRow
cellDifference = Range（B&（x））Value - initialValue
如果Abs（cellDifference） > 1然后
'你的代码
lastRow = lastRow + 1
End If
x = x + 1
Wend
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:
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):
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.
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?
I can provide some of the data if the explanation is confusing. Thank you for your time.
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 while instead.
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
这篇关于在循环中更改For循环的长度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
