Excel Made Easy

Loop WHILE END WHILE in VBA

If you want to repeat a statement or a process or make a loop of some instructions as long as on condition is TRUE then you can use the WHILE ... END WHILE statement. We will make a program in Excel VBA that loops through an endless range or entire column (starting in B4). In that example, we will look for specific data in an endless range in column B

In its basic form it is used that way.

While condition (true or false, something like donuts_counter < 10 )
Code you want to execute while condition is true
Wend will ends the loop

Now let's make an example with a small piece of VBA code that will go through an endless loop to check the quality of donuts the WHILE END WHILE Loop

VBA WHILE LOOP WEND statement

It is important to initialise your data before you enter the loop because if the while condition is not true at the beginning, it will not even enter the loop.

The code for this macro looks like this.

Sub donuts()

' looking for the bad donuts
i = 3                                                  ' initialise the first row where the list will begin
donuts_quality = "good"                     ' initialise the donuts quality to good
good_donuts = 0                               ' we set the number of good donuts to 0

While donuts_quality = "good"            ' begnining of the while loop

donuts_quality = Cells(i, 3)                  ' looks at the second column

If donuts_quality = "good" Then good_donuts = good_donuts + 1

i = i + 1

END WHILE   (Formerly WEND)                                      'end of the while loop

' final quality statement if we found a bad donuts then we say it.

If donuts_quality = "bad" Then
Range("E5").Value = "not all dounts were good, please improve"
Else
Range("E5").Value = "all donuts were good, excellent"
End If

End Sub

VBA WHILE LOOP WEND Statement

You can see the in the cell "E5", the judgement of the quality of the donuts has been written down by the VBA macro.

You made it, congratulation!!