VBA Loop goes beyond threshold

Question

I am trying to master a basic loop using predefined row and column count.

Somehow loop continues beyond the column limit I have defined for it. Can you advise why? And how can I ensure it does not go beyond column 5?

Below is VBA code:

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

Answer 1

As mentioned in the comments, don't use your end point as the looping variable.

The code is doing exactly what you told it to though, here's an example demonstrating the issue:

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

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

Because you have "re-used" c as the end point of the loop, For c = 1 to c successively becomes equivalent to For c = 1 to 5, then For c = 1 to 6, then For c = 1 to 7.

TO FIX IT:

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