Welcome to Excel Avon
For Each Loop in Excel VBA?
DOWNLOAD USED EXCEL FILE FROM HERE>>
In today’s article (For Each Loop in Excel VBA) we will explain how to use a loop that allows you to perform the same activity for multiple cells or objects in Excel. The VBA for “each” loop goes through the objects or collection of objects and performs similar activities. The “each” keyword is used with the “for” function in VBA. and indicates that the process repeats in the for loop for each unit in an array or range.
To understand For Each Loop in Excel VBA, we have to go to VBE – Like last time, first go to the Developer Tab, then we will click on the Visual Basic option as shown in the image below.
On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.
For Each Loop in Excel VBA?
In this example we will be using For Each Loop in excel VBA, first of all, I will write subroutine just for For Each Loop.
Sub UseForEach() End Sub
After writing the subroutine, we will define bigrng as range.
Sub UseForEach() Dim bigrng as Range End Sub
we will define cell as range.
Sub UseForEach() Dim bigrng as Range Dim Cell as Range End Sub
Now I will use set function for range and define big rng from activesheet range.
Sub UseForEach()
Dim bigrng as Range
Dim Cell as Range
set bigrng = activesheet.range("A1: D10")
End Sub
We shall now execute for loop using each cell in bigrng.
Sub UseForEach()
Dim bigrng as Range
Dim Cell as Range
set bigrng = activesheet.range("A1: D10")
For each cell in bigrng
Next Cell
End Sub
I will Define some text for Cell. Value
Sub UseForEach()
Dim bigrng as Range
Dim Cell as Range
set bigrng = activesheet.range("A1: D10")
For each cell in bigrng
cell.value = "ABC"
Next Cell
End Sub
After writing the code, click the run button
After clicking the run button, we will go to the activesheet, the values in all of the ranges that were selected have been filled.
Let’s go back to the activesheet and see by filling in another value. Now i will fill the no. of cell of the row & column
Sub UseForEach()
Dim bigrng as Range
Dim Cell as Range
set bigrng = activesheet.range("A1: D10")
For each cell in bigrng
cell.value = "Cell.Row & ", " & Cell.Column
Next Cell
End Sub
After writing the code, click the run button
After clicking the run button, we will go to the activesheet, the values in all of the ranges that were selected have been filled. the Column and row values of each cell in the activesheet are filled in all the cell.
With array, for Each Loop
Write this loop code you need to know the size of the array (upper and lower bound) so that you can use both as the counter for the loop. Basically, for loop will start from the first element of the array and loop up to the last.
First, I will write subroutine use for Each loop with array.
Sub UseWithArray() End Sub
After writing the subroutine, we will define variable allThings as variant.
Sub UseWithArray() Dim allThings as variant End Sub
we will define variable oneThings as variant. Define Both variables as variant
Sub UseWithArray() Dim allThings as variant Dim onethings as variant End Sub
Now I will create an array, Define with allThings.
Sub UseWithArray()
Dim allThings as variant
Dim onethings as variant
allThings = array("A", "B", "C", "D", "Jan", "Feb", "Mar", "X", "1", "2", "3", "4", "5", "7")
End Sub
Now Execute for loop using Each oneThing in allThings.
Sub UseWithArray()
Dim allThings as variant
Dim onethings as variant
allThings = array("A", "B", "C", "D", "Jan", "Feb", "Mar", "X", "1", "2", "3", "4", "5", "7")
for Each oneThing in allThing
Next oneThing
End Sub
Now we will use debug.print to print the variable
Sub UseWithArray()
Dim allThings as variant
Dim onethings as variant
allThings = array("A", "B", "C", "D", "Jan", "Feb", "Mar", "X", "1", "2", "3", "4", "5", "7")
for Each oneThing in allThing
debug.Print onething
Next oneThing
End Sub
after writing the code, Click the run button
After clicking the run button, we will go to the immediate window, The values we defined are printed in the Immediate window.
