A ‘do while’ loop macro in Excel: 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
View the training video: