How to Use the For-Next Loops in Excel 2016 VBA
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.
Sub AddNumbers() 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:
Sub AddOddNumbers() 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.
Sub ShadeEveryThirdRow() 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.
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.
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.
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