Microsoft Excel VBA Training Videos

A 'do while' loop macro in Excel 2007

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:
Sub automation_loop()
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
Loop

End Sub
The Excel training video below demonstrates the idea.



A 'do while' loop macro in Excel 2007