How to use For Each…Next statement in VBA?

The For Each…Next statement in VBA (Visual Basic for Applications) is used to loop through each element in a collection or array. It’s particularly useful when you want to perform the same action on every item in a collection without manually indexing each element. Here’s how you use it:

Syntax:

VBA

For Each element In group
    [statements]
    [Exit For]
    [statements]
Next element

element: This is a variable that represents the current element in the group or collection.
group: This is the collection or array that you want to loop through.
statements: These are the lines of code that you want to execute for each element in the group.
Exit For: This statement is optional. It immediately exits the For Each loop.

Example:

Let’s say you have a collection of cells in an Excel worksheet and you want to change the background color of each cell.

VBA

Dim cell As Range
For Each cell In Worksheets("Sheet1").Range("A1:A10")
    cell.Interior.Color = RGB(255, 255, 0) ' Yellow color
Next cell

In this example, the loop will iterate over each cell in the range A1:A10 of “Sheet1”. For each cell in this range, it sets the interior color (background color) to yellow.

Key Points:

Collection or Array: The For Each…Next loop works on collections of objects (like Excel cells, slides in PowerPoint, etc.) or arrays.
Readability: It improves the readability of your code, especially when dealing with collections.
Not for Every Situation: While it’s very useful for collections, in some cases (like when you need to use the index of an array in your calculations), a traditional For…Next loop might be more appropriate.

Remember to ensure that the collection or array you are looping through is not empty to avoid runtime errors.