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 sht 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
Can you help? I’m tryng to call a a few columns and then run those through a for loop it looks something like this. When I type =answr(C29) into cell C29 the error is #NAME?
Private Sub UserForm_Click()
Dim answr As String
Set cell = Workbook(1).Worksheets(3).Range(“C4:C28″)
For i = 4 To 28
1
If cell(i).Text = 0 Then answr = “Transmitter is broken”
Else
If cell(i).Text > 80 Then answr = “too much pressure”
Else
anwr = “Good”
End If
June 2nd, 2010 at 11:02 amExit For
End Sub
@Kim – Not sure how you exactly plan to use the code, but something like this may help:
Private Sub UserForm_Click()
Dim j As Long
j = 100
For i = 1 To 10
If j < 10 Then
MsgBox “asdf”
ElseIf j = 90 Then MsgBox “sdfasdf”
Else: MsgBox “100″
End If
Next i
End Sub
Regards,
June 2nd, 2010 at 11:10 pmSHOULD BE “Next sht” NOT “Next i” (caps just to emphise).
July 23rd, 2010 at 3:34 pmSub my_for_loop3()
For Each sht In ActiveWorkbook.Worksheets
MsgBox sht.Name
Next i
End Sub
I am tryting to copy Column A to colum H. Col A has values every 4th rows. I would like to list each value of A col in every 6th row in H col. This is my code but maybe my loop is not right or something. I didn’t get error message but Col A disappear but no values in H col. What did i do wrong? Was i supposed to use Copy and do destination?
August 19th, 2010 at 1:35 pmthank you so much,
Sub column()
Dim i As Integer, j As Integer
For i = 1 To 29
For j = 1 To 60
Cells(i, 1).Value = Cells(j, 9).Value
i = i + 4
j = j + 6
Next j
Next i
End Sub
Well, I am new to VBA but this worked to me to skip one iteration of i:
Sub my_for_loop6()
For i = 0 To 5
If (i = 4) Then
i = i + 1
Else
GoTo a:
End If
a:
MsgBox (i)
Next i
End Sub
April 8th, 2011 at 8:49 ami will learn from your example ,thank you
April 15th, 2011 at 12:38 pmHi, I need help!!!
October 16th, 2011 at 4:33 pmI am trying to program a simple set of robots. I have programmed the timing of the robots in (increases by 10s every row) and now I have to program in so that for the cells containing 0s to 30s the adjacent column displays R’s (to indicate a red robot) and then for the cells displaying 40s and 50s the adjacent column displays G’s, and then it continues in steps of 4 R’s, 2 G’s etc. Does anyone know of a way I could do this????? I know it’s a loop function but I don’t know how to get the loop function to skip rows? Any help would be great
This has been of a great help. Thanks !!!
February 8th, 2012 at 2:27 amcan i place a loop within a function in vba?
ie:
function mypi(tol)
do while n>= tol
February 28th, 2012 at 1:33 pmn=-3^-k/2*k+1
sum=sum+n
k=k+1
loop
end function
@ajay
May 15th, 2012 at 3:07 amMany many thanks for your solution it really helped me =)))
Thanks for the great For..Next loop explanations. I get it now and can apply it to other uses.
September 17th, 2012 at 10:27 amGood afternoon. I have the following code to populate a template from a workbook and it works great for row 2. I need to loop it so it repeats this macro for all the rows of data in the sheet. What do I need to do?
ChDir “C:\PRT”
October 2nd, 2012 at 2:04 pmWorkbooks.Open Filename:= _
“C:\PRT-Template.xlsx”
Windows(“Sizes.xlsm”).Activate
Range(“A2″).Select
Selection.Copy
Windows(“PRT-Template.xlsx”).Activate
Range(“F6:J6″).Select
ActiveSheet.Paste
Windows(“Sizes.xlsm”).Activate
Range(“B2″).Select
Application.CutCopyMode = False
Selection.Copy
Windows(“PRT-Template.xlsx”).Activate
Range(“F7:Z7″).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Dim strName As String
strName = Range(“F6″).Text
ActiveSheet.SaveAs “C:\PRT-” & strName
Nice work! Thanks so much. I’ve tagged your site and I’ll be back…
December 3rd, 2012 at 9:11 pm