In the article What Is a Variable in VBA?, we wrote the following code. It writes the value of the variable i in three different cells:
This code works. But to write in 100 cells, you would have to write the same line 100 times. This is far too much.
This is exactly the case where we create a loop: the program goes back several times to the same line of code, and only the value of the variable changes. In this way, at each iteration of the loop, the value of the variable is incremented, and the program writes in a different cell.
There are two types of loops:
- For … Next: when you know the number of iterations to make
- Do While … Loop: when the loop must stop as soon as a condition is met
1. How to write a For … Next loop
We want to make a loop on the first 10 cells of our worksheet. To do this, we start with the instruction For, followed by the variable i. This variable changes the row number. Its value starts at 1 and goes up to 10.
Inside the For … Next loop, we write the same instruction as before: we write the value of the variable in the cell of the matching row.
Sub WriteTenCells()
For i = 1 To 10
Cells(i, 1) = i
Next i
End Sub
When VBA runs this loop, here is what happens:
- For i = 1 To 10: the variable i receives the start value, 1.
- Cells(i, 1) = i: VBA writes the value of i in row i. The first time, it writes 1 in A1.
- Next i: VBA adds 1 to i, and goes back to the line For. This is the line i = i + 1 of our first code, but VBA does it for you.
- As long as i is not greater than 10, the loop continues. After 10, it stops automatically, and VBA reads the line after Next.
To fill 1,000 cells, you only change one number: For i = 1 To 1000. The code stays the same size.
2. When you don’t know the number of rows: Do While
The Do While loop is mostly used to scan all the values of a column, and to stop when the last cell is reached. This is a very common task in VBA, because you cannot know in advance if you will have 50, 100 or 1,000 rows to process.
With a Do While loop, you must give the condition that stops the loop. Very often, this condition tests if the content of the cell is empty or not.
⚠️ Be careful: in a For … Next loop, the increment is automatic. In a Do While loop, it is not. You must write a line of code that increments the variable. If you forget it, the program stays on the first cell of your list forever, and the only way to stop it is to press Ctrl + Break. And today, many laptops no longer have a Break key. So be very careful with this line.
In our example, column A is already filled with the values 1 to 10, typed before we run the macro. This is not like the For … Next example, where the loop writes the values while it runs. Here, the loop only reads the values of column A, and writes the double of each value in column B.
You must also give the variable its start value before the Do While loop: this is the row of the first cell to read.
Sub DoubleValues()
i = 1
Do While Cells(i, 1) <> ""
Cells(i, 2) = Cells(i, 1) * 2
i = i + 1
Loop
End SubIn this code:
- i = 1: the loop starts in row 1
- Do While Cells(i, 1) <> “”: the loop continues as long as the cell in column A is not empty
- i = i + 1: the line that increments the variable, to go to the next row
3. The condition at the start or at the end
The condition can be written on the first line of the loop, or on the last line. Both are allowed:
Do While Cells(i, 1) <> ""
...
Loop
Do
...
Loop While Cells(i, 1) <> ""It all depends on when you want to test the condition. With Do While … Loop, the condition is tested before each turn: if the first cell is empty, the loop never runs. With Do … Loop While, the condition is tested after each turn: the loop always runs at least once.
What’s next
Ask an AI to write a loop, and you will often see the line Dim i As Integer at the top of the code. It declares the type of the variable i. But Integer is an old type, and it can make your macro crash on a large worksheet. This is explained in VBA Variables: Why the AI Still Uses Integer and Single.