The For Loop in VBA is one of the most frequently used loops in VBA. The For loop has two forms: For Next and For Each In Next. The For loop is typically used to move sequentially through a list of items or numbers. To end the for loop at any given point we can use the exit for statement. Let’s take a closer look at each of these loops.
VBA For … Next Loop
The For … Next loop has the following syntax:
1 2 3 | For a_counter = start_counter To end_counter 'Do something here Next a_counter |
What we are doing here essentially is create a loop that uses a variable a_counter as the ‘time keeper’ of the loop. We set it to a value equal to start_counter at the beginning of the loop and then increment (or reduce) it by 1 during each loop till. The loop will execute till the time the value of the a_counter becomes equal to end_counter. The loop executes for the last time when both the above values match and then stop.
Example of a for loop
1 2 3 4 5 6 7 | Sub my_for_loop1() For a_counter = 1 to 10 j = a_counter Next a_counter msgbox "The value of the counter in the last loop was " & a_counter End Sub |
The final value of the of the a_counter in the above loop is 11.
VBA For Loop in Reverse with STEP Instruction
It is not necessary that counter in the for loop only move from a lower value to a higher value - you can have the for loop backwards too. Here is an example of for loop in reverse:
1 2 3 4 5 6 7 | Sub my_for_loop2() For a_counter = 10 to 1 Step -1 j = a_counter Next a_counter msgbox "The value of the counter in the last loop was " & a_counter End Sub |
The final value of the a_counter in this loop is 1.
As you can see, we can use the Step n instruction to ensure that the for loop works either forward or in reverse. By default the Step value is forward 1, however it can be set to a number more than 1 to skip forward loops or negative for the for loop to work in reverse.
VBA For Each … In … Next Loop
The For Each … In … Next loop has the following syntax:
1 2 3 | For Each item_in_group In group_of_items Do something here Next item_in_group |
The item_in_group here belongs to a group_of_items (smart ain’t I). What I mean is that the object used as a group_of_items has to be a collection of objects. You can’t run a ‘for each item’ loop on individual objects (lest Microsoft throw the friendly run-time error 438 at you !)
The above loop moves one item at a time starting with the first item in the collection of objects. You can use this particular for loop syntax to iterate along sheets in a workbook, shapes in a sheet, pivot tables in a sheet or any collection of objects in general.
Let’s take the example of how you can use the for loop to iterate through all worksheets in a workbook:
1 2 3 4 5 | Sub my_for_loop3() For Each sht In ActiveWorkbook.Worksheets MsgBox sht.Name Next i End Sub |
Now let’s see how we can loop through all the pivot tables in a sheet:
1 2 3 4 5 | Sub my_for_loop4() For Each pvt In ActiveSheet.PivotTables MsgBox pvt.Name Next pvt End Sub |
End For Loop before End Condition
If you need to end the For loop before the end condition is reached or met, simply use the END FOR in conjunction with the IF statement. In the example given below, we exit the for loop prematurely and before the end condition is met. The for example given below, the loop exits when a_counter reaches a value of 3.
1 2 3 4 5 6 | Sub my_for_loop5() For a_counter = 0 To 5 MsgBox a_counter If (a_counter = 3) Then Exit For Next a_counter End Sub |
Move out of or Skip a particular loop in a For Loop
It is not advisable to move out a for loop and then move back again. Let’s take a very convoluted example:
1 2 3 4 5 6 7 8 9 10 11 12 13 | Sub my_for_loop6() Dim j As Integer For i = 0 To 5 b: If (j = 3) Then GoTo a: j = i Next i a: j = 4 GoTo b: MsgBox ("Final value of j = " & j) End Sub |
What we’ve tried to do here is a move out of the for loop in one particular iteration (when j = 3). What do you think is the final value of j in the above example. 3 ? 5? Well … none of them really. The loop executes endlessly and would soon lead to overflow.
However it is possible to skip a loop in the For Loop. You can increment the counter by 1 (or any other number) and that can cause the for loop to skip all the loops in between. Here’s an example.
1 2 3 4 5 6 | Sub my_for_loop7() For i = 0 To 5 i = i + 1 MsgBox i Next i End Sub |
However again, this is not a good coding practice and can lead to headaches for the folks maintaining the VBA code later. Instead check if the particular condition is to be skipped in a FOR loop, try using an IF function or even a SELECT CASE statement.
Can you help? I am trying to scan a group of cells within Excel using For Each…Next statement. I have a code which basically works (see below). My problem is that I need to know which Cell within the collection is being processed when my test condition is true. Nothing I’ve tried to far has worked.
Dim MyObject, MyCollection
MyCollection = Range(”PROJECTS”)
July 7th, 2009 at 2:33 amFor Each MyObject In MyCollection
If MyObject = “=” Then
‘ <<<>>>
Exit For
End If
Next
Peter you can try this:
2
3
4
5
6
7
8
Dim rng As Range
Set rng = Range("PROJECTS")
For Each cel In rng.Cells
MsgBox "The value of cell at row " & cel.Row & " and column " & cel.Column & " is " & cel.Value
If cel.Value = "" Then Exit For
Next cel
End Sub
Thanks Ajay. I have tried something like that earlier and it didn’t work. I’ve in any case tried your version of it and it still doesn’t work. Ha, Ha, because it says “cel” isn’t defined, I have added a line “Dim Cel ” and that fixed it. It works a treat! Many, many, many thanks.
July 7th, 2009 at 7:26 amGreat
By the way, the code did work for me with Excel 2003. Out of curiosity - what version did you test it with ?
July 7th, 2009 at 10:35 ami have done one macro which compare sheet1 a1 with sheet 2 a1 now i just want to do it for each and every cell till it comes blank cell
November 13th, 2009 at 3:42 am@arpan use something like this in the for loop
November 13th, 2009 at 1:06 pmif cell.value =”" then exit for