Using Loops in VBA in Microsoft Excel





Why Loops?

The purpose of a loop in VBA is to get Excel to repeat a piece of code a certain number of times. How many 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 for as many times as there are rows of data).Loops can be constructed many different ways to suit different circumstances. Often the same result can be obtained in different ways to suit your personal preferences. These exercises demonstrate a selection of different ways to use loops.There is two basic kinds of loops, both of which are demonstrated here:

  • Do…Loop and
  • For…Next loops.

The code to be repeated is placed between the key words.

Open the workbook Using Loops in Visual Basic Application and take a look at the four worksheets. Each contains two columns of numbers (columns A and B). The requirement is to calculate an average for the numbers in each row using a VBA macro.

Now open the Visual Basic Editor (Alt+F11) and take a look at the code in Module1.  You will see a number of different macros. In the following exercises, first run the macro then come and read the code and figure out how it did what it did.

You can run the macros either from the Visual Basic Editor (VBE) by placing your cursor in the macro and pressing the F5 key, or from Excel by opening the Macros dialog box (ALT+F8) choosing the macro to run and clicking Run. It is best to run these macros from Visual Basic Editor by using Debug > Step Into (by pressing F8) so you can watch them as they work.

Instruction

If Developer Tab is not in the Ribbon..

  • Open Excel.
  • Go to VBA Editor (press Alt + F11)
  • Go to Immediate Window. ( Ctrl + G)
  • Write below Code.
    • Application.ShowDevTools = True

How to Insert VBA code in Excel

  • Go to Developer Tab > Code Group > Visual Basic
  • Click Insert > Module.
  • Will open a Blank Module for you.
  • Write / Paste provided code in that Module

Untitled-1

How to Run VBA code in Excel

  • Select anywhere in between the Code, Sub… End Sub
  • Click Run & Run Sub or F5Untitled-1

Exercise 1: Do… Loop Until…

 

The object of this macro is to run down column C as far as is necessary putting a calculation in each cell as far as is Column B is filled or NotEmpty.

Select cell C2 before run the macro as macro is based on ActiveCell.Untitled-1

 

Here’s the code:

Sub Loop1()
'This loop runs until there is nothing in the Previous column
Do
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
End Sub

This macro places a formula into the active cell, and moves into the next cell down. It uses Loop Until to tell Excel to keep repeating the code until the cell in the adjacent column (column B) is empty. In other words, it will keep on repeating as long as there is something in column B.

=Average(RC[-1],RC[-2]) is same as =AVERAGE(B1,A1) with respect to C1,  in R1C1 style, it’s trying to say, that, go for average of One Column left (c-1) and Two Column left (C-2)’ s data.

 

    Delete the data from Column C and ready for the next exercise

 

Exercise 2: Do While… Loop

The object of this macro is to run down column C as far as is necessary putting a calculation in each cell as far as is necessary.

Select cell C2 before run the macro as macro is based on ActiveCell.

Sub Loop2()
'This loop runs as long as there is something in the Previous column
Do While IsEmpty(ActiveCell.Offset(0, -1)) = False
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
ActiveCell.Offset(1, 0).Select
Loop
End Sub

 

The function IsEmpty = False means “Is Not Empty”

This macro does the same job as the last one using the same parameters but simply expressing them in a different way. In previous code, we are first running the Do… Loop code, then checking if criteria matched or not, where,

In this code, we are first checking the condition, and if matched then we are running the Do… Loop.

It uses Loop Until to tell Excel to if adjacent column (column B) is not empty, then only repeat the code.

 

Delete the data from Column C and ready for the next exercise

Exercise 3: Do While Not… Loop

The object of this macro is to run down column C as far as is necessary putting a calculation in each cell as far as is necessary.

Select cell C2 before run the macro as macro is based on ActiveCell.

Here’s the code:

Sub Loop3()
'This loop runs as long as there is something in the Previous column
Do While Not IsEmpty(ActiveCell.Offset(0, -1))
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
ActiveCell.Offset(1, 0).Select
Loop
End Sub

 

This macro makes exactly the same decision as the last one but just expresses it in a different way. IsEmpty = False means the same as Not IsEmpty. Sometimes you can’t say what you want to say one way, so VBA often offers an alternative syntax.

