March 3, 2014

Calculations Using Loops

How to use the ‘for next’ and ‘do while’ loops in Excel to automate calculations like overtime and total wages.

Let’s learn about some practical examples of usage of the looping process in Excel VBA.
From the data in our Excel worksheet we wish to calculate the overtime and the wages automatically.
We click on the developer tab.
Next we select insert and place a command button from the Activex controls on to our Excel worksheet by clicking and dragging.
We write our macro in the Visual Basic Editor for applications and attach the macros to our command button one by one which execute when the inserted command button ‘calculate’ is clicked.
In the case of the ‘do while’ loop we run the calculations till a condition is met. We know the starting row where calculations have to be performed but we not know the total number of rows of data in our Excel worksheet. As soon as the looping process finds a blank cell under the header ‘Name’, it stops the calculation process and our work is done.
In the case of the ‘for next’ loop we have counted the number of rows of data that have been used in our Excel worksheet. Now using the ‘for next’ looping process we perform tjhe calculations on the known number of rows of data.

The complete VBA code is shown in the image below:

VBA code for the command buttons
VBA code for the command buttons

View the training video:

Further reading:
VBA loops

One thought on “Calculations Using Loops

Comments are closed.