A 'do while' loop macro in Excel 2007: The aim of working with large
amounts of data received from sources like text files, databases or
even Excel itself is to automate the required calculations. In the
previous training videos we learnt how to access worksheet cells and
also perform calculations in individual spreadsheet cells. Now we
automate the whole process of calculations or even data input by using 'do
while' loop macros.
These macros perform the calculations in Excel as long as the defined
conditions are met. In our example shown below we wish to calculate
'other benefits' and the 'total' amount of money that a employee
receives as long as the 'name' of the employee under the 'name' heading
has been input. As soon as the program encounters a 'no data' cell it
stops the calculations as desired. Of course you can define different
conditions based on the calculation requirements of your problem and
perform more difficult calculations. As you can see apart from the
understanding of the logic and the syntax used to create a macro in
Visual Basic for Applications (VBA) you need to have a sound knowledge
of the domain in which you are working!
The complete macro code:
Row = 2
Do While Cells(Row, 1) <> ""
Cells(Row, 3).Value = Cells(Row, 2) * 0.5
Cells(Row, 4).Value = Cells(Row, 2).Value + Cells(Row, 3).Value
Row = Row + 1
The Excel training video below demonstrates the idea.