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

Checks values in a range 10 rows by 5 columns

   <source lang="vb">

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

</source>
   
  

For Loops with different steps

   <source lang="vb">

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

</source>
   
  

For loop whose step is 2

   <source lang="vb">

Sub ForNextStep()

   Dim intCounter As Integer
   For intCounter = 1 To 5 Step 2
       Debug.Print intCounter
   Next intCounter

End Sub

</source>
   
  

For...Next Loop

   <source lang="vb">

  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 
</source>
   
  

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

   <source lang="vb">

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

</source>
   
  

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

   <source lang="vb">

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

</source>
   
  

Nest if statement into a for statement

   <source lang="vb">

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

</source>
   
  

Non integer step for loop

   <source lang="vb">

Sub macro_loop2()

 Dim i As Double
 For i = -0.3 To 0.3 Step 0.1
   Debug.Print i
 Next i

End Sub

</source>
   
  

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

   <source lang="vb">

Sub cmdForNext()

   Dim intCounter As Integer
   For intCounter = 1 To 5
       Debug.Print intCounter
   Next intCounter

End Sub

</source>
   
  

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

   <source lang="vb">

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

</source>
   
  

you can go backward and use increments other than 1

   <source lang="vb">

Sub ReverseForNext()

   Dim n As Integer 
   For n = 50 To 1 Step -2 
       Debug.Print n 
   Next 

End Sub

</source>