Do While Loop in Excel VBA

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:
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

View the training video:


One thought on “Do While Loop in Excel VBA

  1. Amalendu

    Sir we do I start with Dim function and when do start directly (not need dim function) make me confuse, please can you guide me for it with an example?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *