How to Use Do Until Loop in Excel VBA (with 2 Examples)

In this article, we will explain how to use the Do Until Loop in Excel VBA with two simple examples.

What Is a Loop?

A Loop is a programming construct that repeats a certain set of code multiple times. The code keeps running until it reaches a certain condition.

Types of Loops in VBA

In Visual Basic for Applications (VBA), there are three main types of loops:

For loops
Do loops and
While loops

The For Loop is the most common loop used in writing VBA code. The Do Until loop is less used but more applicable in certain cases than the others.

Introduction to the VBA Do Until Loop

A Do Until Loop is a type of loop that runs until a condition is met. It differs from a standard Do Loop in that a Do Until Loop will run until the condition is True, whereas a Do Loop will run until the condition is False.

VBA Do Until Loop: Syntax

The Do Until Loop can be expressed in two different ways.

Syntax 1

Do Until [Input condition]

 [Statements]
 
Loop

Here, the Do Until Loop evaluates the condition and returns TRUE or FALSE. If the condition is TRUE, it will immediately exit the loop; if it is FALSE, it will run the code and carry out the stated task.

Syntax 2

Do

[Statements]

Loop Until [Input condition]

In this case, the Do Loop will first run the code task before checking to see if the condition is true or false. It will continue to carry out the corresponding action if the condition is FALSE, and will immediately exit the Loop if the condition is TRUE.

Example 1 – Using a Simple Do Until Loop in VBA

Instead of repeating the same code multiple times, we can use the Do Until loop to automate the process.

In the dataset below, we have some football players’ salaries at their clubs. All the players are from Argentina. We’ll execute a Do Until loop to fill the Country column instead of entering the values manually.

Steps:

Go to the Developer tab.
Pick Visual Basic from the ribbon.

Click on the Insert tab.
Choose Module.

Input the following VBA code in the module window that opens:

Sub DoUntilLoop()
Dim i As Integer
i = 5
Do Until i > 9
    Cells(i, 3).Value = "Argentina"
    i = i + 1
Loop
End Sub

Here, we define a DoUntilLoop() as a Sub_procedure and a variable i with value 5. We then apply a Do Until loop to return Argentina as output from cell C5 until cell C9.

Run the code by pressing the F5 button or the Run button from the ribbon.

Column C is filled with the desired text.

We can also write the code to accomplish this as follows:

Sub DoUntilLoop()
Dim i As Integer
i = 5
Do
    Cells(i, 3).Value = "Argentina"
    i = i + 1
Loop Until i > 9
End Sub

Example 2 – Applying the Do Until Loop with a Condition

We can also get output by applying conditional analysis, which will reduce our calculations as well as workload.

In the example below, we want to reduce the levels of cyanide in the cassava root samples/cultivars. The process contains ten steps, and after each purification/extraction/filtration step, the amount of cyanide remaining is measured. The maximum safe intake of HCN per kg of human body weight is 10 mg. So, we want to keep experimenting until the level of HCN per kg falls below 10 mg.

Steps:

Open a module following the process described in Example 1.
Input the following code in the module:

Sub DoUntilLoopExample()
Dim valueofInterest As Integer
Range("C8").Select
Do Until ActiveCell.Value <= 10 Or ActiveCell.Value = ""
valueofInterest = ActiveCell.Value
If valueofInterest > 10 Then
ActiveCell.Offset(0, 1).Value = "Yes, Continue"
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Here, we define DoUntilLoopExample() as a Sub_procedure and a variable valueofInteger. We check if the value in cell C8 is greater than 10 or not. If it is, the code will return Yes, Continue and continue the loop, which will keep iterating until the value is below 10.