How to Use Do While Loop in Excel VBA (3 Examples)

When you would like to repeat a statement as long as the condition meets, you can use the do while loop in Excel VBA. Using the do while loop, you can set multiple conditions and exit the do while loop. This article will show you how to use the do while loop in Excel VBA effectively. I hope you find this article very informative.


Download Practice Workbook

Download the practice workbook below.


Overview of Do While Loop in Excel VBA

Do while loop is a loop where you need to define the condition. If the condition is true then, the loop will run. Otherwise, it will stop working. It is a continuous process until the condition becomes false. The syntax of the do-while loop is given below.

Do While Condition
[statements]
Loop

Condition: You have to define the condition because it is the primary criterion for running the do-while loop. If the condition is true, do while loop will work continuously.

Statement: It is the line of code to execute the do while loop.

Loop: It denotes the end statement of the do while loop. It tells us to go back to the initial stage to re-run the do while loop.


3 Suitable Examples to Use Do While Loop in Excel VBA

To use the do while loop in Excel VBA, you have found three suitable examples through which you can have a clear overview of this topic. In these three examples, we would like to show different conditions where you can change cell color based on marks, set remarks, and execute the command button. All of these cases utilize the do while loop and provide meaningful results at the end. Before doing anything, you need to enable the Developer tab on the ribbon. To do this, follow the link How to Show the Developer Tab on the Ribbon. To use this VBA code, follow the steps carefully.


1. Changing Cell Color Based on Marks

Our first example is based on changing cell color based on student marks. Here, we utilize the do while loop and set a statement through which the VBA code will act for the dataset. In the end, we will add the proper explanation of the code. To show this method, we take a dataset that includes some students’ name and their marks.

Steps

  • First, go to the Developer tab on the ribbon.
  • Then, select the Visual Basic option from the Code group.

  • It will open up the Visual Basic window.
  • Then, go to the Insert tab at the top.
  • After that, select the Module option.

  • As a result, a Module code window will appear.
  • Write down the following code.
Sub Do_While_Loop1()
Dim i As Integer
i = 5
Do While i < 13
If Range("C" & i).Value > 79 Then
Range("C" & i).Interior.Color = RGB(0, 255, 51) 
Else  
End If
i = i + 1
Loop
End Sub
  • Then, close the Visual Basic window.
  • Then, go to the Developer tab on the ribbon.
  • Select the Macros option from the Code group.

  • Then, the Macro dialog box will appear.
  • Select Do_While_Loop1 from the Macro name section.
  • After that, click on Run.

Changing Cell Color Based on Marks Using Do While Loop in Excel VBA

  • Finally, you will get the desired results.
  • Students who get marks greater than 79 will convert the color into the given color.

Modifying Cell Color Based on Marks by Using Do While Loop in Excel VBA

🔎 VBA Code Explanation:

Sub Do_While_Loop1()

First of all, provide a name for the sub-procedure of the macro.

Dim i As Integer
i = 5

Next, declare the necessary variable for the macro. Then, set the initial value of i.

Do While i < 13
If Range("C" & i).Value > 79 Then
Range("C" & i).Interior.Color = RGB(0, 255, 51) 
Else  
End If
i = i + 1
Loop

After that, we use the do-while loop where the value of i is less than 13.  Then, we use an If condition where if the range value is greater than 79, it will return the cell with a given color. Otherwise, it will return nothing. Next, we define a single increment of i. This process will go on until the condition becomes false.

End Sub

Finally, end the sub-procedure of the macro


2. Setting Remarks Using Do While Loop

Our second example is based on setting remarks using the do while loop. Here, we utilize the do while loop and set a statement through which the VBA code will act for the dataset. In the end, we will add the proper explanation of the code. To show this method, we take a dataset that includes some students’ name and their marks

Steps

  • First, we create a new column where we would like to put our remarks.

  • After that, go to the Developer tab on the ribbon.
  • Then, select the Visual Basic option from the Code group.

  • It will open up the Visual Basic window.
  • Then, go to the Insert tab at the top.
  • After that, select the Module option.

  • As a result, a Module code window will appear.
  • Write down the following code.
