Loop statements are used to get Excel to repeat a piece of code a certain number of times. The number of times the code gets repeated can be specified as a fixed number (e.g. do this 10 times), or as a variable (e.g. do this until there are no more rows of data).
The are several ways in which loops can be constructed, depending on the circumstances in which they are going to be used. Quite often the wanted result can be obtained in different ways, depending on your preferences.
There are two basic kinds of loops, examples of both: Do…Loop and For…Next loops. The code to be repeated is placed between the key words.
For our first example open a new workbook and place the numbers 1 to 10 in cells A1 through to A10.
We are going to loop down the list, adding 5 to the number in column ‘A’.
Now open the VBE, add a new module and start a new sub routine call MyLoopStatement. Copy the code below into your new routine.
Public Sub MyLoopStatement()
Range("A1").Select
Do While IsEmpty(ActiveCell) = False
ActiveCell.Offset(0, 1).FormulaR1C1 = "=SUM(RC[-1]+5)"
ActiveCell.Offset(1, 0).Select 
Loop
End Sub
This routine loops down the list placing a formula in each of the cells to the right of the activecell. It will continue WHILE the active cell is not empty.
The next example uses Do until.
Range("A1").Select
Do
ActiveCell.Offset(0, 1).FormulaR1C1 = "=SUM(RC[-1]+5)"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell)
This example loops until it finds a blank cell. It is generally considered better practise to use Do Until, rather then Do While. Both of the above examples runs the code within the loop at least once before testing whether to move on. You could write the same Do Until / Do While loops in the following way.
Range("A1").Select
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(0, 1).FormulaR1C1 = "=SUM(RC[-1]+5)"
ActiveCell.Offset(1, 0).Select
Loop Moving the test to the start of the loop means it is carried out before carrying out any of the instructions within the loop. This could be useful if, for example, sometimes cells A1:A10 were blank.
if you know or can use VBA to find out how many times you want to repeat the code you can use a For… Next loop.
In the following example we know that we have 10 rows of data, so we can tell the code to loop 10 times.
Dim l As Long
Dim lRowCount As Long
lRowCount = Range("A1").CurrentRegion.Rows.Count - 1
' You could also try
' lRowCount = Cells.SpecialCells(xlCellTypeLastCell).Row
For l = 1 To lRowCount
ActiveCell.Offset(0, 1).FormulaR1C1 = "=SUM(RC[-1]+5)"
ActiveCell.Offset(1, 0).Select
Next l
For… Next loops are particularly good a looping through objects.
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
For Each ws In wb.Worksheets
Debug.Print ws.Name
Next ws
The above code would be quicker then:
Dim l As Long
Dim wb As Workbook
Set wb = ThisWorkbook
For l = 1 To wb.Worksheets.Count
Debug.Print wb.Worksheets(l).Name
Next l
As always, I hope the above examples have been helpful. If you have any questions or comments please do let us know.
