WHILE wend loop is used when the user is not sure how many times they want to execute the VBA code within the program. With a WHILE loop, the loop body may not even execute once.

If the set of condition estimates to True, the statements or the code between the While and Wend statements is executed. After the Wend statement is executed, control is passed back up to the While statement, where the specified condition is checked again. When condition estimates to False, the program execution skips to the first statement following the Wend statement. You can nest While...Wend loops within each other.

Syntax

While (condition)
    [statements 1]
    [statements 2]
    ... 
    [statements n]
 Wend 
         'If condition evaluates to True, statements between the While and Wend statements is executed
         Worksheets("Sheet2").Cells(val, 1).Value = val
         val = val + 2
     'Closing the While loop
     Wend
 End Sub

Example 1: Write a Macro in VBA using while wend loop to print the series of odd number between 1 to 10.

 Sub While_Wend_Loop_Example1()
 Dim val As Integer
 val = 1
     'While will loop until the cells' value is not equal to empty
     While val <= 10
         'If condition evaluates to True, statements between the While and Wend statements is executed
         Worksheets("Sheet2").Cells(val, 1).Value = val
         val = val + 2
     'Closing the While loop
     Wend
 End Sub

Output

Example 2: Write a procedure or macro using While wend that does the following:

Get "Qualified" in column E if the value of column D is above 200

Sub While_Wend_Loop_Example2()
 r = 2
     'If condition evaluates to True, statements between the While and Wend statements is executed.
     While Cells(r, 4) <> ""
         If Cells(r, 4) > 200 Then
             Cells(r, 5) = "Qualified"
         Else 
             Cells(r, 5) = "Disqualified"
         End If
         r = r + 1
     Wend
 End Sub

Output

Name	Title	Date	Sale Amount	Qualified/Not Qualified
Alan Isaac Rothenberg	IT Manager	6/8/2016	$220.00	Qualified
Christopher J. Murphy III	Sales	1/1/2014	$251.00	Qualified
Elmer C. Laslo	Customer Service	6/16/2016	$217.00	Qualified
Frank Zane	Data Entry Specialist	5/31/2015	$344.00	Qualified
Gerard M. Banmiller	Customer Service	1/11/2014	$980.00	Qualified
Gregory L. Gibson	IT Manager	11/22/2015	$178.00	Disqualified
John C. Black	Customer Service	3/31/2014	$258.00	Qualified
N. William White	Data Entry Specialist	12/21/2015	$38.00	Disqualified
Robert F. Mangano	Data Entry Specialist	7/16/2015	$371.00	Qualified
Ronald J. Carlson	IT Tech	6/8/2016	$67.00	Disqualified
Thomas E. Vessey	Customer Service	3/21/2016	$437.00	Qualified

Example 3: Write a macro or procedure in VBA using nested while wend loop to print the dual dates of different months.

Sub While_Wend_Loop_Example3()
    Dim LVal1 As Integer
    Dim LVal2 As Integer
     r = 2
    LVal1 = 1
    LVal2 = 7 
    While LVal1 < 6
       'While inside while
       While LVal2 < 11
          Cells(r, 1) = LVal1 & "-" & LVal2
          LVal2 = LVal2 + 1
          r = r + 1 
       Wend
       LCounter2 = 8
       LVal1 = LVal1 + 1
    Wend
 End Sub

Output

Excel VBA While wend Loop

Syntax