EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Logical Functions VBA For Each Loop
Secondary Sidebar
VBA Logical Functions
  • VBA Logical
    • VBA Do While Loop
    • VBA IF Statements
    • VBA Loops
    • VBA Select Case
    • VBA Else If
    • VBA While Loop
    • VBA Select Cell
    • VBA Break for Loop
    • VBA IF Not
    • VBA Do Until Loop
    • VBA OR
    • VBA Boolean
    • VBA Like
    • VBA For Each Loop
    • VBA Operators
    • VBA Selection
    • VBA DoEvents
    • VBA Do Loop
    • VBA Not
    • VBA With
    • VBA AND

VBA For Each Loop

By Madhuri ThakurMadhuri Thakur

VBA For Each Loop

Excel VBA For Each Loop

“Each” keyword is used in VBA along with “For” function. It signifies that for each entity in an array or the range repeat the process in for loop. Basically, we won’t input the step range i.e. from 1 to 5 or 1 to 10, instead we just put it as shown in the syntax below.

Syntax

For Each object-name In Range

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Note: You need to have Developer tab in the excel worksheet.       

If you do not see the “Developer” tab in excel, refer the below steps.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,088 ratings)

Step 1: Click on File Option.

VBA For Each Loop Step 1

Step 2: Drop-down appears, Click on Options tab.

VBA For Each Loop Step 2

Step 3: Once you click on “Options”, a dialog box appears as shown below and click on the Customize Ribbon option.

VBA For Each Loop Step 3

Step 4: When we drag down in the customize ribbon options we will find an option for Developer we need to check that box which will enable us to use VBA in excel.

VBA For Each Loop Step 4

How to Use For Each Loop in VBA?

Below are the different examples to use For Each Loop in Excel using VBA code.

You can download this VBA For Each Loop Excel Template here – VBA For Each Loop Excel Template

Excel VBA For Each Loop – Example #1

We shall take a simple example of For Each Loop in VBA. Assume that below is the set of data in VBA.

VBA For Each Loop Example 1-1

Follow the below steps to use For Each Loop in Excel VBA.

Step 1: Click on “Developer” tab and then click on the “Visual Basic” option on the left-hand side (first option) as shown below.

VBA For Each Loop Example 1-2

Once you click on it, a new window appears as shown below.

VBA For Each Loop Example 1-3

You will notice that it contains two tabs on the left-hand side, “Sheet1(Sheet1)” and “ThisWorkbook”. The only difference is “Sheet1” when you want to execute code for this particular sheet only and in “ThisWorkbook”, we can write a code and execute which can be applied for the whole excel workbook.

Step 2: Double click on “Sheet1(Sheet1) and you will see a blank screen where we need to write the code. Give a name to the subset created. Here we have given name as “Sub For_Each_Ex1()” as shown in the below screenshot.

Code:

Sub For_Each_Ex1()

End Sub

VBA For Each Loop Example 1-4

This is the way we create a subset and automatically “End Sub” appears. This is by default feature of excel. If “End Sub” does not appear the code will not execute and throw an error.

Step 3: We shall declare the variables as a range.

Code:

Sub For_Each_Ex1()

Dim Earn, Range1 As Range

End Sub

VBA For Each Loop Example 1-5

Here we have declared two variables, “Earn” which we will use with For Each and “Range1” as the range of the data set.

Step 4: Now, we shall fix a range i.e. range of data which we need to work upon by using the keyword “Set” as shown below.

Code:

Sub For_Each_Ex1()

Dim Earn, Range1 As Range
Set Range1 = Range("A1:A10")

End Sub

VBA For Each Loop Example 1-6

Here we fixed “Range1” as from range cells A1 to A10, which is our data set.

Step 5: We shall now execute For loop using Each keyword.

Code:

Sub For_Each_Ex1()

Dim Earn, Range1 As Range
Set Range1 = Range("A1:A10")
For Each Earn In Range1

End Sub

VBA For Each Loop Example 1-7

Here, for each value in Range, Earn will get a value assigned as For loop gets executed step by step.

Step 6: Now, we shall use “Msgbox” keyword to display each line entry in the range assigned.

Code:

Sub For_Each_Ex1()

Dim Earn, Range1 As Range
Set Range1 = Range("A1:A10")
For Each Earn In Range1
MsgBox Earn.Value

End Sub

VBA For Each Loop Example 1-8

Here “Earn.value” will display the value in the cell i.e. starting from the first cell in the range.

Step 7: Now, we want to execute the next value in the cell range given, hence, we have to use “Next” keyword in the code. This will help to proceed further in the range and display each entity in cell range one by one.

Code:

Sub For_Each_Ex1()

Dim Earn, Range1 As Range
Set Range1 = Range("A1:A10")
For Each Earn In Range1
MsgBox Earn.Value
Next Earn

End Sub

VBA For Each Loop Example 1-9

Step 8: Run the code by hitting F5 or Run button and see the output.

VBA For Each Loop Example 1-10

The moment the “Next Earn” code line is executed, “Earn.value” will be assigned with next cell value in the range. One by one click on “OK” and will keep displaying each value in the range until the range of values is finished.

Excel VBA For Each Loop – Example #2

We shall take another example in which we shall display the name of each sheet in an excel file. Let’s assume we have sheets named as below in excel file:

VBA For Each Loop Example 2-1

As we notice that there are around 5 sheets in the excel file and we need to display the name of each one by one.

We will write and execute the code in “ThisWorkbook” section as highlighted in the red box below:

ThisWorkbook Example 2-2

Double click on it and while window pane appears and start writing the code in it. Follow the below steps to use For Each Loop in Excel VBA:

Step 1: We shall writing the code by assigning a subset name as shown below.

Code:

Sub pagename()

End Sub

VBA For Each Loop Example 2-3

Step 2: Also, we can directly start writing the “For” loop statement without declaring the variable as shown below.

Code:

Sub pagename()

For Each sht In Application.Sheets

End Sub

VBA For Each Loop Example 2-4

Here “sht” is our variable1 which by default gets declared and “Application.sheets” signifies that in the file consider all the sheets. Application.fucntion specifies that we can add any feature or function or excel tool to be considered while executing the code.

Step 3: Now we shall enter the message box syntax and along with it we shall add syntax to display sheet name as shown below:

Code:

Sub pagename()

For Each sht In Application.Sheets
MsgBox "The sheet name is:" & sht.Name

End Sub

MsgBox Example 2-5

Here the statement which we would like to display is “The sheet name is:” and then we type an ampersand(&) which concatenates the code line and then we type “sht.Name” i.e. it will select the sheet starting from the first sheet and display the name.

Step 4: Now, we need to repeat the process for displaying each sheet name. Hence, we would use “Next sht” as shown below:

Code:

Sub pagename()

For Each sht In Application.Sheets
MsgBox "The sheet name is:" & sht.Name
Next sht

End Sub

Add Next Keyword Example 2-6

“Next sht” will let the system know that, consider next sheet and display its name for further execution.

Step 6: Run the code by hitting F5 or Run button and see the output.

Sheet Name Example 2-7

Once, you click on “OK” and the code executes further, it will display the next sheet name. The code will execute until there are no further sheets on display.

Excel VBA For Each Loop – Example #3

We shall now take an example where we have to sum all the values in a given set of data or an array. Assume below is the set of data:.

Sum all the values Example 3-1

Follow the below steps to use For Each Loop in Excel VBA:

Step 1: First declare the subset name as “eachadd()” as shown below.

Code:

Sub eachadd()

End Sub

VBA For Each Loop Example 3-2

Step 2: Now, we shall declare the variables needed, one as integer named “total” in which we keep summing up the cell values one by one and fix a range of cells which contains values we need to add.

Code:

Sub eachadd()

Dim total As Integer
Dim Range1 As Range

End Sub

VBA For Each Loop Example 3-3

Step 3: After, declaring variable and range, we need to fix the range to be used for loop. We shall fix this range from A1 to A10 cells using the keyword “Set” as shown below:

Code:

Sub eachadd()

Dim total As Integer
Dim Range1 As Range
Set Range1 = Range("A1:A10")

End Sub

VBA For Each Loop Example 3-4

Step 4: We can now directly start using the For loop function and directly declare the variable. Here, we are using “add1” as variable and it will be by default declared by the system.

Code:

Sub eachadd()

Dim total As Integer
Dim Range1 As Range
Set Range1 = Range("A1:A10")
For Each add1 In Range1

End Sub

VBA For Each Loop Example 3-5

Step 5: Adding each entity in the range one by one and into the variable declared “total”. By default, the value of “total” initially is zero. And we need to add one by one value of cells and again that sum has to add in “total” again.

Code:

Sub eachadd()

Dim total As Integer
Dim Range1 As Range
Set Range1 = Range("A1:A10")
For Each add1 In Range1
total = total + add1.Value

End Sub

VBA For Each Loop Example 3-6

The above statement is executed from right to left i.e. “add1.value” is the first cell value and add it to “total” which is zero. Hence, the sum would be 0 + 1 which is 1. And that sum as 1 would go in “total” before “=” sign. This is an advantage as formula executes from left to right.

Step 6: Now add “Next add1” which will now help to consider the next cell value of the data range and add that value in the “total” variable as shown below.

Code:

Sub eachadd()

Dim total As Integer
Dim Range1 As Range
Set Range1 = Range("A1:A10")
For Each add1 In Range1
total = total + add1.Value
Next add1

End Sub

Add Next Keyword Example 3-7

Step 6: Press “F8” to run the code by each line. Also, we can see the value of “total” in the screenshot below.

Compilation of Code Example 3-8

Similarly, until the range of cells gets over, for loop will keep on executing this sum of an array.

Step 7: Now, we will use “Msgbox” keyword.

Code:

Sub eachadd()

Dim total As Integer
Dim Range1 As Range
Set Range1 = Range("A1:A10")
For Each add1 In Range1
total = total + add1.Value
Next add1
MsgBox "Final Summation:" & total

End Sub

MsgBox Example 3-9

Step 8: Run the code by hitting F5 or Run button and see the output.

Final Summation Example 3-10

Things to Remember

  • “Each” keyword is only used most of the times in case of use of VBA For Loop function only. Else, it has limited usage in excel VBA.
  • The advantage for “Each” keyword is only that we don’t have declare steps like from 1 to x in the “For” statement.

Recommended Articles

This is a guide to VBA For Each Loop. Here we discuss how to use For Each Loop in Excel using VBA code along with practical examples and downloadable excel template. Below are some useful excel articles related to VBA –

  1. VBA Do While Loop
  2. Switching Columns in Excel
  3. VBA Break for Loop
  4. Count Names in Excel
  5. How to Run the VBA Code?
Popular Course in this category
VBA Training (4 Courses, 12+ Projects)
  4 Online Courses |  13 Hands-on Projects |  50+ Hours |  Verifiable Certificate of Completion
4.7
Price

View Course
1 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more