How to use Do While Loop in Excel VBA Easily (2 Type)

  Welcome to Excel Avon

What is Do While Loop?

DOWNLOAD EXCEL FILE FROM HERE>>

Today’s article is about do while loop, today we are going to understand how do while loop works, do while loop means to do something while condition is true. The VBA do while loop is used to execute the given statements as long as the condition is true. If the condition is false on the first check, the execution exits the loop without executing the given statements even once. Or we can just say that
which executes a set of statements repeatedly while a condition remains true. When the condition becomes false the loop terminates.

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 While Loop in Excel VBA

Example 1

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

Sub UseDoWhileSimple()

End Sub

After writing the subroutine we will define the variable

Sub UseDoWhileSimple()
Dim i as Long

End Sub

Let’s take the value of i as the value

Sub UseDoWhileSimple()
Dim i as Long

i = 1
End Sub

Now enter the word “Do While”. And after starting the loop name, enter the condition as “i <= 100”. close the loop by entering the word “LOOP”.

Sub UseDoWhileSimple()
Dim i as Long

i = 1
Do While i<= 100
Loop
End Sub

In the activesheet, Now 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. We will fill square of i in the location of active sheet 

Sub UseDoWhileSimple()
Dim i as Long

i = 1
Do While i<= 100
   activesheet.cells (i, 1). value = i ^ 2
Loop
End Sub

Now I will write code for every next step.

Sub UseDoWhileSimple()
Dim i as Long

i = 1
Do While i< = 100
    activesheet.cells (i, 1). value = i ^ 2
    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, yes, all the cells will not be visible in the image, but we have shown you the result, it has square fill of i till 100 cells

Example 2

We will go back to the VBE and in this we will write the value for a cell blank after Each step.

Sub UseDoWhileSimple() 
Dim i as Long 

i = 1 
Do While i< = 100 
   activesheet.cells (i, 1). value = i ^ 2
    i = i + 2
Loop 
End Sub

After click on run button, we will go to active sheet where result will be printed, yes, not all cells will be visible in the image, but we have shown you the result, it has square fill of i up to 100 cells but after every single cell a cell is blank.

Do While Loop with Exit Do

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

Example 1

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

Sub UseDoWithExit()

End Sub

After writing the subroutine we will define the variable

Sub UseDoWithExit()
Dim i as Long

End Sub

Let’s take the value of i as the value

Sub UseDoWithExit()
Dim i as Long

i = 1
End Sub

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

Sub UseDoWhileSimple() 
Dim i as Long 

i = 1 

Do While i > 0
Loop 
End Sub

In the Active sheet, now using the Cell 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. We will fill in the cube of i in place of the active sheet.

Sub UseDoWhileSimple() 
Dim i as Long

i = 1 

Do While i > 0
   activesheet.cells(i, 1) = i ^ 3
Loop 
End Sub

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

Sub UseDoWhileSimple() 
Dim i as Long

i = 1 

Do While i > 0
    activesheet.cells(i, 1) = 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 UseDoWhileSimple() 
Dim i as Long

i = 1 

Do While i > 0
   activesheet.cells(i, 1) = i ^ 3
   i = i + 1
   if i > 100 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 activesheet

Example 2

Now we will go back to VBE and then add exit do for 20 cells

Sub UseDoWhileSimple() 
Dim i as Long 

i = 1 

Do While i > 0 
   activesheet.cells(i, 1) = i ^ 3 
    i = i + 1 
    if i > 20 then 
         Exit Do 
End If 
Loop 
End Sub

After click on run button, we will go to active sheet where result will be printed, yes, not all cells will be visible in the image, but we have shown you the result, it has cube fill up to 100 cells, but we have used exit do in the code is stopped after 20 cells.