Sub Do_While_Loop_Offset()
Dim i As Integer
i = 5
Do While i < 13
If Range("C" & i).Value < 33 Then
Range("C" & i).Offset(0, 1).Value = "Fail"
Else
Range("C" & i).Offset(0, 1).Value = "Pass"
End If
i = i + 1
Loop
End Sub
  • Then, close the Visual Basic window.
  • Then, go to the Developer tab on the ribbon.
  • Select the Macros option from the Code group.

  • Then, the Macro dialog box will appear.
  • Select Do_While_Loop_Offset from the Macro name section.
  • After that, click on Run.

Setting Remarks Using Do-While Loop in Excel VBA

  • As a result, we will get the required remarks from the given student marks.
  • In this case, the do-while loop verifies all the cases and gives the desired remarks.

Providing Remarks Using Do-While Loop in Excel VBA

🔎 VBA Code Explanation:

Sub Do_While_Loop_Offset()

First of all, provide a name for the sub-procedure of the macro.

Dim i As Integer
i = 5

Next, declare the necessary variable for the macro. Then, set the initial value of i.

Do While i < 13
If Range("C" & i).Value < 33 Then
Range("C" & i).Offset(0, 1).Value = "Fail"
Else
Range("C" & i).Offset(0, 1).Value = "Pass"
End If
i = i + 1
Loop

After that, we use the do-while loop where the value of i is less than 13.  Then, we use an If condition where if the range value is less than 33, it will return fail in the next column by using offset. Otherwise, it will return pass in the next column by using offset. Next, we define a single increment of i. This process will go on until the condition becomes false.

End Sub

Finally, end the sub-procedure of the macro.


3. Executing Do While Loop Using Command Button

We have a worksheet with a list of items sold in one column. We would like to use a Do While Loop in order to evaluate the list. If the item sold was Apples in the Items Sold column, then we would like no formatting to be applied but to show the category as fruit.

For every fruit, we would like to show the category. In the next column which states the Category, we would like to see the fruit’s name written just beside the fruits which have been sold.

If the cell at hand in the Items Sold column, has a vegetable listed, then we would like no formatting to be applied, and the cell next to it in the category column to receive a value of vegetables. To do the example carefully, follow the steps.

Steps

  • First, go to the Developer tab on the ribbon.
  • Then, select Insert drop-down option from the Controls group.

  • After that, choose Text Box under the ActiveX Controls section

  • Then, draw a TextBox on the worksheet.
  • After that, right-click on TextBox.
  • Select Properties from the Context Menu.

  • The Properties dialog box will appear.
  • Then, the Name of the TextBox is txtresultDoWhileLoop.
  • Next, close the dialog box.

  • Then, go to the Developer tab on the ribbon again.
  • Select Insert drop-down option from the Controls group.
  • Then, choose Command Button under the ActiveX Controls section.

  • Next, draw the button on your worksheet.

Acting Do While Loop Using Command Button in Excel VBA

  • Then, right-click on the button and select Properties from the Context Menu.
  • After that, change the Caption from the Properties dialog box.

  • As a result, the name of the button is changed. See the screenshot.

Utilizing Do While Loop by Using Command Button in Excel VBA

  • Then, right-click on the button and select View Code from the Context Menu.

  • After that, enter the following code for the button click event.
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim valueinText As String
Dim itemSold As String
valueinText = txtresultDoWhileLoop.Text
If valueinText = "Populate" Then
Range("B5").Select
Do While ActiveCell.Value <> ""
itemSold = ActiveCell.Value
If itemSold = "Apples" Then
 ActiveCell.Offset(0, 1).Value = "Fruit"
 ElseIf itemSold = "Cherries" Then
 ActiveCell.Offset(0, 1).Value = "Fruit"
 ElseIf itemSold = "Oranges" Then
 ActiveCell.Offset(0, 1).Value = "Fruit"
Else
 ActiveCell.Offset(0, 1).Value = "Vegetables"
 End If
