Excel VBA Loops

Loops are very important when coding as it can help repeat the same action without rewriting the same piece of code over and over.

There are a few different loop statements in VBA, we will see some examples of the most used once here.

VBA For … Next Loop

This statement has an opening and closing line, this is the basic structure:

Microsoft Excel VBA loops tutorial by DataMInded

An iteration is a process of repeating a step.

Iteration number 1 is the first time this code runs

Iteration number 2 is the second time the code run

Iteration number 5 is the 5 time the code is repeated

Let’s take an example and explain how this works.

We want to display the message “This is iteration number…” & the iteration number.

Open your VBA editor and declare an integer variable. The loop requires a number to count the number of times the code will be repeated, this is the loop counter.

Microsoft Excel VBA loops tutorial by DataMInded

Now let’s write the first line of the excel For Next loop. We will display our message 5 times, so the loop will start from 1 and repeat a maximum of 5 times:

image12.png

For every loop we want to display the message:

“This is iteration number…” & the iteration number.

The iteration number is our loop counter, in our example is i, so we will expect the message to be:

“This is iteration number 1” the first time the macro runs this loop:

Microsoft Excel VBA loops tutorial by DataMInded

As soon as the macro has finished the first iteration, we will use the end line in the loop to tell excel to move to the next iteration now:

Microsoft Excel VBA loops tutorial by DataMInded

This is the full VBA For Next loop statement.

Please run this code and you will see the following result:

The first message displayed:

“This is iteration number 1”

The second message displayed:

“This is iteration number 1”

And so on until the 5th iteration.

As you can see the code is tidy and non-repetitive. Our example is pretty small and you could possibly write each of the messages individually in VBA without too much work, but when you work with large ranges, this is almost impossible to do.

Let’s take another example and go through a range of cells and write the iteration numbers there:

We will record the value of i in each of the cells in this range:

Microsoft Excel VBA loops tutorial by DataMInded

Again we will declare our integer loop counter (please note, you can call this anything, it doesn’t have to be a single letter) we will call it loopCounter this time:

Microsoft Excel VBA loops tutorial by DataMInded

Let’s write the first line of the VBA loop statement now. As you can see we will write in cells from A2 to A10 so our range will be from 2 to 10:

Microsoft Excel VBA loops tutorial by DataMInded

For each of the loops, we want the loopCounter number to be entered in each of the cells values.

The first cell we want to enter the value in is cell A2, and our first loopCounter is 2, so we can express cell A2 as : Range(“A”&loopCounter)

The second cell we want to enter the value in is cell A3, and our second loopCounter is 3, so we can express cell A3 as : Range(“A”&loopCounter)

And so on ….so our statement inside the loop will be:

Microsoft Excel VBA loops tutorial by DataMInded

Let’s finish this excel loop statement by telling excel to move on to the next loopCounter when it has finished:

image17.png

Now let’s run this code, and the result should be the following:

Microsoft Excel VBA loops tutorial by DataMInded

In the examples above we performed the same action in each of the loops, but it doesn’t have to be that way. In some situations, depending on the cell value for example, we want the macro to perform different actions.

Let’s see an example,

In the following range, we want to enter the value 100 in each of the empty cells.

Microsoft Excel VBA loops tutorial by DataMInded

The first part of the code is the same as the example above, we will use a loop counter to move from cell to cell:

Microsoft Excel VBA loops tutorial by DataMInded

Next, we want to check first the cell value, if the cell is empty then we want to enter the value 100. We will use the VBA If Then statement to check the value:

Microsoft Excel VBA loops tutorial by DataMInded

Note that we do not need an Else block here as we do not want any action taken if the cell is not empty.

And lastly, we will close the loop by moving to the next cell:

Microsoft Excel VBA loops tutorial by DataMInded

Run this code and you should see the following result:

Microsoft Excel VBA loops tutorial by DataMInded

If you would like to read more about this topic, check out the Microsoft guide to using it here:

For...Next statement (VBA) | Microsoft Docs

