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

Get FREE Advanced Excel Exercises with Solutions!

People like to be adventurous. They do not like to do the same thing again, again and again. In terms of working with VBA, we can skip the same code writing multiple times. Instead, we can use different kind of loops. In this article, I am going to explain how to use Do Until Loop in Excel VBA with two simple examples. I hope it will make your life easier.


What Is Loop?

A Loop is a programming construct that allows you to repeat a certain set of code multiple times. The code keep running until it reaches a certain condition. It is a highly used powerful function that allows to work a similar huge amount of task in shortest possible time.


Types of Loops in VBA

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

  1. For loops
  2. Do loops and
  3. While loops

Among the Loops in VBA, For Loops is the most common loop and is vastly used in writing VBA code. Do Until loop is less used but this loop may come in handy in certain cases instead of others.


Introduction of VBA Do Until Loop

A Do Until Loop is a type of loop that runs until a condition is met. It is different 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.

Do Until Loops are useful when you want to repeat a certain set of actions until a specific condition is met.


VBA Do Until Loop: Syntax

Do Until Loop can be expressed with syntax 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. It will immediately exit the Loop if the condition is TRUE.


Example-1: Using Simple Do Until Loop in VBA

Instead of repeating the same writing multiple times, we can use the Do Until loop to lessen our workload. Here is an example to do so.

In this example, I have arranged some players’ salaries with clubs. They are all from Argentina. So, I will try to execute the Do Until loop to fill the Country column instead of writing it manually in all those cells.

Do Until Loop VBA

Steps:

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

Using Simple Do Until Loop

  • Next, click on the Insert tab.
  • From the available options, choose Module.

  • Now, input the following VBA code on that module:
Sub DoUntilLoop()
Dim i As Integer
i = 5
Do Until i > 9
    Cells(i, 3).Value = "Argentina"
    i = i + 1
Loop
End Sub

Using Simple Do Until Loop

Here, I have defined DoUntilLoop() as Sub_procedure and a variable i with value 5. I have applied Do Until loop from cell C5 to return Argentina as output until cell C9.

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

We will have our desired output on the defined cells using Do Until Loop.

You can write the code like the following one also.

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 VBA Do Until Loop with Condition

We can also get output by applying conditional analysis. It will reduce our calculations as well as workloads.

In our source example, 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, I want to keep experimenting until the level of HCN per kg comes under 10mg.

Here, I have organised my data in the Step, Amount of Cyanide Remaining in Cassava Cultivar after Step (mg) and Continue with Experiment Yes/No? columns.

Using Do Until Loop With a Condition

Steps:

  • Open a module following the above procedures mentioned in Example-1.
  • Next, write 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, I have defined DoUntilLoopExample() as Sub_procedure and a variable valueofInteger. I have defined the C8 cell to consider its value and compare it if the value is greater than 10 or not. If the result is greater than 10, it will return Yes, Continue and continue the Loop. It will continue until the value gets less than 10.

  • Finally, execute the code by pressing the F5 button or the Run button from the ribbon.

Do Until Loop VBA

Thus, we will have the output on the defined cell using Do Until Loop.


Download Practice Workbook


Conclusion

That’s all for today. I have tried to explain how to use Do Until Loop in Excel VBA with two simple examples. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Taryn Nefdt
Taryn Nefdt

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS), and biofuels. She enjoys showcasing the functionality of Excel in various disciplines. She has over ten years of experience using Excel and Access to create advanced integrated solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo