How to Use the Do While Loop in Excel VBA

Download Practice Workbook

Download the practice workbook.

The Do While Loop in Excel VBA

The syntax is:

Do While Condition
[statements]
Loop

Condition: the primary criterion to run the do-while loop. If the condition is true, the do while loop will work continuously.

Statement: executes the do while loop.

Loop: denotes the end statement of the do while loop; goes back to the initial stage to re-run the do while loop.

Example 1 – Changing the Cell Color Based on Marks

The sample dataset includes students’ names and marks.

Steps

Go to the Developer tab.
Select Visual Basic.

In the Visual Basic window, go to the Insert tab.
Select Module.

Enter 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

Close the Visual Basic window.
Go to the Developer tab and select Macros.

In the Macro dialog box, select Do_While_Loop1 in Macro name.
Click Run.

Cells with marks greater than 79 are filled green.

VBA Code Breakdown

Sub Do_While_Loop1()

names the sub-procedure.

Dim i As Integer
i = 5

declares the variable; sets 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

in the do-while loop the value of i is less than 13. In the If condition, if the range value is greater than 79, the cell is filled with color. Otherwise, no color is added. A single increment of i is defined. This process continues until the condition becomes false.

End Sub

ends the sub-procedure.

Example 2 – Setting Remarks Using the Do While Loop

Steps

Create a new column to display the remarks.

Go to the Developer tab.
Select Visual Basic.

In the Visual Basic window, go to the Insert tab.
Select Module.

Enter 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

Close the Visual Basic window.
Go to the Developer tab and select Macros.

Select Do_While_Loop_Offset in Macro name.
Click Run.

This is the output.

VBA Code Breakdown

Sub Do_While_Loop_Offset()

names the sub-procedure.

Dim i As Integer
i = 5

declares the variable; sets 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

in the do-while loop the value of i is less than 13. In the If condition, if the range value is less than 33, it will return fail in the next column, using the Offset function. Otherwise, it will return pas. A single increment of i is defined. The process continues until the condition becomes false.

End Sub

ends the sub-procedure.

Example 3 – Executing the Do While Loop Using a Command Button

The dataset showcases a list of items sold in column A.

Steps

Go to the Developer tab.
Select Insert in Controls.

Choose Text Box in ActiveX Controls.

Draw a TextBox in the worksheet.
Right-click the TextBox.
Select Properties.

In the Properties dialog box, name the TextBox: txtresultDoWhileLoop.
Close the dialog box.

Go to the Developer tab.
Select Insert in Controls.
Choose Command Button in ActiveX Controls.

Draw the button in the worksheet.

Right-click the button and select Properties.
Change the Caption in the Properties.

The name of the button changed.

Right-click the button and select View Code.

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

Close Visual Basic
Deactivate Design Mode in Controls.

Enter Populate in the TextBox to activate the button.

Click the Set Category button.

It will show the categories of the items in column A.

VBA Code Breakdown

Private Sub CommandButton1_Click()

names the sub-procedure.

Application.ScreenUpdating = False

turns off Screen Updating.

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

two variables of the string data type are declared. The first variable is valueinText and the second variable is itemSold. valueinText gets its value from the text input in the TextBox.

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

the If statement checks if the valueinText value is Populate and selects B5 (the first cell containing data in the column Items Sold).

Do While ActiveCell.Value <> ""

opens the Loop Structure with the Do While ActiveCell.Value<>””.

Note

if this line is omitted, an infinite loop is created. It defines that the looping should occur as long as the active cell is not blank.

 itemSold = ActiveCell.Value

the second variable: itemSold takes the value of the active cell.

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

the If & Else If options are used for the fruits and vegetables within the actual loop. The If statement ends 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 ends, and goes back to the initial If statement. It declares that if the value in the TextBox Clear, content is cleared in the category column and formatting is cleared in the Items sold column. The conditional logic block ends with an end if statement.

End Sub

ends the sub-procedure of the macro

[/wpsm_box]

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

Format the cells with color based on the Region.

Steps

Go to the Developer tab.
Select Visual Basic.

In Insert, select Module.

Enter 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

Close Visual Basic
Go to the Developer tab and select Macros.

In the Macro dialog box, select Do_While_Loop2 in Macro name.
Click Run.

This is the output.

VBA Code Breakdown

Sub Do_While_Loop2()

names the sub-procedure of the macro.

Dim i As Integer
i = 5

declares the variable and sets 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

In the do-while loop where the value of i is less than 13. In the If condition, if the range value is equal to North, it will return the cell with a selected color; if the range value is equal to East, it will return the cell with a selected color. Otherwise, it will return another color. A single increment of i is defined. The process continues until the condition becomes false.

End Sub

ends the sub-procedure of the macro

How to Exit a Do While Loop with Excel VBA

Steps

Go to the Developer tab.
Select Visual Basic.

In Insert, select Module.

Enter 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

Close Visual Basic
Go to the Developer tab and select Macros.

In the Macro dialog box, select Exit_Do_While_Loop in Macro name.
Click Run.

A message box is displayed showing the blank cells.

The do-while loop stops working at this point.

VBA Code Explanation:

Sub Exit_Do_While_Loop()

names the sub-procedure.

Dim i As Integer
i = 5

declares the variable and sets 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

in the do-while loop where the value of i is less than 13. In the If condition, if the range value is blank, it will return a message box that includes the cell number of the blank cell. The do-while loop ends its procedure. A single increment of i is defined. The process continues until the condition becomes false.

End Sub

ends the sub-procedure.

Get FREE Advanced Excel Exercises with Solutions!

How to Use the Do While Loop in Excel VBA – 3 Examples

Download Practice Workbook

The Do While Loop in Excel VBA

Example 1 – Changing the Cell Color Based on Marks

Example 2 – Setting Remarks Using the Do While Loop

Example 3 – Executing the Do While Loop Using a Command Button

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

How to Exit a Do While Loop with Excel VBA

Related Articles

Leave a reply Cancel reply