VBA For Each Loop

The example above we can achieve it using the excel For Each Loop as well. The main purpose of this kind of loop is to move through objects in excel.

Let’s modify the example above using the VBA For Each Loop

We are now moving between objects in excel, we do not need the loop counter anymore.

We just need to tell excel what object we are using. In our Example it’s a range.

Let’s declare a Range variable:

image25.pngMicrosoft Excel VBA loops tutorial by DataMInded

We will move through the range: Range (“A2:A10”)

Let’s assign this range to a variable as well:

Microsoft Excel VBA loops tutorial by DataMInded

The structure that we will use is:

Microsoft Excel VBA loops tutorial by DataMInded

Our element is cell

Our Range is myRange

The first line of the statement:

Microsoft Excel VBA loops tutorial by DataMInded

Next we will check if the cell is empty and enter 100:

image28.pngMicrosoft Excel VBA loops tutorial by DataMInded

Let’s close the loop by moving to the next cell:

Microsoft Excel VBA loops tutorial by DataMInded

Run the code and you will see the same result as above.

If you would like to read more about this topic, check out the Microsoft guide to using it here:

Using For Each...Next statements (VBA) | Microsoft Docs

VBA Do While Loop

This type of loop will run for as long as the condition is True.

In the range below, we want to make red each of the cells that contain a name.

Microsoft Excel VBA loops tutorial by DataMInded

We can do this with the For Next Loop or with the For each Loop, but there is a more efficient and dynamic loop for this example and that is the Do While Loop.

Let me explain:

  1. If we use the VBA For Next Loop, first we need to specify the range, in this occasion it will be cells 2 to 8.

Later on the user adds 3 more names to the list. Our loop will not work past cell 8, we will need to manually update the range of the loop. This is not very practical, we cannot do this every time the sheet is updated.

  1. Let’s say we expand the range just in case there is more names in the future, we use the range from cell 2 to cell 16 with the For Next loop.

Now because the For Next loop is a fixed iteration loop, it will iterate through each cell even if the cell is empty. The macro will be long and inefficient.

To avoid the above issues we will use the Do While loop.

First we will need an iterator for this loop, we will need to specify where to start from (cell 2, so our iterator starting position will be 2) and we need this as loop counter so we can move to the next iteration:

Microsoft Excel VBA loops tutorial by DataMInded

Let’s write the first line of the Excel Do While statement:

Microsoft Excel VBA loops tutorial by DataMInded

So we want to repeat this loop for as long as the cell value is not empty.

And for each loop we want the colour of the cell to be RED:

Microsoft Excel VBA loops tutorial by DataMInded

Once we are done with this iteration we want to move to the next one, so let’s increment the loop counter by 1:

Microsoft Excel VBA loops tutorial by DataMInded

And as a last step, let’s give the instruction to Excel to loop to the next:

Microsoft Excel VBA loops tutorial by DataMInded

Run the macro and the result should be the following:

Microsoft Excel VBA loops tutorial by DataMInded

One thing to bear in mind when using the Do While loop: this is an infinite loop, meaning it doesn’t have a specified range, it will keep on running until the condition is not True anymore.

Make sure you are sure the condition will turn to False eventually or you will have a never ending loop!!

VBA Do Until Loop

The Excel Do Until Loop is almost identical to the Do while loop, it’s just expressed in a different way.

For our example above our loop was:

Do while the cell has a value

With the Do Until Loop we would express it:

Do Until you reach an empty cell, make the cells red until you reach the first empty cell.

Change the code we used in the previous example to:

Microsoft Excel VBA loops tutorial by DataMInded

Rerun the code and you will see the same results

As the Do While loop, this is an infinite loop, please make sure the loop condition will be met or you will have an infinite loop!

If you would like to read more about this topic, check out the Microsoft guide to using it here:

Do...Loop statement (VBA) | Microsoft Docs

Free Excel Shortcuts Cheat Sheet

Subscribe to download the file, and to get our latest Excel tips and discounts by email.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit