VBA For Loop – For Next and For Each In Next




TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

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.

for-loop-in-vba

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 !)

for-loop-vba-error-run-time-error-438

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.

TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. Peter Bode wrote:

    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”)
    For Each MyObject In MyCollection
    If MyObject = “=” Then
    ‘ <<<>>>
    Exit For
    End If
    Next

  2. Ajay wrote:

    Peter you can try this:

    1
    2
    3
    4
    5
    6
    7
    8
    Sub try2()
    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
  3. Peter Bode wrote:

    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.

  4. Ajay wrote:

    Great :-) By the way, the code did work for me with Excel 2003. Out of curiosity – what version did you test it with ?

  5. arpan wrote:

    i 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

  6. Ajay wrote:

    @arpan use something like this in the for loop
    if cell.value =”" then exit for

  7. Kim wrote:

    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
    Exit For
    End Sub

  8. Ajay wrote:

    @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,

  9. Randall wrote:

    SHOULD BE “Next sht” NOT “Next i” (caps just to emphise).
    Sub my_for_loop3()
    For Each sht In ActiveWorkbook.Worksheets
    MsgBox sht.Name
    Next i
    End Sub

  10. ttalgi wrote:

    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?
    thank 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

  11. Skazis wrote:

    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

  12. odisious wrote:

    i will learn from your example ,thank you

  13. Peter Gill wrote:

    Hi, I need help!!!
    I 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 :D

  14. Jim wrote:

    This has been of a great help. Thanks !!!

  15. Jay wrote:

    can i place a loop within a function in vba?
    ie:
    function mypi(tol)

    do while n>= tol
    n=-3^-k/2*k+1
    sum=sum+n
    k=k+1
    loop
    end function

  16. Elbacheur wrote:

    @ajay
    Many many thanks for your solution it really helped me =)))

  17. CATIA Macros wrote:

    Thanks for the great For..Next loop explanations. I get it now and can apply it to other uses.

  18. Reed wrote:

    Good 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”
    Workbooks.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

  19. Steven White wrote:

    Nice work! Thanks so much. I’ve tagged your site and I’ll be back…

  20. Ravinder Yadav wrote:

  1. How to Create a Dashboard in Excel | da TaB is On wrote:

    [...] dashboard, comment profusely and write easily understandable code. You can learn more about the VBA For Next and For Each in Next loop here and do until loop [...]

  2. Excel Macro | da TaB is On wrote:

    [...] can refer to some of the previous articles dealing with this. You can learn more about VBA For Loop here. For the Do While Loop please refer to this [...]

  3. my Programming | Pearltrees wrote:

    [...] VBA For Loop – For Next and For Each In Next | Excel & VBA – da Tab Is On 5 6 4 7 [...]

  4. Help with VBA...better way to do this simple task, please. wrote:

    [...] structures try the following: XL: How to Use Looping Structures in Visual Basic for Applications VBA For Loop – For Next and For Each In Next | Excel & VBA – Databison Excel VBA Loops. Excel Training VBA lesson 10 Excel VBA Loops: Correct/Efficient Uses of Excel [...]

  5. excel vba stop for each? | Askjis wrote:

    [...] 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() … How to Use Looping Structures in Visual Basic for Applications VBA For Loop – For Next and For Each In Next | Excel & VBA … – read more [...]

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel