 How to Use the For-Next Loops in Excel 2016 VBA - dummies

The simplest type of loop in Excel VBA programming is a For-Next loop. 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.

A For-Next example

The following example uses a For-Next loop to sum the first 1,000 positive numbers. The Total variable starts out as zero. Then the looping occurs. The variable Cnt is the loop counter. It starts out as 1 and is incremented by 1 each time through the loop. The loop ends when Cnt is 1,000.

This example has only one statement inside the loop. This statement adds the value of Cnt to the Total variable. When the loop finishes, a MsgBox displays the sum of the numbers.

Dim Total As Double
Dim Cnt As Long
Total = 0
For Cnt = 1 To 1000
Total = Total + Cnt
Next Cnt
MsgBox Total
End Sub

Because the loop counter is a normal variable, you can write code to change its value within the block of code between the For and the Next statements. This, however, is a very bad practice.

For-Next examples with a Step

You can use a Step value to skip some counter values in a For-Next loop. Here’s the previous example, rewritten to sum only the odd numbers between 1 and 1,000:

Dim Total As Double
Dim Cnt As Long
Total = 0
For Cnt = 1 To 1000 Step 2
Total = Total + Cnt
Next Cnt
MsgBox Total
End Sub

This time, Cnt starts out as 1 and then takes on values of 3, 5, 7, and so on. The Step value determines how the counter is incremented. Notice that the upper loop value (1000) is not actually used because the highest value of Cnt will be 999.

Here’s another example that uses a Step value of 3. This procedure works with the active sheet and applies light gray shading to every third row, from row 1 to row 100.

Dim i As Long
For i = 1 To 100 Step 3
Rows(i).Interior.Color = RGB(200, 200, 200)
Next i
End Sub

Check out the result of running this macro. Using a loop to apply background shading to rows.

A For-Next example with an Exit For statement

A For-Next loop can also include one or more Exit For statements within the loop. When VBA encounters this statement, the loop terminates immediately.

The following example demonstrates the Exit For statement. This routine is a Function procedure, intended to be used in a worksheet formula. The function accepts one argument (a variable named Str) and returns the characters to the left of the first numeric digit. For example, if the argument is “KBR98Z,” the function returns “KBR.”

Function TextPart(Str)
Dim i As Long
TextPart = ““
For i = 1 To Len(Str)
If IsNumeric(Mid(Str, i, 1)) Then
Exit For
Else
TextPart = TextPart & Mid(Str, i, 1)
End If
Next i
End Function

The For-Next loop starts with 1 and ends with the number that represents the number of characters in the string. The code uses VBA’s Mid function to extract a single character within the loop. If a numeric character is found, the Exit For statement is executed, and the loop ends prematurely.

If the character is not numeric, it is appended to the returned value (which is the same as the function’s name). The only time the loop will examine every character is if the string passed as the argument contains no numeric characters.

A nested For-Next example

You can have any number of statements in the loop and nest For-Next loops inside other For-Next loops.

The following example uses a nested For-Next loop to insert random numbers into a 12-row-by-5-column range of cells. Notice that the routine executes the inner loop (the loop with the Row counter) once for each iteration of the outer loop (the loop with the Col counter). In other words, the routine executes the Cells(Row, Col) = Rnd statement 60 times. These cells were filled by using a nested For-Next loop.
Sub FillRange()
Dim Col As Long
Dim Row As Long
For Col = 1 To 5
For Row = 1 To 12
Cells(Row, Col) = Rnd
Next Row
Next Col
End Sub

The next example uses nested For-Next loops to initialize a three-dimensional array with the value 100. This routine executes the statement in the middle of all the loops (the assignment statement) 1,000 times (10 * 10 * 10), each time with a different combination of values for i, j, and k:

Sub NestedLoops()
Dim MyArray(10, 10, 10)
Dim i As Long
Dim j As Long
Dim k As Long
For i = 1 To 10
For j = 1 To 10
For k = 1 To 10
MyArray(i, j, k) = 100
Next k
Next j
Next i
‘ Other statements go here
End Sub

Here’s a final example that uses nested For-Next loops, with a Step value. This procedure creates a checkerboard by changing the background color of alternating cells. Using loops to create a checkerboard pattern.

The Row counter loops from 1 to 8. An If-Then construct determines which nested For-Next structure to use. For odd-numbered rows, the Col counter begins with 2. For even-numbered rows, the Col counter begins with 1. Both loops use a Step value of 2, so alternate cells are affected. Two additional statements make the cells square (just like a real checkerboard).

Sub MakeCheckerboard()
Dim R As Long, C As Long
For R = 1 To 8
If WorksheetFunction.IsOdd(R) Then
For C = 2 To 8 Step 2
Cells(R, C).Interior.Color = 255
Next C
Else
For C = 1 To 8 Step 2
Cells(R, C).Interior.Color = 255
Next C
End If
Next R
Rows(“1:8”).RowHeight = 35
Columns(“A:H”).ColumnWidth = 6.5
End Sub