VBA : How to Use For Each Loop

This tutorial explains how For Each loop works in VBA and how we can use it in Excel.

For Each Loop is a loop that iterate over each element in a collection ( like array, ranges). It perform an operation on each element without using their index or relative position.

Syntax of For Each Loop :

For Each element In collection
    ' Code to execute for each element
Next element

You can download the following dataset to practice.

Let's take a few simple examples to understand how it works.

1. Fill each cell with blue color

In this example we are trying to fill each cell in a range ("A2:A5") with blue color.

Sub fillcolor()
     Dim cell As Range
    
    For Each cell In Range("A2:A5")
        cell.Interior.Color = vbBlue  
    Next cell
End Sub

Press Run or F5 to run the above code.

In the above example, "For Each" loop performs an operation cell.Interior.Color = vbBlue over each cell in range ("A2:A5"). "Next" instructs VBA to finish the current iteration and move on to the next cell.

2. Double the value of each cell

The following code is used to multiply the value stored in each cell by 2 in cells ("A1:A5").

Sub doublecellvalue()  
    Dim cell As Range
        
        For Each cell In Range("A1:A5")
        
        If IsNumeric(cell.Value) Then
            cell.Value = cell.Value * 2
        End If
    Next cell
End Sub

Press Run or F5 to run the above code.

IsNumeric(cell.Value) prevents the code from operating on non-numeric values.

Practical examples of For Each Loop

This section covers some practical applications of using For Each Loop.

1. List all Open Workbooks using For Each Loop

In this case we list all the open workbooks in column A that belong to the active sheet of the current workbook.

Sub listallWork()
    Dim wb As Workbook
    Dim rowNum As Integer

    rowNum = 1  

    For Each wb In Application.Workbooks
        
        ThisWorkbook.ActiveSheet.Cells(rowNum, 1).Value = wb.Name
        rowNum = rowNum + 1  
    Next wb
End Sub

Press Run or F5 to run the above code.

VBA : List all Open Workbooks using For Each Loop

wb.Name returns workbook name.

2. List all the elements of an array in a single column.

In this example we list all the elements belong to an array in a single column.

Sub listArray()
    Dim myArray As Variant
    Dim ws As Worksheet
    Dim rowNum As Integer
    Dim element As Variant
    
    Set ws = ThisWorkbook.ActiveSheet
    rowNum = 1
    
    myArray = Array("Deepanshu", "Yash", "Varun", "Ankush", "Ayush", "Mohit")
    
    
    For Each element In myArray
       
        ws.Cells(rowNum, 1).Value = element
        rowNum = rowNum + 1
        Next element
End Sub

Press Run or F5 to run the above code.

VBA : List all the elements of an array in a single column

Note

Dim myArray As Variant allows myArray to store both string and numeric values.
arrayname = Array(values to be stored) is a syntax used to create an Array.

3. Find the Sum of Column A in all Worksheets.

Let's try to find the sum of all the entries in Column A in each worksheet of a workbook simultaneously. The following code should be followed:

Sub sumeachWorksheet()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim Cell As Range
    Dim rng As Range
    Dim Sum As Long

    For Each ws In Application.Worksheets
    
        lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        Set rng = ws.Range("A1:A" & lastRow)
        
        Sum = 0
        
        For Each Cell In rng
            If IsNumeric(Cell.Value) Then
                Sum = Sum + Cell.Value
            End If
        Next Cell
        
        ws.Cells(lastRow + 1, 1).Value = Sum
    Next ws
End Sub

The code above is an example of a Nested Loop. One loop works inside the another loop. First loop operates on each worksheet and the second loop works on each cell in a specified range.

About Author:
Yash Gaur

Yash is pursuing an MBA in Finance with a keen interest in analytics. He enjoys working with data and leveraging his research and analytical skills to generate valuable insights.

Let's Get Connected Email LinkedIn