Using Loops in Microsoft Excel VBA

Loops in Microsoft Excel VBA are designed to get Excel to repeat a piece of code a specific number of times. The number of repetitions in the loop can be specified as a fixed number (e.g. loop 10 times), or even as a variable (e.g. loop until no more data).

Excel Loops can be written different ways depending on the circumstances, and often the same result can be achieved using two or three different methods.

The two basic loops in Excel are Do (your code here) Loop and For (your code here) Next loops. Then theexcel-loops code you want to repeat is placed where it says (your code here).

So let’s look an example whereby I will demonstrate both loops to achieve the same result. So in the image you will see I have columns one and two populated with numbers. The first column has 2 to 20 and the second column has 3 to 30. Using loops we will add these figures together and output the results in a different column. The VBA code for the loops must be written in a module within Excel’s VB Environment.

The first loop to be demonstrated will be the Do Until loop. This tells Excel to continue the loop until a criteria has been met that you specify.

Sub Do_Until()

X = 3
Do Until Cells(X, 1) = ""
Cells(X, 3) = Cells(X, 1) + Cells(X, 2)
X = X + 1
Loop

End Sub

do-until-loopLet’s look at the code line by line.

X = 3
Here we using the variable X to hold a value starting at 3. This is because on the excel sheet, the first row starts at row 3. If it was row one, then we would write X=1.

Do Until Cells(X, 1) = ""
This is where we specify the criteria that must be met in order to stop the loop and thus end the routine. So we have said do the routine until cell(x,1) is blank. Remember X is a variable, so could be any number. The two numbers inside the brackets refer to a cell location – X represents the row number and the 1 represents the column number.

Cells(X, 3) = Cells(X, 1) + Cells(X, 2)
This line here calculates the sum of the numbers in column one and two and outputs the result in the same row, but in column 3.

X = X + 1
This line tells Excel to increment the variable X by 1. Remember that X represents the row numbers.

Loop
This tells Excel to loop the previous code.

Ok, so now lets try another way of looping through code using the Do While loop. This will loop through a piece of code until a criteria that you specify ceases to exist. The code for this is as follows:

Sub While_Until()

X = 3
Do While Not Cells(X, 1) = ""
Cells(X, 4) = Cells(X, 1) + Cells(X, 2)
X = X + 1
Loop

End Sub

do-while-loopThis time let’s look at the line of code that is different from the first example.

Do While Not Cells(X, 1) = ""
This tells excel to continue the loop while there is content in cells (X,1). Again remember that X is a variable and represents the row number, and 1 is the column number. Note the use of Not in the line. So essentially the line says, Perform the loop while the cells in column one are NOT blank.

So you will see in the image, that the Do While column has the exact same results as the Do Until column.

This time let’s use the For Next loop. This loop would be used where you specify how many times you want the loop to cycle through the VBA code.

Sub For_Next()

For X = 3 to 12 Step 1
Cells(X, 5) = Cells(X,1) + Cells(X, 2)
Next X

End Sub

Again, as with the other loops there is only minimal code required for this example. Let’s look at the code line by line:

For X = 3 to 12 Step 1
This is where we specify how many times we want to loop through the code. So in the image, you will see that the numbers start in row 3 and end in row 12. So we simply tell Excel to loop through the code starting at 3, increment this value by 1 for every loop, and finish at 12. If you wanted, you could miss every other cell by changing the Step 1 to Step 2

Cells(X, 5) = Cells(X,1) + Cells(X, 2)
This line performs the same calculation as the previous two loops. It adds the two numbers together in each row, and outputs the total to another cell in the same row, in this case column 5.

Next X
This line here tells Excel to go the next cycle in the loop. In doing so it increments the variable X by 1.

for-next-loopAs with the other two loops demonstrated you will see that the For Next loop has provided the same results.

Once you get started with loops, you can then nest loops within loops. But it starts to get more technical and confusing, especially when you add in If statements as well!

I hope you found this introduction to Excel loops useful and informative?

3 thoughts on “Using Loops in Microsoft Excel VBA

  1. Hernan

    ok this is great! but I have a question, I need my loop to do the following, copy and paste the entire 1st column in a new tab and continue doing that until the last column of data. Your code calculate cell by cell right?

  2. Glenn Umnali

    i have the basic codes and anyone who can placed a loop in my code:

    Private Sub CommandButton1_Click()
    If OptionButton1.Value = True Then
    Sheet2.Range(“d2”).Value = “L-Paid”
    Sheet2.Range(“e2”).Value = “Setup Approved”

    ElseIf OptionButton2.Value = True Then
    Sheet2.Range(“d2”).Value = “B-Paid”
    Sheet2.Range(“e2”).Value = “Waiver Approved”
    Else
    End If
    Sheet2.Range(“c2”).Value = TextBox1.Text

    End Sub

  3. David

    Hi, I have a large complex excel sheet (which includes retrieving data from a database to use in its calculations). The sheet resolves to a single value in a particular cell and changes by reference to the date.

    I want to populate a column with the results of the sheet alongside each reference date. I’m pretty sure this is easy and I would really appreciate you pointing me to the best method for doing this.

    Many thanks
    David

    Output required

    Date Sheet result
    1 Dec 2015 342.5
    2 Dec 2015 375.2
    3 Dec 2015 366.7
    4 Dec 2015 395.6
    etc

Leave a Reply

Your email address will not be published. Required fields are marked *