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.