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