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

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

By John Walkenbach

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.

Using a loop to apply background shading to rows.
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.
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.
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