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.

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

You can download the following dataset to practice.

  Download Excel File

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

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.

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.

This section introduce some practical applications of 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.

wb.Name returns workbook name.

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.

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.

Read MoreListenData