Imagine you wanted to work to the bottom of a column of data in Excel until you reached the bottom. A “Loop” would help you do that. Loops are really handy. Macro loops allow you to repeat a certain action or actions until a particular condition is met (e.g. the data runs out).

    This is a key piece of code that’s going to help you loop to the bottom of a list. It’s a “Do until” loop. It works until it finds a cell that meets a certain condition. In this case it works until it finds a blank cell:
    'Comment: starts loop
    Do Until IsEmpty(ActiveCell)
    'Comment: insert or separately record code that manipulates data and place it here
    'Step down one row in column one
    ActiveCell.Offset(1, 0).Select
    'End loop
    There’s a fuller example of this loop at work under our definition for “Variables“.
    There are other types of loops apart from the do until loop. You only need one of them to work for you. With a bit of experimentation you will find that one might solve your problem in slightly fewer lines of code or take less time to run. Here are some examples of other macro loops solving the same problem.
    The “Do while” loop works while a condition is met. In this case it works while it can find a non-blank cell):
    'Start loop
    Do While ActiveCell.value <> ""
    'Insert your code that manipulates data the way you want and place it here
    'Step down one cell
    ActiveCell.Offset(1, 0).Select
    'End loop
    The “For each” loop works on each cell that meets a particular condition. In this case it works on each non-blank cell):
    'Tells Excel which cells to look at
    For Each MyCell In Range("A:A")
    'Sets the condition. Repeats for all cells that meet the condition
    If MyCell <> 0 Then
    'Insert your code here
    End If

Macros coverage from the financial modelling course

You’re looking at the macros glossary from our course material covering the use of macros in Excel financial modelling.

...join the conversation...

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