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:
- For loops
- Do loops and
- 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.
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.
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.
- Go to the Developer tab first.
- Pick Visual Basic from the ribbon.
- 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
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.
- 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.
Thus, we will have the output on the defined cell using Do Until Loop.
Download Practice Workbook
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.