How to Use the Do While Loop in Excel VBA

We have already covered an introduction to looping and the simplest type of loops, namely the For…Next Loop and the For Each…Next Loop, in previous tutorials. We discovered that the For…Next Loop and the For…Each Next Loop are utilized when the statements in the code, need to be repeated for a finite number of times. We are now going to look at how to use the Do While Loop in Excel VBA.

Read More: How to Use the For Each Next Loop in Excel VBA

Excel VBA Do While Loop Example

The Do While loop repeats a statement whenever a condition becomes true. The statement is executed every time in a loop whenever the condition is found true. When a false result is found while performing the test the loop stops.

Learn Excel VBA Online: Top Excel VBA Courses Online

We will begin with a short example, where we will see how this Do While loop in Excel VBA actually works.

  • Let’s put some numbers in a column.

  • Now press on the Visual Basic under the Developer option. If there is any code written in the new sheet, erase that and copy the following code there.
Sub loopexample()
 
Dim i As Integer
 
i = 1
 
Do While Cells(i, 1).Value <> ""
 
    Cells(i, 2).Value = Cells(i, 1).Value - 5
 
    i = i + 1
 
Loop
 
End Sub
  • Run the code and you will see the following result.

Do While loop excel

Here we can see the second column contain some numbers and those are the subtracted result from each row of the 1st column. The condition (Cells(i, 2).Value = Cells(i, 1).Value – 5) checks each cell of the 1st column. As after the 9th row, there is no number for the rest of the 1st column the second column stops executing numbers.

You can follow the link below for more example regarding the Do While Loop in  Excel VBA.

http://www.excel-easy.com/vba/loop.html

Do While Loop in Excel VBA (Using a 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 the actual cell containing the text Apples to receive a blue italicized font.

In the same way, if the item sold was cherries then we would like a dark pink italicized font and for oranges, we would like an orange italicized font. For every fruit with the different color italicized font, we would like the cell containing fruits to be double underlined. In the next column which states the Category, we would like to see the fruits name written just beside the fruits which have been sold.

Read More: How to Use Do Until Loop in Excel VBA

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.

Now, let’s begin the Do While loop for the specified formatting that we discussed above.

The source data is shown below.

1) So, first things first go to Developer>Controls>Insert and under the ActiveX Controls section, choose Text Box as shown below.

2) Draw a TextBox on the worksheet.

3) Using the Properties Window, name the TextBox, txtresultDoWhileLoop.

4) Now, go to Developer>Controls>Insert and under the ActiveX Controls section, choose Command Button and draw a button on the worksheet.

5) Using the Properties Window, name the Command Button, cmdsubmitResult and change the caption to Submit.

6) Right-click the button, you just created and select View Code.

7) Enter the following code for the button click event.

Private Sub cmdsubmitResult_Click()
 
Application.ScreenUpdating = False
 
Dim valueinText As String
 
Dim itemSold As String
 
valueinText = txtresultDoWhileLoop.Text
 
If valueinText = "Populate" Then
 
    Range("A5").Select
 
    Do While ActiveCell.Value <> ""
 
    itemSold = ActiveCell.Value
 
    If itemSold = "Apples" Then
 
    ActiveCell.Font.Color = RGB(0, 114, 230)
 
    ActiveCell.Font.Italic = True
 
    ActiveCell.Font.Underline = xlUnderlineStyleDouble
 
    ActiveCell.Offset(0, 1).Value = "Fruit"
 
    ElseIf itemSold = "Cherries" Then
 
    ActiveCell.Font.Color = RGB(237, 55, 124)
 
    ActiveCell.Font.Italic = True
 
    ActiveCell.Font.Underline = xlUnderlineStyleDouble
 
    ActiveCell.Offset(0, 1).Value = "Fruit"
 
    ElseIf itemSold = "Oranges" Then
 
    ActiveCell.Font.Color = RGB(222, 148, 16)
 
    ActiveCell.Font.Italic = True
 
    ActiveCell.Font.Underline = xlUnderlineStyleDouble
 
    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("A5:A550").ClearFormats
 
End If
 
End Sub

 

8) Return to the worksheet and make sure Design Mode is not activated, enter the text Populate in the TextBox, on the worksheet as shown below.

9) Click on the Submit button in order to format the text in the Items Sold column and populate the Category column, simultaneously with the categories specified, in the code as shown below.

Do While Loop in Excel VBA

10) Then type Clear in the text box on the worksheet and click on the Submit button.

The Category column is now cleared of all the values, and the Items Sold column is cleared of all the specific formatting. And there you have it.

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

The Code Description of the Do While Loop in Excel VBA

The first 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.

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.

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 the cell A5 in the worksheet needs to be selected. This is the first cell containing data under the column heading Items Sold.

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.

Do While Loop in Excel VBA

Click on the image to see a larger version

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

We then open the If & Else If options for conditional logic statement (for the fruits and vegetables) within the actual loop, that will have to be evaluated as part of the loop.

We end the If statement within the actual loop with an End if.

We then specify that we need to move on to the cell immediately below the former active cell once the statements have been evaluated and executed, using this line of code: ActiveCell.Offset(1, 0).Select.

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. We then end the overall conditional logic block with an end if statement.

Read More: IF & AND Functions in Excel VBA to Test Multiple Conditions

Download Working File

DoWhileLoopExcelVBA

Conclusion

The Do While Loop in Excel VBA is utilized when one wants to repeat statements or actions, as long as a certain specified condition evaluates to true. It is slightly more complex than the For…Next Loop Structure and the For…Each Next Loop Structure, however, it is more versatile and can be used when one is not sure of a number of times exactly that the statements in the loop have to be repeated.

Please feel free to tell us if you use the Do While Loop structure in your VBA code.

Review Section: Test your Understanding

1) When does one use a Do While Loop in Excel VBA?

2) What is an infinite loop?

3) How does one exit an infinite loop?

4) Using a Do While Loop, fill each cell in a range containing data, with a blue fill, up until one reaches a blank cell.


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