Excel VBA: Do Until….Loop
DO UNTIL….Loop
The “Do Until” Loop is same unlike DO WHILE statement just that it will keep on
looping till the condition is not met. This
loop is used to repeat a set of statements if the condition is false. The
condition may be checked at the beginning of the loop or at the end of Do Until
loop.
Syntax
Do Until condition [statement 1] [statement 2] ... [statement n] [Exit Do] [statement 1] [statement 2] ... [statement n] Loop
Alternate Syntax
Do [statement 1] [statement 2] ... [statement n] [Exit Do] [statement 1] [statement 2] ... [statement n] Loop Until condition
In the first syntax, the “Do Until” loop checks the condition first and returns the condition result as TRUE or FALSE. If the condition returns true, the loop gets terminated else for False t will execute the code and perform a specified task.
In the second syntax, it will execute the cod task then it test for the condition to check whether the condition is TRUE or FALSE. If the condition is FALSE it will perform the same task again. If the condition is TRUE then it will exit the loop. Even if the condition is TRUE, but it will still run at least once in the second syntax.
Example 1: Write a Macro, using Do Until Loop to print the odd numbers between 1 to 10.
In the above example, if you change the condition of Do Until I = 10 instead of 11, since variable ‘I’ will never reach this value 10, as 1+2 = 3, then 3+2 = 5 and so on.. i.e. we will have only odd numbers, excel will keep on running till value of I is 32,767 which is the maximum value a variable declared as integer can take. This is how Do Until is different from Do While.
Sub DoUntil_Example1()
Dim I As Integer
I = 1
'repeat a set of statements if the condition is false.
Do Until I = 11
Worksheets("Sheet2").Cells(I, 2).Value = I
I = I + 2
Loop
End Sub
Output
1
3
5
7
9
Example 2: Write a procedure or macro using Do Until that does the following task:
Get "Qualified" in column E if the value of column D is above 200 else it should return “Not Qualified”
Sub DoUntilLoop_Example2() Dim CurRow As Byte CurRow = 2 'checks the condition first and get the condition result as TRUE or FALSE. Do Until Cells(CurRow, 1) = "" If Cells(CurRow, 4) > 200 Then Cells(CurRow, 5) = "Qualified" Else Cells(CurRow, 5) = "Not Qualified" End If CurRow = CurRow + 1 Loop End Sub
Output
| Name | Title | Date | Sale Amount | Qualified/ Not Qualified |
| Itrat Zaidi | IT Manager | 6/8/2016 | $220.00 | Qualified |
| Varun Mahanty | Sales | 1/1/2014 | $251.00 | Qualified |
| Elmer C. Laslo | Customer Service | 6/16/2016 | $217.00 | Qualified |
| Sohrab Grait | Data Entry Specialist | 5/31/2015 | $344.00 | Qualified |
| Gerard M. Banmiller | Customer Service | 9/8/2014 | $980.00 | Qualified |
| Arvind Patra | IT Manager | 11/22/2015 | $178.00 | Not Qualified |
| John C. Black | Customer Service | 3/31/2014 | $258.00 | Qualified |
| N. William White | Data Entry Specialist | 12/21/2015 | $38.00 | Not Qualified |
| Robert F. Mangano | Data Entry Specialist | 7/16/2015 | $371.00 | Qualified |
| Rohit Thakkar | IT Tech | 12/21/2017 | $67.00 | Not Qualified |
| Thomas E. Vessey | Customer Service | 1/1/2017 | $437.00 | Qualified |
Example 3: In the following procedure, the “i < 11” condition is tested at the beginning of the loop, Loop ends because the condition is met. Hence, the it will exit the loop.
Sub DoUntil_Example3() 'Declaring the variable i = 6 'It will check the condition and if it is true then will run the block of code Do Until i < 11 i = i + 1 MsgBox "The value of the variable is: " & i Loop End Sub
Output
Alternate Syntax
Example 4: In the following procedure, the “i < 11” condition is tested at the end of the loop. So firstly the code will be executed. Hence, the it will return 7. Then will check the condition and terminate the loop.
Sub DoUntil_Example4() 'Declaring the variable i = 6 'It will firstly run the program and then check the condition Do i = i + 1 MsgBox "The value of the variable is: " & i Loop Until i < 11 End Sub
Output
