我有一堆代码,它处理一组数据,然后找到数据进入下一个值步骤(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
这一直很好,直到我尝试执行最后24行代码,只是发现宏已按预期将新行插入到最后一个数据块的中间而不是末尾。我进行了调试,发现在新行输入到图表后,For循环中的行值变量“ x”停止在原始“ lastRow”位置,而不是新位置。这导致我尝试放入下面的代码行(上面的注释表明了我调试自己的进度):
lastRow = lastRow + 5
将其放置在For循环中,以使“ lastRow”值每次添加行时都增加所添加的行数。不幸的是,这也不起作用。“ lastRow”变量正在增加其值(通过逐步访问宏可以发现),但是For循环仍在没有该行的位置结束。
我的TL; DR版本的问题是:当您已经在循环本身内部时,是否可以增加For循环的长度,或者有一种更优雅的方式来执行相同的操作?
如果解释令人困惑,我可以提供一些数据。感谢您的时间。
我做了一个简单的测试,发现了同样的问题:
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
看起来excel确实预编译了for循环的限制。您可以改为将循环更改为一段时间。
看到这篇文章
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
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句