Loop Structures

For ... Next 

Use For ... Next loop if the number of loops is already defined and known.  A For ... Next loop uses a counter variable that increases or decreases in value during each iteration of the loop.  This loop structure is being used the most for our examples on this site.  

Here is an example of the For ... Next loop:

    For i = 1 to 10
        Cells(i, 1) = i
    Next i

    

In this example, i is the counter variable from 1 to 10.  The looping process will send value to the first column of the active sheet and print i (which is 1 to 10) to row 1 to 10 of that column.

Note that the counter variable, by default, increases by an increment of 1


For ... Next Loop With Step

You can use the Step Keyword to sepcify a different increment for the counter variable.

For example:

    For i = 1 to 10 Step 2
        Cells(i, 1) = i
    Next i

This looping process will print values with an increment of 2 on row 1, 3, 5, 7 and 9 on column one.

    

You can also have decrement in the loop by assign a negative value afte the Step keyword.

For example:

    For i = 10 to 1 Step -2
        Cells(i, 1) = i
    Next i

This looping process will print values with an increment of -2 starts from 10 on row  10, 8, 6, 4 and 2 on column one.

    

Do While ... Loop

You can use the Do While ... Loop to test a condition at the start of the loop.  It will run the loop as long as the condition is ture and stops when the condition becomes false.  For Example:

   i = 1
    Do While i =< 10
        Cells(i, 1) = i
        i = i + 1
    Loop

This looping process yields the same result as in the For ... Next structures example.

One thing to be caution is that sometimes the loop might be a infinite loop.  And it happens when the condition never beomes false.  In such case, you can stop the loop by press [ESC] or [CTRL] +[BREAK]


Do Until ... Loop 

You can test the condition at the beginning of the loop and then run the loop until the test condition becomes true.

Example:

    i = 1
    Do Until i = 11
        Cells(i, 1) = i
        i = i + 1    
    Loop

This looping process yields the same result as in the For ... Next structures example

Do ... Loop WhileWhen you want to make sure that the loop will run at least once, you can put the test at the end of loop.  The loop will stop when the condition becomes false.   (compare this loop structure to the Do ... While Loop.)

For Example:
    

   i = 1
    Do 
        Cells(i, 1) = i
        i  = i + 1
    Loop While i < 11

This looping process yields the same result as in the For ... Next structures example


Do ... Loop Until

This loop structure, like the Do ... Loop While, makes sure that the loop will run at least once, you can put the test at the end of loop.  The loop will stop when the condition becomes true.   (compare this loop structure to the Do ... Until Loop.)

For Example:
    

   i = 1
    Do 
        Cells(i, 1) = i
        i  = i + 1
    Loop Until i = 11

  
This looping process yields the same result as in the For ... Next structures example.