VBA Do Until Loop

What is Do Until Loop in VBA Excel?

In VBA Do Until Loop, we need to define criteria after the until statement which means when we want the loop to stop and the end statement is the loop itself. So if the condition is FALSE it will keep executing the statement inside the loop but if the condition is TRUE straight away it will exit the Do Until statement.

As the words itself says that to do some task until a criterion is reached, Do until the loop is used in almost all of the programming languages, in VBA also we sometimes use Do until loop. Do Until Loop means to do something until the condition becomes TRUE. It is like a logical function that works based on TRUE or FALSE.

vba do until condition

This is the opposite of the Do While loop where Do while runs the loops as long as the condition is TRUE.

Syntax

Do Until loop has two kinds of syntax.

Syntax #1 

Do Until [condition]

[Perform Some Task]

Loop

Syntax #2

Do

[Perform Some Task]

Loop Until [condition]

Both look very similar, and there is one simple differentiation is there.

In the first syntax “Do Until” loop checks the condition first and gets the condition result is TRUE or FALSE. If the condition is FALSE, it will execute the code and perform a specified task, and if the condition is TRUE, then it will exit the loop.

In the second syntax, “Do” loop, firstly, it will execute the cod task, then it tests whether the condition is TRUE or FALSE. If the condition is FALSE, it will again go back and perform the same task. If the condition is TRUE, then it will straight away exit the loop.

Example

I know it is not that easy to understand anything in the theory part but nothing to worry about. We will give you easy examples to understand the loop. Read on. To start the learning, let’s perform the task of inserting the first 10 serial numbers from cell A1 to A10.

You can download this VBA Do Until Excel Template here – VBA Do Until Excel Template

Follow the below steps to apply the “Do Until” loop.

Step 1: Create a macro name first to start the subprocedure.

Code:

Sub Do_Until_Example1()

End Sub
Example 1.1

Step 2: Define a variable as “Long.”  I have defined “x” as a long data type.

Dim x As Long
Example 1.2

Step 3: Now, enter the word “Do Until.”

Do Until
Example 1.3

Step 4: After starting the loop name, enter the condition as “x =11”.

Do Until x = 11
Example 1.4

x = 11 is the logical testLogical TestA logical test in Excel results in an analytical output, either true or false. The equals to operator, “=,” is the most commonly used logical test.read more we have applied. So this line says to run the loop until x is equal to 11.

Step 5: Apply CELLS property, and let’s insert serial numbers from 1 to 10.

Cells(x, 1).Value = x
Example 1.5

Note: Here, we have mentioned “x” starts from 1, so at first x value is equal to 1. Wherever “x” is there is equal to 1.

Step 6: Now close the loop by entering the word “LOOP.”

Sub Do_Until_Example1()

Dim x As Long

Do Until x = 11
Cells(x, 1).Value = x
Loop

End Sub

vba do until example 1.6

Ok, we are done with the coding part, now we will test the codes line by line to understand the loop better.

To run the line by line code, first press the F8 key.

vba do until shortcut key

It will first highlight the macro name by yellow color.

vba do until example 1.7

When you can see the yellow line, it says that that is not executed by about to execute if you press the F8 key one more time.

Now press the F8 key one more time, the yellow line will go to Do Until Loop.

vba do until example 1.8

Now to understand the loop, place a cursor on the variable “x” and see the value of variable “x.”

vba do until example 1.9

So, x=0. Since the highlighted line is the first line in the loop, so the value of “x” is zero, so press the F8 key once again and see the value of “x.” Before that exit, the code is running and assign the value to “x” as 1.

vba do until example 1.10

Now again, start the running of a loop by pressing the F8 key. See the value of “x.”

vba do until example 1.11

Now the value of “x” is showing as 1. To have incremental value to the variable “x,” we need to reassign the value of variable “x” as x = x + 1 inside the loop.

vba do until example 1.12

Now press the F8 key one more time, and we should get the value of 1 in cell A1.

vba do until example 1.13

Now press the F8 key once again and see what the value of “x” is.

vba do until example 1.14

The value of variable “x” is 2 now. So our condition says to run the loop until the condition becomes TRUE, so our loop keeps running until the value of “x” becomes 11.

Press F8 one more time. It will jump back to the “Do Until” loop line.

example 1.15

Press the F8 key two more times, and we will get the value of 2 in cell A2.

vba do until example 1.16

Press the F8 key again and the value of “x” becomes 3 now.

excel vba do until loop - example 1.17

Press the F8 key again it will jump back to the loop once again.

excel vba do until loop - example 1.15

Like this, this loop will again keep executing the task until the value of “x” becomes 11. Now I have executed the loop until the “x” value becomes 11.

excel vba do until loop - example 1.18

Now, if I press F8, it still will go back to the loop.

excel vba do until loop - example 1.15

But if I press the F8 key now, it will exit the loop because the applied condition becomes “TRUE,” i.e., x = 11.

end loop - example 1.19

So we have serial numbers from 1 to 10 in the excel sheet now.

Example 1.20

So, this is the basic idea of the “Do Until” loop. To understand any loops, you need to run the code line by line until you get the full knowledge about loops.

Recommended Articles

This has been a step by step guide to VBA Do Until Loop. Here we discuss how to use Do Until Loop in Excel VBA along with a practical example. Below are some useful excel articles related to VBA –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>