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
row do you wish to start with the calculations.
The condition that the looping process must satisfy
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
row 4 to row 5.
The loop must have an end point. For example you can
that the loop should stop when it finds no data in a cell.
The syntax has to be 100% correct. This may sound
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!
Here's the code for the example shown in the training video:
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
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
Sub automate_calculations_using_do_while_loop() 'Declare a variable r. If you don't define a datatype like integer or long MS Excel treats it as a Variant and does the needful Dim r '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
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 Loop 'End of macro