Latest

VBA Do Until Loop | How to Use Excel VBA Do Until Loop with Examples?

Excel VBA Do Until Loop

Do till loop will continue to repeat the statements until the condition/criteria develop into TRUE. It’s going to execute the statements as long as the circumstances are FALSE. As soon as the condition/standards grow to be TRUE, it terminates the loop. It may be appeared as reverse to Do While loop, the place loop runs so long as the standards are TRUE and get terminated as quickly as the standards are FALSE.

See the movement diagram under which explains the working of Do Until loop:

Working of Do Until loop

Syntax of Do Until Loop in Excel VBA

Do Until Loop has two kinds of syntax in Excel VBA.

Syntax 1:

Do Until [Condition] [Statements to be executed]

Loop

Syntax 2:

Do
[Statements to be executed] Loop Until [Condition]

The essential difference between these two syntaxes is of execution. Within the first syntax, the loop will all the time examine if the condition is True or False. Whether it is False, it can repeat the loop once more. As quickly because the condition/standards are true, it is going to terminate the loop. In this case, there is a risk that the loop will get terminated at first iteration itself (if the condition is True). Hence, you’ll not get any output in that case. Nevertheless, in second syntax the loop will initially execute the statements after which verify for the situation whether or not it’s True or False. If the situation is False, it’s going to again execute the identical set of statements and verify for the situation. If the primary iteration itself provides condition as True, this loop terminates, however earlier than that, it can have a press release executed. In that case, we’ll get at the least one iteration (As null output).

Lastly, the output of the two codes will be the similar. Nevertheless, the execution is something that differs these two.

Example of Excel VBA Do Until Loop

Let’s see the examples of Do Until Loop in Excel VBA.

]]]]]]>]]]]>]]>

Instance #1 – VBA Do Until Loop when circumstances are checked at the beginning

Comply with the under steps to apply the Do Until loop in Excel VBA.

Step 1: Insert a new module beneath Visible Primary Editor (VBE) to have the ability to write code.

VBA Do Until Loop Example 1-1

Step 2: Define a sub-procedure which may retailer the macro code you may be writing.

Code:

Sub Do_Until_Ex1()

Finish Sub

VBA Do Until Loop Example 1-2

Step three: Outline a new variable “X” with knowledge sort as “Long”. This variable can be utilized as a condition/standards beneath Do Until loop.

Code:

Sub Do_Until_Ex1()

Dim X As Lengthy

Finish Sub

VBA Do Until Loop Example 1-3

Step 4: Set the preliminary worth to 1 for the variable X.

Code:

Sub Do_Until_Ex1()

Dim X As Lengthy
X = 1

End Sub

VBA Do Until Loop Example 1-4

Step 5: Begin the loop with key phrases Do Until.

Code:

Sub Do_Until_Ex1()

Dim X As Lengthy
X = 1
Do Until

Finish Sub

VBA Do Until Loop Example 1-5

Step 6: Specify the standards as X = 11 beneath Do Until. This can be a situation which allows the loop to break when is True. It signifies that the loop can be operating till the value of X = 11. As quickly as the value for X equals to 11, the loop will terminate.

Code:

Sub Do_Until_Ex1()

Dim X As Long
X = 1
Do Until X = 11

Finish Sub

VBA Do Until Loop Example 1-6

Step 7: Now, use VBA Cells.Value perform to find a way to add the square of every quantity till X underneath the first column.

Code:

Sub Do_Until_Ex1()

Dim X As Long
X = 1
Do Until X = 11
Cells(X, 1).Value = X * X

Finish Sub

VBA Do Until Loop Example 1-7

Step 8: Again, we’d like to set the increment in X by 1, so that each time the loop runs and checks the situation it can go to the subsequent number and retailer a squared worth of it within the respective cell until quantity 11.

Code:

Sub Do_Until_Ex1()

Dim X As Long
X = 1
Do Until X = 11
Cells(X, 1).Worth = X * X
X = X + 1

End Sub

VBA Do Until Loop Example 1-8

Step 9: Full this Do Until loop by getting into the key phrase “Loop” at the end of the code.

Code:

Sub Do_Until_Ex1()

Dim X As Lengthy
X = 1
Do Until X = 11
Cells(X, 1).Worth = X * X
X = X + 1
Loop

Finish Sub

VBA Do Until Loop Example 1-9

On this code, We needed to have squared values for numbers starting from 1 until 11 (As quickly as a worth is 11, the loop terminates). Beneath every cell of excel sheet (till cell number 11), the values will get saved. Increment of 1 unit permits a number every time to be increased by 1 in earlier worth of X and the squared value of that quantity is printed within the respective cell. For instance, Squared value for three can be printed underneath the third cell of the lively excel sheet. This loop runs as long as X just isn’t equal to 11. Subsequently, underneath cell A1 to cell A10, we get the squared values of number ranging from 1 to 10 (at number 11 loop terminated).

