For...Next Statement
Syntax
For counter = initial_value To maximum_value [Step stepcounter]
'code to execute on each iteration
[Exit For]
Next [counter]
VBA For … Next Loop basic example
This is a simple for loop. The loop counter is an integer and it starts from 1 and ends at 10.
As there is no increment step is mentioned, the default increment by 1 is used.
Sub ForLoop1()
Dim i As Integer
For i = 1 To 10
Cells(i, 1).Value = i
Next
End Sub
VBA For...Next Loop using optional Counter variable after Next
This for loop is also a very basic one. The difference with the previous for loop with this is the counter beside Next.
In VBA for loop we can specify the counter beside Next. This is optional and it works exactly the same as the previous loop.
Sub ForLoop2()
Dim i As Integer
For i = 1 To 10
Cells(i, 1).Value = i
Next i
End Sub
VBA For Loop using incrementer Step (Step with positive value)
This for loop shows how we can set a custom increment. By default the loop counter is incremented by 1.
If we want to have a different amount of increment, then we have to mention it with Step.
Sub ForLoop3()
Dim i As Integer
For i = 1 To 10 Step 2
Cells(i, 2).Value = i
Next i
End Sub
VBA For Loop changing the counter inside For Loop
The counter in VBA for loop can be changed inside the for loop.
In the following code segment, the loop counter will increase by 2 as defined in the Step.
But inside the for loop, the current value of the loop counter is decremented by 1.
This might be useful if we need to change the current value of the loop counter based on some condition.
Then we can check the condition inside the loop and do whatever change we want to the loop counter.
Sub ForLoop4()
Dim i As Integer
For i = 1 To 10 Step 2
Cells(i, 3).Value = i
i = i - 1
Next i
End Sub
VBA For Loop using decrementer Step (Step with negative value)
If we want a for loop that will start from a higher value and will continue to loop until a specific lower value is met, then we will need to decrement the loop counter.
It is really simple to do by giving a negative Step value.
Sub ForLoop5()
Dim i As Integer
For i = 10 To 1 Step -2
Cells(i, 4).Value = i
i = i - 1
Next i
End Sub
VBA For Loop using initial value, ending value and step counter variable
Sometimes we don't know what the start value or the end value or the increment step will be. So we will need to have variables.
This for loop just shows the VBA for loop supports variables for start value, end value and step value.
Sub ForLoop6()
Dim i As Integer
Dim initial_value As Integer
Dim end_value As Integer
Dim step_counter As Integer
initial_value = 10
end_value = 1
step_counter = -2
For i = initial_value To end_value Step step_counter
Cells(i, 5).Value = i
i = i - 1
Next
End Sub
VBA For Loop and Exit For on a certain condition
If we need to exit from the for loop or break based on some condition, then we can use the Exit For statement.
The below for loop exits when the value of i becomes larger than 5.
Sub ForLoop7()
Dim i As Integer
For i = 1 To 10
If i > 5 Then
Exit For
End If
Cells(i, 7).Value = i
Next
End Sub
For Each...Next Statement
Syntax
For Each element In group
[statements]
[Exit For]
[statements]
Next [element]
VBA For Each … Next Loop basic example
Other than integer loop counter we might need to loop through an object collection.
We cannot use for loop for this purpose. For Each loop can be used in these cases.
The following For Each loop loops over all the sheets of the current workbook.
The current item selected from the object collection is stored in the variable given just after For Each.
Sub ForEachLoop1()
Dim i As Variant
i = 1
For Each aSheet In ActiveWorkbook.Sheets
Cells(i, 6).Value = aSheet.Name
i = i + 1
Next
End Sub
VBA For Each Loop, Looping over a string array
We can also loop through our self defined array of string or integer or any other data type. The below for loop shows an array of 10 strings.
Although only 3 strings are set in the array, the For Each loop will loop for 10 times.
For the unassigned elements of the array, the variable will have blank as string.
Sub ForEachLoop2()
Dim i As Variant
Dim str_array(10)
str_array(1) = "first"
str_array(2) = "second"
str_array(3) = "third"
i = 1
For Each aStr In str_array
Cells(i, 8).Value = IIf(aStr = "", "Blank", aStr)
i = i + 1
Next
End Sub