|
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:
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.
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 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
8. Else 9.
'Make the contents Red
11. End If 12. Selection.Offset(1, 0).Select 13. Loop 16. End Sub 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 14. Range("TopCell").Selector 14. Range("A1").Select 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.or 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.
1. Sub ProcessIt() 2.
‘To force the starting cell
4. Do While ActiveCell.Value <> "" 5. If ActiveCell.Value >= 0 Then 6.
'Make the contents Blue
8. Else 9.
'Make the contents Red
11. End If 12. Selection.Offset(1, 0).Select 13. Loop 14. Range("A1").Select 15. Msgbox "Processing is Complete" 16. End Sub 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 |