循环结束变量不变 [英] For loop end variable doesn't change
问题描述
问题发生在行被删除。我更新了 RowCount
的值,并补偿 i
再次检查同一行,因为行刚刚被删除。如果我有一列有10行,其中2是空的,它们将被删除。我希望对于i = 1到RowCount
停止在8,但是它继续9,并产生一个错误,因为它尝试删除不存在的第9行。 p>
我需要做什么,循环停止在8而不是继续(我假设 RowCount的初始值
。
Sub ColourFilledCells()
Dim Table1 As ListObject
Set Table1 = ThisWorkbook.Worksheets(1 ).ListObjects(1)
Dim i As Lon,RowCount As Long
RowCount = Table1.ListRows.Count
对于i = 1 To RowCount
如果不是Table1.DataBodyRange(i,1)= Empty Then
与Table1.DataBodyRange(i,1)
如果.Value< 0然后
.Interior.Color = RGB(255,0,0)
ElseIf .Value> 0然后
.Interior.Color = RGB(0,255,0)
Else
.ColorIndex = 0
结束如果
结束
ElseIf Table1.DataBodyRange(i,1)= Empty Then
Table1.ListRows(i).Delete
RowCount = RowCount - 1
i = i - 1
End If
Next i
End Sub
为了避免删除影响到For循环的问题,请向后倒数。
您的代码重构(加上几个建议)
对于i = RowCount到1 Step -1
如果不是空(Table1.DataBodyRange(i,1) )然后
与Table1.DataBodyRange(i,1)
如果.Value< 0然后
.Interior.Color = vbRed
ElseIf .Value> 0然后
.Interior.Color = vbGreen
Else
.ColorIndex = xlColorIndexNone
End If
End With
Else
Table1.ListRows i)。删除
结束如果
下一个我
I've written a very simple loop that goes through a table column and colors negative values red, positive values green and removes empty rows.
The problem occurs when rows are deleted. I update the value of the RowCount
, and compensate i
to check the same row again since a row was just deleted. If I have a column with 10 rows of which 2 are empty, they are deleted. I would expect the For i = 1 to RowCount
to stop at 8, but it continues to 9 and produces an error because it then tries to delete the nonexistent 9th row.
What do I need to do so the loop stops at 8 instead of continuing (to I assume the initial value of the RowCount
.
Sub ColourFilledCells()
Dim Table1 As ListObject
Set Table1 = ThisWorkbook.Worksheets(1).ListObjects(1)
Dim i As Lon, RowCount As Long
RowCount = Table1.ListRows.Count
For i = 1 To RowCount
If Not Table1.DataBodyRange(i, 1) = Empty Then
With Table1.DataBodyRange(i, 1)
If .Value < 0 Then
.Interior.Color = RGB(255, 0, 0)
ElseIf .Value > 0 Then
.Interior.Color = RGB(0, 255, 0)
Else
.ColorIndex = 0
End If
End With
ElseIf Table1.DataBodyRange(i, 1) = Empty Then
Table1.ListRows(i).Delete
RowCount = RowCount - 1
i = i - 1
End If
Next i
End Sub
To avoid issues with Delete affecting to For loop, count backwards.
Your code, refactored (Plus a few suggestions)
For i = RowCount to 1 Step -1
If Not isempty( Table1.DataBodyRange(i, 1)) Then
With Table1.DataBodyRange(i, 1)
If .Value < 0 Then
.Interior.Color = vbRed
ElseIf .Value > 0 Then
.Interior.Color = vbGreen
Else
.ColorIndex = xlColorIndexNone
End If
End With
Else
Table1.ListRows(i).Delete
End If
Next i
这篇关于循环结束变量不变的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!