We are going to learn about programming FOR EACH NEXT loops. First we are going to learn what loops are and why they are necessary. Then we are going to learn how to use a FOR EACH NEXT LOOP.
Types of VBA loops
Note: In Excel VBA they are two types of FOR LOOPS: FOR NEXT and FOR EACH NEXT. Our focus will be on the FOR EACH NEXT loop in this article.
The For Each loop in Excel VBA is used to iterate over a collection of objects, such as cells in a range or items in an array. The syntax is as follows:
For Each item in collection
[statements to be executed]
Here, item is a variable that holds each item in the collection as the loop iterates over it. collection is the object to be looped through.
Dim rng As Range
Set rng = Range("A1:A5")
For Each cell In rng
cell.Value = "Hello World"
This code sets a range rng to the cells A1 to A5, and then uses a For Each loop to iterate through each cell in the range, setting the value of each cell to “Hello World”.
Now let’s see how to use the For Each Next loop.
Follow the steps below:
Right-click anywhere on the ribbon and select “Customize the Ribbon” from the drop down menu.
A dialog box will open. Click on the DEVELOPER check box (it is under “Customize the Ribbon Main Tabs”) and press ok.
The developer tap is now visible and is present next to the view tab on the top menu bar. Click on Developer tab and select “View Code”
A new window (Visual Basic Editor) will open which would have a dialog box in the center. You will write the code in the dialog box.
Write the following line of code in the dialog box.
For Each sht In ActiveWorkbook.Worksheets
After writing the code, close the window by clicking on the cross(x) icon on the upper right side of the screen.
This is the result. That’s it! You have successfully used a For Next Each loop.