VBA FOR LOOP (For Next, For Each) – The Guide + Examples

Key Points

• VBA For Loops are fixed types of loops.
• It is not possible to use any of these loops while recording a macro.

For Next Loop

VBA FOR NEXT is a fixed loop that uses a counter to run iterations. In simple words, you need to specify the number of times you want to run the loop, and once it reaches that count loop, it will stop automatically. That’s why it is a fixed loop and most popular among VBA developers.

Syntax

Following is the syntax for the VBA For Next Loop:

For counter = Start To End [Step stepsize]
[statements]
Next [counter]

1. Counter: It’s variable that stores the count of the repetition and increases its value with each repetition. For example, if the loop is in its fifth repetition the value of the counter will be five, and once the loop executes the statement for the fifth iteration the value of the counter changes to six.
2. Start: It is a numeric variable that stores the starting value for the counter.
3. End: It is a numeric variable that stores the ending value for the counter.
4. Step Size: This variable specifies a big increase or decrease in the value of the counter. By default, the step size is one, but you can also use any positive or negative value.
5. Statement: It’s a set of code do you want to execute in each iteration of the For Next loop.
6. Next: It’s the end statement for one iteration of the loop and uses the counter value to tell VBA to repeat the loop and once the counter reaches the end value it ends the loop.

Example to Understand For Next Loop

To understand how to use it for the next loop, let’s write a simple code to add serial numbers in a column.

Let me elaborate a little more: We need to write a code that can go from cell selected cell to the next 10 cells one by one and add serial numbers just in the below snapshot.

To use For Next Loop, you can use the following steps:

1. First, you need to declare a variable that you can use to store the counter value.
   
2. Next, we need to assign the counter value to the variable.
   
3. Now it’s time to write code for loop, so in the first line, you need to use the counter variable and define starting and the ending value. Here in this code, you have the variable “i” as the ending value.
   
4. From here you need to write code that can add the serial numbers into the active cell and then move to the next cell. As the counter value increases in each iteration, you can use it to enter the serial number into the active cell and after that, you can use the offset to move the selection to the next cell downwards.
   
5. In the end, use the keyword next and the counter variable to end the code for the loop.

Sub AddSerialNumbers()
Dim i As Integer
i = 10 

    For i = 1 To i 
  
        ActiveCell.Value = i       
        ActiveCell.Offset(1, 0).Activate  
 
    Next i

End Sub

You can see in the below snapshot, when I run this code it starts from the selected cell by entering a serial number and then moves to the next cell with each iteration.

As you know, For Next is a fixed loop and in this code, when you assign a numeric value, it tells VBA to run the loop for that number of times.

And you can see the below snapshot that I have taken in the second iteration of the loop where the value of “i” counter variable is two.

The point I’m trying to make is the counting variable changes its value in each iteration and enters that value in the active cell and then offset activates the next cell downward that completing one iteration.

In the end, the “Next” keyword it’s VBA to go back to the first line of the loop and repeat it. As you have seen in the syntax of For Next, you can also use a step value in the loop to make the counter work according to that.

So if you use two as a step value the code would be something like the below.

Sub AddSerialNumbers()

Dim i As Integer
i = 10 

    For i = 1 To i Step 2  

        ActiveCell.Value = i
        ActiveCell.Offset(1, 0).Activate 
  
    Next i

End Sub

And when you run this code, it will on the activity something like the below snapshot.

When you use a step value (Like Step 2, which you have used in the above code), VBA skips every 2nd iteration in the loop, and as you can see you have got five numbers with the difference of one within each number.

Note: In the above example, you have used “i” as the counter variable, but you can use a different name as well according to your convenience.

For Each Next Loop

VBA FOR EACH NEXT is a fixed loop that can loop through all the objects in a collection. In the For Each Next, you don’t need to specify the count of iterations. Instead, you can specify a collection of objects, and it will be able to loop through all those objects one by one.

For Example: If you want to loop through all the cells from a range. In this case, the range is the collection, and cells are the objects that you have.

Syntax

Following is the syntax for the VBA For Each Next Loop:

For Each Object In Collection
[statements]
Next [object]

1. Object: It represents the one object in the collection of objects in which you are looping. With each iteration, moves from one object to the next.
2. Collection: It’s a collection of objects in which you want to loop.
3. Statement: Line(s) of code that you want to execute in each loop through all the objects in the collection.
4. Next: It’s the end statement for one iteration of the loop and tells VBA to move to the next object in the collection.

Example to Understand the For Each Next Loop

To understand For Each Next loop, let’s write a code to hide all the sheets other than the active worksheet. This code will check each worksheet’s name and match it with the active worksheet’s name and hide it if it doesn’t match.

To use For Each Next loop, you can use the following steps:

1. First, you need to declare a variable to use as a worksheet.
   
2. After that, use “For each” keyword, and then “mySheet” variable for the sheet, and use “ActiveWorkbook.Worksheets” for the collection of worksheets from the active workbook.
   
3. Next, you need to use VBA IF Statement to test if the name of the sheet that is currently in that loop iteration is NOT EQUAL to the active worksheet, and if this condition is true then hide sheet that is now in the loop iteration.
   
4. In the end, you have the NEXT keyword and “mySheet” the variable to end the code for the loop.
   

Final Code:

Sub Hide_Other_Sheets()
 
Dim mySheet As Worksheet
 
For Each mySheet In ActiveWorkbook.Worksheets

    If mySheet.Name <> ActiveSheet.Name Then

        mySheet.Visible = False

    End If

Next mySheet

End Sub

When you run this code, it will loop through each worksheet in the active workbook and match its name with the active worksheet, and if the worksheet’s name is not matched with the active worksheet’s name, it will hide it.

In this way, all the worksheets will hide.