learn microsoft excel

Automation Using A 'Do While' Loop
Excel 2007
 Excel 2003
 Home
 Macros Excel 2003
 Excel 2010
About Us
 Free Ebook
 Offline Training
 Blog
 Interactive Training

You can achieve a great deal of automation using a 'do while' loop in your calculations and analysis of worksheet data in Microsoft Excel. The 'do while' loop in combination with the cells property helps loop through all the data in the worksheet and perform calculations and analysis like conditional formatting quickly and easily. When you use the  looping code you need to care of the following points:
  • The loop must have a starting point. For example from which row do you wish to start with the calculations.
  • The condition that the looping process must satisfy to be able to run through the data. For example, a cell must have data and not be empty
  • Increment of the loop parameter. For example now go from row 4 to row 5.
  • The loop must have an end point. For example you can define that the loop should stop when it finds no data in a cell.
  • The syntax has to be 100% correct. This may sound daunting but it becomes quite easy to write such code for a macro with some practice and once you get the hang of it you love it!
Here's the code for the example shown in the training video:

Sub automate_calculations_using_do_while_loop()
r = 4
Do While Cells(r, 1) <> ""
Cells(r, 4) = Cells(r, 2) * Cells(r, 3)
r = r + 1
Loop
End Sub
Excel 2007 Excel 2003 Macros Excel 2007 Macros Excel 2003 Excel 2010
About Us Free Ebook Offline Training Blog Interactive Training