Excel VBA: DO WHILE….Loop

DO WHILE….Loop

The “Do While” Loop is the same, unlike the FOR statement, just that it will keep on looping till the specified condition is true. It is used when we want to repeat a set of statements where the condition may be checked at the beginning or at the end of the loop.

Syntax

Do While condition
    [statement 1]
    [statement 2]
    ...
    [statement n]
 Loop    

Alternate Syntax

Do 
    [statement 1]
    [statement 2]
    ...
    [statement n]
 Loop While (condition) 

The difference between both the syntax is that in the first one, the While condition is checked first before any code block or statement is executed, and in the second syntax, the given statement or code block is executed first, and then the While condition is checked. Even if the while condition is false, but it will still run at least once in the second syntax.

Example 1: Write a macro using Do While loop to print the odd number between 1 -10.

Sub DoWhile_Example1()
 Dim val As Integer
 val = 1
     'Do While will loop until the cells' value is not equal to empty
     Do While val <= 10
         'with the help of worksheet property inserting the values
         Worksheets("Sheet2").Cells(val, 1).Value = val 
         val = val + 2
     'Closing the Do While loop
     Loop
 End Sub 

Output

1

3

5

7

9

Example 2: Write a procedure or macro using Do Loop that does the following:

Get "Qualified" in column E if the value of column D is above 200

Sub DoWhile_Loop_Example1()
 'initializing the variable r
 Dim r As Byte
 'Setting up a value of variable 'r'
 r = 2
     'Do While will loop until the cells' value is not equal to empty
     Do While Cells(r, 1) <> ""
         'IF Condition Block
         If Cells(r, 4) > 200 Then 
             Cells(r, 5) = "Qualified"
         Else
             Cells(r, 5) = "Disqualified"
         End If
         'Adding one to r counter to move to the next cell
         r = r + 1
     'Closing the while loop 
     Loop
 End Sub 

Output

Name	Title	Date	Sale Amount	Qualified/Disqualified
Leo Hendry	Customer Service	6/8/2016	$220.00	Qualified
Rani Bhardwaj	Sales	1/1/2014	$251.00	Qualified
Elmer C. Laslo	Customer Service	6/16/2016	$217.00	Qualified
Ayush Khurana	Data Entry Specialist	5/31/2015	$344.00	Qualified
Gerard M. Banmiller	Customer Service	1/11/2014	$980.00	Qualified
Gregory L. Gibson	IT Manager	11/22/2015	$178.00	Disqualified
Megha Malik	Customer Service	3/31/2014	$258.00	Qualified
N. William White	Sales Manager	12/21/2015	$38.00	Disqualified
Robert F. Mangano	Data Entry Specialist	7/16/2015	$371.00	Qualified
Itrat Zaidi	IT Tech	6/8/2016	$67.00	Disqualified
Thomas E. Vessey	Business Development Executive	3/21/2016	$437.00	Qualified

Example 3: In the following procedure, the “n>6” condition is tested at the beginning of the loop, Loop ends because the condition is not met. Hence, the msgbox returns the value of 0.

Sub DoWhile_Example3()
 Dim n As Integer
 Dim nTotal As Integer
 n = 6
 nTotal = 0
     'While condition is checked at beginning of the code
     Do While n > 6
         'the statement won't get executed 
         nTotal = n + nTotal
         n = n - 1
     Loop
 'it will return 0
 MsgBox nTotal 
 End Sub 

Output

While Using the Second Syntax

Example 4: In the following procedure, the “n>6” condition is tested at the end of the loop, and because it is met, the loop executes, but only once after which the value of n decreases to 5, and the Loop ends. The msgbox returns the value of 6.

Sub DoWhile_Example4()
 Dim n As Integer
 Dim nTotal As Integer
 n = 6
 nTotal = 0    
     Do
         nTotal = n + nTotal
         n = n - 1 
     'While condition is checked after the code has been executed once
     Loop While n > 6
 MsgBox nTotal
 End Sub 

Output