ActiveCell.Offset(1, 0).Select
Loop
ElseIf valueinText = "Clear" Then
Range("B5:B550").ClearContents
Range("C5:C550").ClearFormats
End If
End Sub
  • Then, close the Visual Basic
  • After that, you need to deactivate the Design Mode from the Controls group.

  • Then, write down Populate in the TextBox in order to activate the button.

Executing Do While Loop Using Command Button in Excel VBA

  • Finally, click on the Set Category command button.
  • It will show the categories of your given items. See the screenshot.

Applying Do While Loop Using Command Button in Excel VBA

🔎 VBA Code Explanation:

Private Sub CommandButton1_Click()

First of all, provide a name for the sub-procedure of the macro.

Application.ScreenUpdating = False

The next thing we are doing in the code is turning off Screen Updating, this ensures that the screen does not flicker while the code is running.

Dim valueinText As String
Dim itemSold As String
valueinText = txtresultDoWhileLoop.Text

We then declared two variables of the string data type. The first variable is called valueinText and the second variable is called itemSold. valueinText gets its value from the text input in the TextBox we created on the worksheet.

If valueinText = "Populate" Then
Range("B5").Select

Then we enter a conditional logic block with the If statement. Using the If statement we check if the valueinText value is Populate. If the user has entered the word Populate in the text box, then the first thing that needs to be done is that cell B5 in the worksheet needs to be selected. This is the first cell containing data under the column heading Items Sold.

Do While ActiveCell.Value <> ""

We then open the Loop Structure with the Do While ActiveCell.Value<>””.

Note

This is a very important line since if it is omitted, we would basically be creating what is known as an infinite loop. This line says that looping should occur as long as the active cell is not blank.

 itemSold = ActiveCell.Value

We then state that the second variable we declared itemSold will take on the value of whatever the active cell value is.

If itemSold = "Apples" Then
ActiveCell.Offset(0, 1).Value = "Fruit"
ElseIf itemSold = "Cherries" Then
ActiveCell.Offset(0, 1).Value = "Fruit"
ElseIf itemSold = "Oranges" Then
ActiveCell.Offset(0, 1).Value = "Fruit"
Else
 ActiveCell.Offset(0, 1).Value = "Vegetables"
 End If
ActiveCell.Offset(1, 0).Select
Loop

We then open the If & Else If options for conditional logic statement (for the fruits and vegetables) within the actual loop, which will have to be evaluated as part of the loop. Then, end the If statement within the actual loop with an End if.

ElseIf valueinText = "Clear" Then
Range("B5:B550").ClearContents
Range("A5:A550").ClearFormats
End If

Then, we end the loop, and then we go back to the initial If statement and declare that if the value in the TextBox on the worksheet is Clear, then clear the contents in the category column and clear all the formatting in the Items sold column. then finish the overall conditional logic block with an end if statement.

End Sub

Finally, end the sub-procedure of the macro

[/wpsm_box]

How to Use Do While Loop with Multiple Conditions Using Excel VBA

We can use the do-while loop for multiple conditions. In this process, we can define several conditions and use the do while loop code accordingly. It will give us the required solution. To show the process, we take a dataset that includes the salesman’s name, region, and sales amount. Follow the steps carefully. Here, we will format the cells with color based on the Region.

Steps

  • First, go to the Developer tab on the ribbon.
  • Then, select the Visual Basic option from the Code group.

  • It will open up the Visual Basic window.
  • Then, go to the Insert tab at the top.
  • After that, select the Module option.

  • As a result, a Module code window will appear.
  • Write down the following code.
Sub Do_While_Loop2()
Dim i As Integer
i = 5
Do While i < 13
If Range("C" & i).Value = "North" Then
Range("C" & i).Interior.Color = RGB(204, 255, 0)
ElseIf Range("C" & i).Value = "East" Then
Range("C" & i).Interior.Color = RGB(153, 255, 255)
Else
Range("C" & i).Interior.Color = RGB(255, 255, 0)
End If
i = i + 1
Loop
End Sub
  • Then, close the Visual Basic
  • Then, go to the Developer tab on the ribbon.
  • Select the Macros option from the Code group.

  • Then, the Macro dialog box will appear.
  • Select Do_While_Loop2 from the Macro name section.
  • After that, click on Run.

