循环结束变量不变 [英] 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屋！