How to use Do Until Loop in Excel VBA Easily (3 Example)

Welcome to Excel Avon

What is Do Until Loop?

DOWNLOAD USED EXCEL FILE FROM HERE>>

This article is for Do Until Loop in Excel VBA, in this article we will understand what the work of DO Until Loop in Excel VBA also, how to use do until loop, and learn how to use Do Until Loop with Exit Do. It can be looked as opposite to Do While loop, In VBA Do until if condition is False then it will continue executing the statement inside the Loop. but if the condition is true then it will exit the do until statement.

Or we can say something like this, do until loop will continue to repeat the statements until the condition become TRUE. It will execute the statements as long as the conditions are FALSE. As soon as the condition become TRUE, it terminates the loop. 

To understand for loop in Excel VBA, we have to go to VBE Like last time, first go to the Developer Tab, then we will click on the Visual Basic option as shown in the image below.

On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.

DO UNTIL LOOP IN EXCEL VBA

Example 1

After inserting the model in VBE we will write the subroutine for simple do Until LOOP.

Sub UseDountil ()

End Sub

After writing the subroutine we will define the variable

Sub UseDountil ()
Dim i as Long

End Sub

Let’s take the value of i as the value

Sub UseDountil ()
Dim i as Long

i = 1
End Sub

Now enter the word “Do Until”. And after starting the loop name, enter the condition as “i > 20”. close the loop by entering the word “LOOP”.

Sub UseDountil ()
Dim i as Long

i = 1
Do Until i > 20
Loop
End Sub

In the activesheet, using the CELLS property, enter the serial number.

Note: Here, the variable (i) starts with 1, so the value of the first i is equal to 1. Wherever “i” occurs, it is equal to 1. Fill cube of i in the location of active sheet.

Sub UseDountil ()
Dim i as Long

i = 1
Do Until i > 20
    Activesheet.cells(i, 1).value = i ^ 3
Loop
End Sub

Now inside the loop, reassign the i value as i, (code for every next step).

Sub UseDountil ()
Dim i as Long

i = 1
Do Until i > 20
    Activesheet.cells(i, 1).value = i ^ 3
    i = i + 1
Loop
End Sub

After writing the code we will click in Run button.

After clicking on the run button, we will go to the active sheet where the result will be printed, we have shown you the result, it has cube fill of i till 20 cells.

Example 2

We’ll go back to the VBE and write the value for a cell blank after each step.

Sub UseDountil () 
Dim i as Long 
i = 1 

Do Until i > 20 
   Activesheet.cells(i, 1).value = i ^ 3
   i = i + 2
Loop 
End Sub

After click on run button, we will go to active sheet where result will be printed, we have shown you the result, it has cube fill of i up to 20 cells but after every single cell a cell is blank.

DO UNTIL LOOP WITH EXIT DO

Example 2

Exit Do Statement is used when we want to exit Do Loops based on some criteria. It can be used with do until. When exit do is executed, control jumps to the next statement immediately after the do loop.

We will write the subroutine for simple do Until LOOP with Exit Do.

Sub UsewithExit() 

End Sub

After writing the subroutine we will define the variable

Sub UsewithExit () 
Dim i as Long

End Sub

Let’s take the value of i

Sub UsewithExit ()
Dim i as Long

i = 1
End Sub

Now enter the word ‘Do Until’. And after starting the loop name, enter the condition as “i > 1000”. close the loop by entering the word “LOOP”.

Sub UsewithExit () 
Dim i as Long 
i = 1 

Do Until i > 1000
Loop
End Sub

In the Active Sheet, now using the Cells property, enter the serial number. Note: Here, the variable “i” starts with 1, wherever “i” occurs it is equal to 1. Fill cube of i in the location of active sheet.

Sub UsewithExit ()
Dim i as Long 
i = 1 

Do Until i > 1000
    Activesheet.cells(i, 1).value = i ^ 3
Loop
End Sub

Now inside the loop, reassign the i value as i = i + 1.

Sub UsewithExit ()
Dim i as Long 
i = 1 

Do Until i > 1000
   Activesheet.cells(i, 1).value = i ^ 3
   i = i + 1
Loop
End Sub

Now I will write condition if the value of i exceeds 100 then code will stop. and use “End If”.

Sub UsewithExit ()
Dim i as Long 
i = 1 

Do Until i > 1000
   Activesheet.cells(i, 1).value = i ^ 3
   i = i + 1
   if i = 20 Then
      Exit Do
   End If
Loop
End Sub

After writing the code we will click in Run button.

After click on run button, we will go to active sheet where result will be printed, we have shown you the result, it has cube fill of i up to 20 cells but after every single cell a cell is blank. with Exit Do