If you use index numbers to refer to cells you can use a do-while loop to automate calculations and analysis in MS Excel. View training video in Hindi.
A do while loop Excel macro is one of the powerful code you can create to automate calculations or perform analysis. The looping is performed as long as a condition is met and the computer stops the calculations as soon as the defined condition is met. We can, for example as shown in the online training video, define that the loop should run as long as data is available in the column number one under the header Item.
The code now looks like this for this sample. Text following an apostrophe is treated as a remark.:
sub calculations() ‘ name of the macro
Row = 3 ‘we define the starting row
Do while Cells(Row,1)<>”” ‘we want the computer to perform the calculations as long as there is data in column number one
Cells(Row,4)=Cells(Row,3)*Cells(Row,2) ‘ Here we multiply the quantity with the rate of the item and place the data under the head Amount
Row=Row+1 ‘ now the computer should look for data in the next row
Loop ‘ The program should keep on looping till it finds no data in column no. 1
End Sub ‘ End the program
Now you can try your hand at calculatiing the VAT and the Final amount by modifying the macro appropriately.
View the video for details.