Excel VBA – Looping Part 1

shutterstock_173122277

One thing you cannot record as part of an Excel macro is looping i.e. moving from one row/column, worksheet or even workbook to another. You can record copying a formula down a column or similar, but it records it as AUTOFILL and therefore records the cells as absolute references meaning that if you have any variation to the number of rows etc. when you recorded the macro, it won’t work properly. So this is where you need to do LOOPS in VBA.

There are two basic loops you can use;

  • Do While/Until…..Loop
  • For each…..next

In this first blog I am going to go through the DO WHILE/UNTIL type loop.

Let’s take a simple example, where I want to add a formula in column C adding up values in columns A and B.

 

Simple table to add a formula column to

Simple table to add a formula column to

The code required to do this would be as follows;

First we need a start point i.e. where do we want to start the loop from? In this example, the first cell is C2.

Range(“C2”).Select

or

Cells(2,3).Select (use whichever method you prefer)

Then we need to give an instruction when to stop looping. Here, we want the code to stop running when the cell(s) to the left of the formula cell is/are empty. You would need to decide how to handle this depending on the data you have – are all columns full of data? Are there any blanks in either or both columns? This will determine what you need to put in your DO UNTIL/WHILE command.

Do Until ActiveCell.Offset(0,-1).Value = “” (stop when cell to left is blank)

Or

Do Until Activecell.Offset(0,-2).Value = “” And ActiveCell.Offset(0,-1).Value = “” (stop when both columns A and B are blank)

If you are not familiar with the OFFSET function in VBA, it allows you to reference cells based on their distance in terms of ROWS and then COLUMNS from the selected or active cell.

Positive numbers refer to ROWS down and COLUMNS to the right, whereas negative numbers refer to ROWS up, and COLUMNS to the left of the active cell. If you had cell C5 selected, for example, then;

ActiveCell.Offset(2,-1) would refer to cell B7 – two rows down & 1 column to the left.

The alternative to DO UNTIL is DO WHILE. Works the same way, just a question of logic to tell the code when to stop. If using DO WHILE in our original example the code would be like this;

Do While ActiveCell.Offset(0,-1)<> “” (keep going as long as the cell in the column to the left is not empty)

Personally, I tend to go with DO UNTIL…seems more logical to me, but the choice is yours.

Get into the habit of adding in the word LOOP to make sure you close off the pairing of code words. This will give us the following code so far;

Range(“C2”).Select

Do Until ActiveCell.Offset(0,-1).Value = “”

Loop

One thing you will always need in a LOOP, is movement. You need to add a line that tells the editor which direction to travel in as part of the loop. If you don’t do this, then you stay permanently in the start cell and therefore in a permanent loop. If that happens you can use CTRL + BREAK to stop the loop and show the END/DEBUG window. If it is the LOOP causing the problem the code word LOOP should be highlighted in yellow.

In this example, I need to move down a row each time so I will add the line;

ActiveCell.Offset(1,0).Select

This should be just above the LOOP line. If you go on to do more complex loops, think carefully where the movement comes in. I’ll show you a simple example later.

The final part of the code is to tell the code what to do with each loop. In this example it is to add a formula. Here, I will be using R1C1 cell referencing style. If you don’t know what that is, or how it works check out my blog about R1C1 reference style ( http://wp.me/p2EAVc-4I ). Good to know when it comes to VBA formulas!

My final code will therefore be;

Range(“C2”).Select

Do Until ActiveCell.Offset(0,-1).Value = “”

          ActiveCell.FormulaR1C1 = “=SUM(rc[-2]:rc[-1])”

          ActiveCell.Offset(1,0).Select

Loop

When you have a loop set up, it’s always worth a look step by step using the DEBUG tools to see the loop in action and to check that it’s doing everything it’s meant to be doing, especially if any variables are concerned.

I mentioned earlier about being careful where you put your movement line in your code. Let’s take a look at a bit of code that removes blank lines from a worksheet;

Range(“C2”).Select

Do Until ActiveCell. Value = “End”

          If ActiveCell.value = “” then

                   Rows(ActiveCell.Row).Delete

          Else

                   ActiveCell.Offset(1,0).Select

          End if

Loop

Here, the movement is built into the IF statement, rather than at the end just before the LOOP. This is necessary because when you delete a row, all rows underneath automatically move up to “fill the void”. If you move each time you delete a line, and there are two or more blank rows in succession, the next blank row would move up to your current location, and then the cursor would move down a row, completely missing out testing the row that had just moved up. The video below will show clearly what happens if the movement is just before the loop, and then with the movement in the IF statement.

So there you have some basic code to create a loop. Looping Part 2 will look at the FOR EACH…NEXT type loop which is a quicker and more efficient way of looping, letting you loop through multiple worksheets and workbooks in seconds, but do get used to the DO UNTIL/WHILE loop. It has its uses and is easier to debug and test than the FOR…NEXT type loops as you will see in Part 2 of looping.

Advertisements

Posted on September 9, 2014, in VBA and tagged , , , , . Bookmark the permalink. 3 Comments.

  1. You really shouldn’t use ActiveCell and .Select. It’s much better to directly call to a .Range object. The ActiveCell can be changed by the user while the macro is running, and then confusion ensues.

    • Agreed that activeCell is something you should learn to avoid over time but this is aimed at beginners and the concept of going somewhere to do something is much easier to understand. At least the person knows where they are before they undertake anything. As they become more confident with coding then yes avoid activeCell and select where possible.

  2. You can know if the cell have a formula with:

    Do While ActiveCell.Formula Empty

    loop

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: