The simplest type of loop is a For-Next loop. Here’s the syntax for this structure:
For counter = start To end [Step stepval]
[statements]
[Exit For]
[statements]
Next [counter]
The looping is controlled by a counter variable, which starts at one value and stops at another value. The statements between the For statement and the Next statement are the statements that get repeated in the loop. To see how
this works, keep reading.
A For-Next example
The following example shows a For-Next loop that doesn’t use the optional Step value or the optional Exit For statement. This routine loops 100 times and uses the VBA Rnd function to enter a random number into 100 cells:
Sub FillRange()
Dim Count As Long
For Count = 1 To 100
ActiveCell.Offset(Count – 1, 0) = Rnd
Next Count
End Sub
In this example, Count (the loop counter variable) starts with a value of 1 and increases by 1 each time through the loop. Because I didn’t specify a Step value, VBA uses the default value (1). The Offset method uses the value of Count as an argument. The first time through the loop, the procedure enters a number into the active cell offset by zero rows. The second time through (Count = 2), the procedure enters a number into the active cell offset by one row (Count –1), and so on. Because the loop counter is a normal variable, you can change its value within the block of code between the For and the Next statements. This, however, is a very bad practice. Changing the counter within the loop can have
unpredictable results. Take special precautions to ensure that your code does not directly change the value of the loop counter.
