One of the interesting things about the For~Next Loop in VBA is the Step.
[ statements ]
[ Continue For ]
[ statements ]
[ Exit For ]
[ statements ]
Next [ counter ]
Step defaults to 1, but Step 2 will cause the loop to run while incrementing the counter variable by 2, and Step -1 will cause the loop to run in reverse, decrementing by 1.
Debug.Write(index.ToString & ” “)
Next
Debug.WriteLine(“”)
‘ Output: 1 2 3 4 5
For number As Double = 2 To 0 Step -0.25
Debug.Write(number.ToString & ” “)
Next
Debug.WriteLine(“”)
‘ Output: 2 1.75 1.5 1.25 1 0.75 0.5 0.25 0
However, For Each~Next, which iterates over the objects in a collection, does not have a Step. For example, you can’t iterate over the range of cells A1:A100, skipping even or odd rows. However, you can create such a loop with the Do Loop and Offset methods instead of For Each. The following loop iterates between A6 and A22, taking only the addresses of the even rows.
Set rngEnd = Sheet1.Range(“A22”)
Set rng = rngBegin
Debug.Print rngBegin.Address
Debug.Print rngEnd.Address
step = 2
Do
Debug.Print rng.Address
Set rng = rng.Offset(step, 0)
If rng.Address = rngEnd.Address Then Exit Do
Loop
This is off topic, but I should have written “If rng Is rngEnd Then Exit Do” instead of “If rng.Address = rngEnd.Address Then Exit Do”. There is no runtime error when executed, but it does not exit at rngEnd, i.e. A22, as expected.
The cell addresses of rng and rngEnd in the last iteration are both ‘A22’, but they are not actually the same object variable. When declaring the object variables, we did the following
Dim rngEnd As Range
These two variables are on the stack, but they will have different memory addresses. Therefore, although they may have the same cell address, ‘A22’, they will have different addresses, so they cannot be the same object.