Using Do While Loop with Multiple Conditions Using Excel VBA

  • As a result, we will get the desired solution for multiple conditions. See the screenshot.

Utilizing Do While Loop with Multiple Conditions Using Excel VBA

🔎 VBA Code Explanation:

Sub Do_While_Loop2()

First of all, provide a name for the sub-procedure of the macro.

Dim i As Integer
i = 5

Next, declare the necessary variable for the macro. Then, set the initial value of i.

Do While i < 13
If Range("C" & i).Value = "North" Then
Range("C" & i).Interior.Color = RGB(204, 255, 0)
ElseIf Range("C" & i).Value = "East" Then
Range("C" & i).Interior.Color = RGB(153, 255, 255)
Else
Range("C" & i).Interior.Color = RGB(255, 255, 0)
End If
i = i + 1
Loop

After that, we use the do-while loop where the value of i is less than 13.  Then, we use an If condition where if the range value is equal to North, it will return the cell with a given color or if the range value is equal to East, it will return the cell with a given color. Otherwise, it will return another color. Next, we define a single increment of i. This process will go on until the condition becomes false.

End Sub

Finally, end the sub-procedure of the macro


How to Exit a Do While Loop with Excel VBA

We can utilize the do while loop in such a way that it can exit the do while loop after getting a certain value or text. In this code, we will define that certain character to run the do while loop in Excel VBA. To show the process, we take a dataset that includes the salesman’s name and their sales amount. Here, there is a blank in the sales amount column. Follow the steps carefully.

Steps

  • First, go to the Developer tab on the ribbon.
  • Then, select the Visual Basic option from the Code group.

  • It will open up the Visual Basic window.
  • Then, go to the Insert tab at the top.
  • After that, select the Module option.

  • As a result, a Module code window will appear.
  • Write down the following code.
Sub Exit_Do_While_Loop()
Dim i As Integer
i = 5
Do While i < 13
If Range("C" & i).Value = "" Then
MsgBox "Blank Cell Found at Cell " & "C" & i
Exit Do
End If
i = i + 1
Loop
End Sub
  • Then, close the Visual Basic window.
  • Then, go to the Developer tab on the ribbon.
  • Select the Macros option from the Code group.

  • Then, the Macro dialog box will appear.
  • Select Exit_Do_While_Loop from the Macro name section.
  • After that, click on Run.

  • As a result, we will get the message box where it finds the blank.
  • The do-while loop will stop working at this point.

Exit a Do-While Loop with Excel VBA

🔎 VBA Code Explanation:

Sub Exit_Do_While_Loop()

First of all, provide a name for the sub-procedure of the macro.

Dim i As Integer
i = 5

Next, declare the necessary variable for the macro. Then, set the initial value of i.

Do While i < 13
If Range("C" & i).Value = "" Then
MsgBox "Blank Cell Found at Cell " & "C" & i
Exit Do
End If
i = i + 1
Loop

After that, we use the do-while loop where the value of i is less than 13.  Then, we use an If condition where if the range value is blank, it will return a message box that includes the cell number of the found blank cell. After that, the do-while loop will end its procedure. Next, we define a single increment of i. This process will go on until the condition becomes false.

End Sub

Finally, end the sub-procedure of the macro.

Read More: For Next Loop in VBA Excel (How to Step and Exit Loop)


Things to remember

  • The do while loop will continue its task until the condition is true.
  • You can verify the condition at the beginning or at the very end of the do-while loop.
  • If the condition is false, the do-while loop will exit.

Conclusion

We have shown three different examples to use the do while loop in Excel VBA effectively. All of these examples are fairly easy to understand. We have also shown the use of the do-while loop for multiple conditions and how to exit the do-while loop in Excel VBA. I hope we covered all possible areas of this topic. If you have any questions, feel free to ask in the comment box. Don’t forget to visit our Exceldemy.com.


Related Articles

Taryn N

Taryn N

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

ExcelDemy
Logo