Step 10: Hit the Run button or press the F5 key to run this code and see the output.

Result of Example 1-10

Instance #2 – Do Until loop when circumstances are checked at the finish of the loop

Comply with the under steps to apply the Do Until loop in Excel VBA.

Step 1: Outline a new sub-procedure underneath VBE module.

Code:

Sub Do_Until_Ex2()
End Sub

VBA Do Until Loop Example 2-1

Step 2: Outline a variable “Y” with knowledge sort as “Long”. This variable can be helpful in looping up the circumstances.

Code:

Sub Do_Until_Ex2()
Dim Y As Long

Finish Sub

VBA Do Until Loop Example 2-2

Step 3: Set the preliminary value of variable “Y” as 1.

Code:

Sub Do_Until_Ex2()
Dim Y As Lengthy
Y = 1

End Sub

VBA Do Until Loop Example 2-3

Step four: Add Do situation by typing Do on subsequent line after you set the preliminary worth of Y to 1.

Code:

Sub Do_Until_Ex2()
Dim Y As Lengthy
Y = 1
Do

End Sub

VBA Do Until Loop Example 2-4

Step 5: Add the piece of code which you need to be executed beneath Do loop. Here we will probably be taking the identical example as of above (it should help us getting know, how the outputs are similar however compilations are totally different for two of those codes).

Code:

Sub Do_Until_Ex2()
Dim Y As Lengthy
Y = 1
Do
Sheets(“Example 2”).Cells(Y, 1).Value = Y * Y

End Sub

VBA Do Until Loop Example 2-5

Step 6: After each iteration of the loop, you needed the value to be elevated by 1 unit, set a command for that underneath Do part.

Code:

Sub Do_Until_Ex2()
Dim Y As Lengthy
Y = 1
Do
Sheets(“Example 2”).Cells(Y, 1).Worth = Y * Y
Y = Y + 1

Finish Sub

VBA Do Until Loop Example 2-6

Step 7: Add a closing statement for this loop with a keyword as “Loop”.

Code:

Sub Do_Until_Ex2()
Dim Y As Long
Y = 1
Do
Sheets(“Example 2”).Cells(Y, 1).Worth = Y * Y
Y = Y + 1
Loop

Finish Sub

VBA Do Until Loop Example 2-7

Maintain on! There is something more. You should have to add the until criteria/situation beneath this loop, this time after the Loop key phrase. It’ll permit the compiler to verify the condition at the end of the loop.

Step 8: Add till Y = 11 as a condition after Loop Key phrase.

Code:

Sub Do_Until_Ex2()
Dim Y As Lengthy
Y = 1
Do
Sheets(“Example 2”).Cells(Y, 1).Worth = Y * Y
Y = Y + 1
Loop Until Y = 11

End Sub

VBA Do Until Loop Example 2-8

Step 9: Run this code by hitting F5 or Run button and see the magic underneath sheet named “Example 2”.

Result of Example 2-9

How does this code work?

System squares up to the value of Y each time underneath the loop and shops it beneath the second column of sheet named “Example 2” row by row. After each iteration, the present value of Y is being elevated by 1 unit and the up to date worth is being squared and saved up. As long as the value is lower than the criterion worth (Y = 11), the code will do the square and retailer it up. As soon As the value compiler reaches to value Y = 11, it stops executing the code and terminates the same.

Each of the codes give an identical output, however logically there’s a difference in compilations for both. In the first sort of code, the situation is being checked initially of the loop and if it is False then solely the loop begins to evaluate the subsequent expression. Nevertheless, in the second instance, the code begins operating loop and executing Do statements initially (storing the values in a buffer memory for the execution statements). At the finish of the loop, compiler comes up with a criterion and checks the same with the buffer worth.

Issues to Keep in mind

  • VBA Do Until is strictly the reverse case of Excel VBA Do Whereas. VBA Do While loop runs as long as the condition is being TRUE. As soon as the situation is FALSE, Do Whereas loop gets terminated. However, VBA Do Until runs as long as the situation is FALSE. As soon because the condition is TRUE, loop will get terminated.
  • It has two ways of proceedings, one the place the condition is checked firstly of the loop and different where the condition is checked at the finish of the loop.

Really helpful Articles

This can be a guide to VBA Do Until Loop. Here we talk about how to use Excel VBA Do Until Loop along with some sensible examples and downloadable excel template. It’s also possible to undergo our different advised articles –

  1. VBA While Loop
  2. LOOKUP Components in Excel
  3. Complete Tutorials on VBA Loops
  4. Lookup Table in Excel

Excel VBA Course – All in One Bundle

120+ Online Programs

500+ Hours

Verifiable Certificates

Lifetime Entry

Study More

  • Excel Knowledge Evaluation Course

  • Excel for Advertising Course