March 1, 2014

Automation Using Do While Loop in Excel VBA

Automation Using A ‘Do While’ Loop Macro
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!

Starting the VBA editor and writing the code:

  • Click on the Developer tab
  • From the Code group click on Visual Basic
  • In the Visual Basic for Applications editor window click on the Insert menu
  • Select Module
  • Under Modules on the left-hand side you’ll see that Module 1 has been inserted
  • In the workspace in the Visual Basic for Applications editor start writing your beginning with the subroutine or macro name

Macro names cannot have spaces. You can use underscores to connect words
Generally people use the camel annotation like automation_Macro but you can also use AutomationMacro or automation_macro

Here’s the VBA code for the example shown in the training video:

Sub automate_calculations_using_do_while_loop()
‘Declare a variable r. If you don’t define a data type like integer or long MS Excel treats it as a Variant and does the needful
Dim r
‘We initialize the variable r to 4 because that is the row where our data input in the Excel worksheet starts
r = 4
‘ Using the keyword ‘do while’ we start the loop and define a condition that as long as the cell Ar is not empty or blank do the following actions
Do While Cells(r, 1) <> “”
‘ Assign the value of the product of cells Br and Cr to Ar. Example vlue of cell A4 becomes equal to the value of product of the cells B4 and C4
Cells(r, 4) = Cells(r, 2) * Cells(r, 3)
‘Go to the next row
r = r + 1
‘Keep on looping till you find a blank cell in Ar. Example A9. In our example you will note that the last data entry is in row 8. Row 9 is blank and once the loop reachers r=9, it will end the subroutine or macro because our condition for looping is that there must be some data in Ar or for example A9
‘End of macro
End Sub

Watch the Excel training video below:

5 thoughts on “Automation Using Do While Loop in Excel VBA

  1. Dear Sir,

    I have data there im applying Data validation when comes to user they paste value in validations cells & data validation cells accepting value
    my problem is i want to set data validation like the user only select data validation list value.. they Don’t allow to paste the value into the validation cells.

    i request to you please do the needful


  2. Hello sir,

    I have 7 data ranges, I want to insert serial number to from 1 to onwards by loop, as many as rows in each data range insert serial number accordingly. Kindly help me aout

  3. Supper sir your simple way explanation. Please guide me how right VBA code for sperating the data from column. Example some SAP PM datas like PMPM, PMPMA, PMCA etc in a column and other column has expenditure amount details.

    If I want to know only PMPA related expenditure with related datas. Please help me

  4. Hello sir ,
    How to insert auto serial number just like in cell code =if(b2=””,””,1) by vba code

Comments are closed.