IsEmpty(ActiveCell.Offset(0, -1)) = False & Not IsEmpty(ActiveCell.Offset(0, -1)), are same way to say, do the repeated task, until adjacent left cell is filled with something, or not Empty.

 

Delete the data from Column C and ready for the next exercise

 

Exercise 4: Including an IF statement

 

The object of this macro is as before, but without replacing any data that may already be there.

Move to Sheet2, select cell C2 and run the macro Loop4.

Untitled-1

Sub Loop4()
' This loop runs as long as there is something in the Previous column
' It does not calculate an average if there is already something in the cell
Do
If IsEmpty(ActiveCell) Then
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
End Sub

 

The previous macros take no account of any possible contents that might already be in the cells into which it is placing the calculations. This macro uses an IF statement that tells Excel to write the calculation only if the cell is empty. This prevents any existing data from being overwritten.

The line telling Excel to move to the next cell is outside the IF statement because it has to do that anyway.

 

Exercise 5: Avoiding Errors

This macro takes the IF statement a stage further, and doesn’t try to calculate an average of cells that are empty.

First, look at the problem. Move to Sheet3, select cell C2 and run the macro Loop4.

Note that because some of the pairs of cells in columns A and B are empty, the =AVERAGE function throws up a #DIV/0 error (the Average function adds the numbers in the cells then divides by the number of numbers – if there aren’t any numbers it tries to divide by zero and you can’t do that!).

Untitled-1

Sub Loop5()
' This loop runs as long as there is something in the NEXT column
' It does not calculate an average if there is already something in the cell
' nor if there is no data to average (to avoid #DIV/0 errors).
Do
If IsEmpty(ActiveCell) Then
If IsEmpty(ActiveCell.Offset(0, -1)) And IsEmpty(ActiveCell.Offset(0, -2)) Then
ActiveCell.Value = “”
Else
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
End If
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub

 

Note that this time there are no error messages because Excel hasn’t tried to calculate averages of numbers that aren’t there, and criteria checking cell has been changed from adjacent column (B0 to adjacent Column D, as for testing purpose, we have change some of Cell in column B to Empty.

In this macro there is a second IF statement inside the one that tells Excel to do something only if the cell is empty. This second IF statement gives excel a choice. Instead of a simple If there is an If and an Else. Here’s how Excel reads its instructions…

“If the cell has already got something in, go to the next cell. But if the cell is empty, look at the corresponding cells in columns A an B and if they are both empty, write nothing (“”). Otherwise, write the formula in the cell. Then move on to the next cell.”

Untitled-1

Exercise 6: For… Next Loop

 

If you know, or can get VBE to find out, how many times to repeat a block of code you can use a For… Next loop.

select cell C2 and then run the macro Loop6.

 

Sub Loop6()
' This loop repeats for a fixed number of times determined by the number of rows
' in the range
Dim i As Long
lastcell = Range("A" & Cells.Rows.Count).End(xlUp).Row
For i = 0 To lastcell
ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
ActiveCell.Offset(1, 0).Select
Next i
End Sub

 

This macro doesn’t make use of an adjacent column of cells like the previous ones have done to know when to stop looping. Instead it counts the number of rows in Column A and find the last filled cell by using below method.

Range(“A” & Cells.Rows.Count).End(xlUp).Row

That means, it goes to the last cell in A column (In case of Excel 2007 prior,  cells # A65536 ( 2^16), and for excel 2007 +  Cell Number A10485776 ( 2 ^ 20))  and then in come One Step Up, with pressing Control Key.. End(XlUp) and uses the For… Next method to tell Excel to loop that number of times.

In between any stage, if need to exit the loop, we can use EXIT FOR keyword to exit the FOR LOOP.

Exercise 7: Getting the Reference From Somewhere Else

In the above code we have checked Column A, to set the No Of Repeating Time,

 Range(“A” & Cells.Rows.Count).End(xlUp).Row

Instead of “A” we can use any other cell, to set the lastCell. By doing something like..

Range(“G” & Cells.Rows.Count).End(xlUp).Row

If you wanted to construct a loop that always ran a block of code a fixed number of times, you could simply use an expression like:

Instead of For i = 0 To LastCell , we can expression like of For i = 0 To 23. It will loop through Rows, and increase i form 0 to 23 / lastCell

Exercise 8: About Doing Calculations…

All the previous exercises have placed a calculation into a worksheet cell by actually writing a regular Excel function into the cell (and leaving it there) just as if you had typed it yourself. The syntax for this is:

ActiveCell.FormulaR1C1 = “TYPE YOUR FUNCTION HERE”

These macros have been using:

ActiveCell.FormulaR1C1 = “=Average(RC[-1],RC[-2])”

Because this method actuall change, just like regular functions – because they are regular functions. The calculating gets done in Excel because all that the macro did was to write the function.

If you prefer, you can get the macro to do the calculating and just write the result into the cell. VBA has its own set of functions, but unfortunately AVERAGE isn’t one of them. However, VBA does support many of the commoner Excel functions with its WorksheetFunction method.

On Sheet1 select cell C2 and run the macro Loop1.

Take a look at the cells you just filled in. Each one contains a function, written by the macro.

Now delete the contents from the cells C2:C20, select cell C2 and run the macro Loop8.

Here’s the code:

Sub Loop7()
Do
ActiveCell.Value = WorksheetFunction.Average(ActiveCell.Offset(0, -1).Value, _
ActiveCell.Offset(0, -2).Value)
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
End Sub

Take a look at the cells you just filled in. This time there’s no function, just the value. All the calculating was done by the macro which then wrote the value into the cell.



11 thoughts on “Using Loops in VBA in Microsoft Excel

  1. Thanks for the tip! I have a worksheet with a column that has intermittent empty cells. I have a macro that populates all of the empty cells below a populated cell with that cell’s value. I applied the Loop(2) technique and it continued on and kept going until I “ESC” out. I’m wondering if you can tell me what I did incorrectly? Thanks again!

  2. Thanks you
    It’s very useful

    Which is the best way to introduce to Excel VBA. I am 46 year old and I think happy to learn about excel

  3. This page gives an excellent overview of different loops and their syntax.I need still more on loops and their usage examples.

  4. “The following can be used to delete unwanted lines from a large sheet .

    Do
    if activecell.value=”””” then
    selection.entirerow.delete
    else
    dim row
    row=1
    selection.offset(row).select
    end if
    loop until activecell.value=””Target”” “

  5. If I talk about exercise 5 the given code is not working properly. After running the macros It is giving result for C1 only.
    A B C
    6 7 6.5
    16 18
    17 6
    18 9
    15 6
    16 17

    The code is:
    Sub Loop3()
    ‘ This loop runs as long as there is something in the NEXT column
    ‘ It does not calculate an average if there is already something in the cell
    ‘ nor if there is no data to average (to avoid #DIV/0 errors).
    Do
    If IsEmpty(ActiveCell) Then
    If IsEmpty(ActiveCell.Offset(0, -1)) And IsEmpty(ActiveCell.Offset(0, -2)) Then
    ActiveCell.Value = “”
    Else
    ActiveCell.FormulaR1C1 = “=Average(RC[-1],RC[-2])”
    End If
    End If
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, 1))
    End Sub

  6. Pls ignore the last one:

    If I talk about exercise 5 the given code is not working properly. After running the macros It is giving result for C1 only.
    A B C
    6 7 6.5
    16 18
    17 6
    18 9
    15 6
    16 17

    The code is:
    Sub Loop3()
    ‘ This loop runs as long as there is something in the NEXT column
    ‘ It does not calculate an average if there is already something in the cell
    ‘ nor if there is no data to average (to avoid #DIV/0 errors).
    Do
    If IsEmpty(ActiveCell) Then
    If IsEmpty(ActiveCell.Offset(0, -1)) And IsEmpty(ActiveCell.Offset(0, -2)) Then
    ActiveCell.Value = “”
    Else
    ActiveCell.FormulaR1C1 = “=Average(RC[-1],RC[-2])”
    End If
    End If
    ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell.Offset(0, 1))
    End Sub
    Please suggest whats going wrong with me.

Leave a Reply

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


− seven = 2

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>