VBA/Excel/Access/Word/Language Basics/For

Checks values in a range 10 rows by 5 columns

 
Sub CheckValues3()
    Dim colIndex As Integer
    Dim rwIndex As Integer
    For colIndex = 1 To 5
            For rwIndex = 1 To 10
                If Cells(rwIndex, colIndex).Value <> 0 Then _
                    Cells(rwIndex, colIndex).Value = 1
            Next rwIndex
    Next colIndex
End Sub

For Loops with different steps

 
Sub ForSteps()
    Dim I As Integer
    For I = 0 To 10
      MsgBox (I)
    Next I
    For I = 0 To 10 Step 2
      MsgBox (I)
    Next I
    For I = 0 To 10 Step 3
      MsgBox (I)
    Next I
    For I = 10 To 0 Step -5
      MsgBox (I)
    Next I
End Sub

For loop whose step is 2

 
Sub ForNextStep()
    Dim intCounter As Integer
    For intCounter = 1 To 5 Step 2
        Debug.Print intCounter
    Next intCounter
End Sub

For...Next Loop

 
   Sub GetTextBoxNames() 
       Dim myForm As Form 
       Dim myControl As Control 
       Dim c As Integer 
       Set myForm = Screen.ActiveForm 
       Set myControl = Screen.ActiveControl 
       For c = 0 To myForm.Count - 1 
           If TypeOf myForm(c) Is TextBox Then 
               MsgBox myForm(c).Name 
           End If 
       Next c 
   End Sub

iterates four times outputting the numbers and 9 in a message box:

 
Sub forLoop3()
    Dim I As Integer
    For I = 0 To 10 Step 3      "4 iterations: 0, 3, 6, and 9
          Debug.Print I
    Next I
End Sub

Iterates six times outputting the numbers 6, 8, and 10 in a message box:

 
Sub forLoop2()
    Dim I As Integer
    
    For I = 0 To 10 Step 2      "6 iterations: 0, 2, 4, 6, 8, and 10
          Debug.Print I
    Next I
End Sub

Nest if statement into a for statement

 
Sub forTest()
    Dim intCounter As Integer
    
    For intCounter = 1 To 10
        If (intCounter Mod 2) = 0 Then
            Debug.Print intCounter & " is an even number"
        Else
            Debug.Print intCounter & " is an odd number"
        End If
    Next
End Sub

Non integer step for loop

 
Sub macro_loop2()
  Dim i As Double
  For i = -0.3 To 0.3 Step 0.1
    Debug.Print i
  Next i
End Sub

The For...Next construct is used when you have an exact number of iterations you want to perform

 
Sub cmdForNext()
    Dim intCounter As Integer
    For intCounter = 1 To 5
        Debug.Print intCounter
    Next intCounter
End Sub

the loop iterates just three times outputting the numbers and 0 in a message box:

 
Sub loopDemo4()
    Dim I As Integer
    For I = 10 To 0 Step -5      "3 iterations: 10, 5, and 0
          Debug.Print I
    Next I
End Sub

you can go backward and use increments other than 1

 
Sub ReverseForNext() 
    Dim n As Integer 
    For n = 50 To 1 Step -2 
        Debug.Print n 
    Next 
End Sub