Automation Using A ‘Do While’ Loop Macro
You can achieve a great deal of automation using a ‘do while’ loop in your calculations and analysis of worksheet data in Microsoft Excel. The ‘do while’ loop in combination with the cells property helps loop through all the data in the worksheet and perform calculations and analysis like conditional formatting quickly and easily. When you use the looping code you need to care of the following points:
- The loop must have a starting point. For example from which row do you wish to start with the calculations.
- The condition that the looping process must satisfy to be able to run through the data. For example, a cell must have data and not be empty
- Increment of the loop parameter. For example now go from row 4 to row 5.
- The loop must have an end point. For example you can define that the loop should stop when it finds no data in a cell.
- The syntax has to be 100% correct. This may sound daunting but it becomes quite easy to write such code for a macro with some practice and once you get the hang of it you love it!
Starting the VBA editor and writing the code:
- Click on the Developer tab
- From the Code group click on Visual Basic
- In the Visual Basic for Applications editor window click on the Insert menu
- Select Module
- Under Modules on the left-hand side you’ll see that Module 1 has been inserted
- In the workspace in the Visual Basic for Applications editor start writing your beginning with the subroutine or macro name
Macro names cannot have spaces. You can use underscores to connect words
Generally people use the camel annotation like automation_Macro but you can also use AutomationMacro or automation_macro
Here’s the VBA code for the example shown in the training video:
‘Declare a variable r. If you don’t define a data type like integer or long MS Excel treats it as a Variant and does the needful
‘We initialize the variable r to 4 because that is the row where our data input in the Excel worksheet starts
r = 4
‘ Using the keyword ‘do while’ we start the loop and define a condition that as long as the cell Ar is not empty or blank do the following actions
Do While Cells(r, 1) <> “”
‘ Assign the value of the product of cells Br and Cr to Ar. Example vlue of cell A4 becomes equal to the value of product of the cells B4 and C4
Cells(r, 4) = Cells(r, 2) * Cells(r, 3)
‘Go to the next row
r = r + 1
‘Keep on looping till you find a blank cell in Ar. Example A9. In our example you will note that the last data entry is in row 8. Row 9 is blank and once the loop reachers r=9, it will end the subroutine or macro because our condition for looping is that there must be some data in Ar or for example A9
‘End of macro
Watch the Excel training video below: