How to Stop a Looping Macro
 
 
  Back
 
Let’s say you are processing a column of data. You want Excel to examine a cell, perform some action on that cell and then move down to the next cell and repeat itself. This is fine, most of us have written such macro. However, how do we get the macro to stop? In other words, how does the macro know when it's at the end of a column of data? Skip the boring intro

This exercise will present four concepts related to this question:

  • Stopping the looping macro
  • A simple example of processing the cells
  • A method to ensure the macro starts properly
  • Some finishing touches


This example assumes the data is copied into the worksheet, probably the result of a download or other similar source and the layout of the data assumes you have a column of data with data in every cell. The length of the column changes daily. 

Line numbers are presented for purposes of clarity in this article. They are NOT a part of the actual code. The line numbers are consistent throughout this article, therefore, it may appear line numbers are missing, however, when you get to the end of the article, you will see all the line numbers.

The macro samples in this article will be written showing only one objective at a time, the next example will add code to show the next objective and so on. 

The end of this article contains the completed macro. You may want to jump to this section, skipping the explanations along the way, coming back to them only if you do not completely understand a portion of the completed macro.

Stopping a Looping Macro:

This sample macro will simply move the cursor down the column and stop when it reaches a blank cell.
 

1.    Sub ProcessIt()

4.       Do While ActiveCell.Value <> ""

12.           Selection.Offset(1, 0).Select

13.      Loop

16.   End Sub

Top
Enter data in a column of cells in an Excel worksheet. Make sure there is data in every cell. Place your cursor in the first cell containing data and run the macro.

Line 4 says: Do this macro while the ActiveCell is not blank, i.e.  Do Until  the  ActiveCell  finds a cell that contains nothing.  Value  in this context means any data, not to be confused with text vs. values as contents of a cell.

Line 12:  This line tells the cursor to move down one cell. Actually, the macro says move down one row and stay in the same column.

Line 13: Loop back to line 4 and repeat.

When the cursor reaches a blank cell, the macro will stop.

In order for this macro to work, there must be data in every cell. If not, the macro will stop prematurely.If your worksheet has blank cells in the column to be processed, then a different approach must be taken to stop the macro.
 

Now, let’s do some actual processing within the macro

This macro will analyze the contents of the cell and format it according.

1. Sub ProcessIt()

4.   Do While ActiveCell.Value <> ""

5.     If ActiveCell.Value >= 0 Then

6.          'Make the contents Blue
7.       Selection.Font.ColorIndex = 5

8.     Else

9.         'Make the contents Red
10.      Selection.Font.ColorIndex = 3

11.    End If

12.    Selection.Offset(1, 0).Select

13.  Loop

16. End Sub

Top
Lines 5 –11 contain the code for an  If  statement. This statement examines the contents of the cell, if the amount is positive, it makes the contents of the cell blue, if the amount is not zero or positive, then the color will be red.

How do you know which number represents which color? Simply record a macro formatting a cell as desired. Then, copy that line into this macro.

Make sure the cursor is on the first cell of the column of data before starting the macro.

Note that the  Selection.Offset(1, 0).Select  code is the last line immediately before the  Loop  statement.
 

Making sure the Macro Starts as Expected:

In this example, if the cursor is not in the top cell when the macro is executed, it will not give the desired results, give misleading results or may not run at all.

One solution to this is to ensure the macro starts in the desired cell by giving the first cell a defined (range) name.

Let’s assume the data always starts in cell  C3.  Give that cell a name, say  TopCell. Make this code the first line of the macro. It does not have to be the first line, however, it must be before the  Do While  line.

   3.  Range("TopCell").Select

The macro will now run properly and as expected every time.
 

To Put Some Finishing Touches on this Macro.

After this macro has been run, the cursor will be resting in the first blank cell under the column of data. The fact that the cursor is in this cell tells you that the macro has been run. But let’s make this a bit more eloquent.

When the macro is complete we will jump the cursor back to the top of the sheet/column and display a message box that says the macro is complete.

After the  Loop  statement enter this code.

14.  Selection.Offset(-1,0).End(xlUp).Select

or
14.  Range("TopCell").Select
or
14.  Range("A1").Select
Top
All three of these lines accomplish the same thing, they jump the cursor back to the top of the sheet. Choose the one that best fits your circumstances and style of writing macros.

The next line of code will be:

15. Msgbox "Process is Complete"

This will place a message box on the screen, telling you the macro has been run. When a message box is displayed, the computer will beep, thus alerting you the macro is complete.


The completed code for this exercise now looks like:

1. Sub ProcessIt()

2.      ‘To force the starting cell
3.    Range("TopCell").Select

4.    Do While ActiveCell.Value <> ""

5.       If ActiveCell.Value >= 0 Then

6.            'Make the contents Blue
7.         Selection.Font.ColorIndex = 5

8.       Else

9.            'Make the contents Red
10.        Selection.Font.ColorIndex = 3

11.      End If

12.   Selection.Offset(1, 0).Select

13.   Loop

14.   Range("A1").Select

15.   Msgbox "Processing is Complete"

16.  End Sub

Top
Other comments about this exercise:

Since you are copying in new data every day, this data will overwrite the exisiting cells. If today’s column of data is shorter than yesterday’s, you will have yesterday’s data still in the sheet beginning at the end of today’s data. Therefore, you must deal with erasing yesterday’s data before you copy in today’s data and before running this macro. One such method is to NOT save data in this file. After processing it, copy it to another file for saving. Or write a macro to erase the existing data prior to runnng this macro. Since this macro contains formatting, make sure the macro erases the data and the formatting.

To use or test this macro, simply highlight it, and copy into an Excel module. You will need to delete the line numbers and any other extraneous items that get pasted.

This example was written using Excel 97. It should run in all versions of Excel beginning with Excel 5 or higher, however it was not tested in those versions.

If there are commands in this sample that you do not understand, look elsewhere in this site for the topic. While in an Excel VBA module place your cursor on a command and press the F1 key. Excel will provide help on that command.
 

 

 
Top

 
 
 
 
 
 
 
 
 
 
 
 

 

solutions@info-stat.com