excel - VBA 循环超出阈值

Question

我正在尝试使用预定义的行数和列数来掌握一个基本循环。

不知何故，循环超出了我为它定义的列限制。你能告诉我为什么吗？我如何确保它不会超出第 5 列？

下面是VBA代码：

Sub Loop101()
    
Dim r       As Integer
Dim c       As Integer
    
'Loop through rowcount and columncount as defined by r and c variables
r = 3
c = 5
    
'loop which i expect to populate a value of 100 in every cell starting with row 1, column 1 and ending and row3, column 5.
For r = 1 To r
    For c = 1 To c
    Cells(r, c).Value = 100
    Next c
Next r
        
End Sub

标签： excelvbaloops

Answer 1

正如评论中提到的，不要使用你的终点作为循环变量。

代码完全按照您的要求执行，这是一个演示该问题的示例：

Dim c As Long
For c = 1 To 5
    ' Do something
Next

Debug.Print c '<= returns 6, not 5!

因为你已经“重用”c了作为循环的终点，For c = 1 to c依次变成等价于For c = 1 to 5, then For c = 1 to 6, then For c = 1 to 7。

要解决这个问题：

Dim i as Long
Dim j as Long

For i = 1 To r
    For j = 1 To c
        Cells(i, j).Value = 100
    Next j
Next i