Excel VBA and The For Each Next Loop

In our prior tutorial, we covered how to do a standard Do loop. Now, we will continue with another form of loop, the For-Each-Next Loop.  For-Each-Next allows you to specify a series or range of cells that you would like to loop through.

The general setup involves

Sub For_Each_Loop()
'set loop range
For Each 'range to loop through
'code to process in loop
Next
End Sub

Looping Through a Range of Cells

Last time we looped through a range of cells using our Activecell and offsetting it row by row for each loop iteration. In this example we will do something similar by specifying a range of cells as a variable, and looping through that range.

I have the below values in column A, cells A2:A7

Suppose I wanted to loop through them and multiply each of them by two. To do so I would do the below.
The general logic to make the below is that:

  • I have a range of cells.
  • I am telling Excel that my range of cells is A2:A6.
  • Assign the range to a variable.
  • State that For Each cell in this range, take the cell value and multiply it by 2.
  • Once this is done I move on the the Next cell
Sub For_Each_Loop()

Dim cell As range, rnge As range
Set rnge = range("A2:A6")

For Each cell In rnge
cell.Value = cell.Value * 2

Next

End Sub

 

Looping Through Worksheets

You can also loop through a series of worksheets, columns, or objects like pictures and graphs.

Sub loop_through_ws()


Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
    
    MsgBox ws.Name
    
    Next

End Sub

The above will loop through every sheet in my workbook, displaying each sheet name in a Message box.


If you liked this content and found it helpful, please like the page and leave a comment below. You can also ask questions on the Forum to get personalized help on your issues and questions.

Please also consider making a small donation to help with the upkeep of the site and with the development of future content.

$0.99



Categories: Excel, VBA

Tags: , , , , ,

Leave a Reply

Show Buttons
Hide Buttons
%d bloggers like this: