×

Excel VBA While wend Loop

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

VBA While wend Loop

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
VBA While wend Loop2

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

VBA While wend Loop3

Related Topics

Excel VBA GoTo Statement

GoTo Statement he GoTo statement branches unconditionally to a specified line in a procedure. It is used to transfer the program control to a new statement, which is headed by a label. It sends...

2 minutes read.

Excel VBA CByte Function

VBA CByte Function: The CByte function in VBA converts an expression into a Byte data type. Syntax CByte (Expression) Parameter Expression (required) - This parameter represents the expression that that you want to convert to...

1 minute read.

Excel VBA RTrim Function

VBA RTrim Function: The Rtrim function in VBA removes the leading spaces from the text in the specified string. Syntax RTrim (String) Parameter String (required) – This parameter represents the string from which you want...

1 minute read.

Excel VBA CDec Function

VBA CDec Function: The CDec function in VBA converts an expression into a Decimal data type. Syntax CDec (Expression) Parameter Expression (required) – This parameter represents the expression that you want to convert to a...

1 minute read.

UserForm and its Properties Excel VBA

UserForm and its Properties Userform has certain properties that can be viewed as category wise (based on appearance, behavior, font) or in an alphabetic manner. The property window is used to set or...

11 minutes read.

VBA VBScript Regex Methods Regex

VBA VBScript Regex Methods Regex The VBA Regex supports 3 methods which are as follows: ExecuteReplaceText Execute The execute method is used to extract a match from the given based on the defined matching...

3 minutes read.

Excel VBA Split Function

Excel VBA Split Function: The Split function in VBA is used to split a string into several substrings and return a one-dimensional array of substrings. Syntax Split (Expression, [Delimiter], [Limit], [Compare]) Parameter Expression (required) – This parameter...

2 minutes read.

VBA Color Index Property

What is Color Index Property? The Excel VBA Color Index is used to change the color for the cell or range of cells or text (located under the Font section). It sets the color...

5 minutes read.

Excel VBA CInt Function

VBA CInt Function: The VBA Cint function converts the specified expression into an Integer. Syntax Cint (Expression) Parameter Expression (required) – This parameter represents the expression that you want to convert to an Integer wherein...

1 minute read.

Excel VBA WeekdayName Function

Excel VBA WeekdayName Function: The WeekdayName function in VBA returns a string containing the weekday name, for the specified integer representation of a weekday. Syntax WeekdayName (Weekday, [Abbreviate], [FirstDayOfWeek]) Parameter Weekday (required) – This parameter represents...

2 minutes read.

Excel VBA InStrRev Function

VBA InStrRev Function: The InStrRev function in Excel VBA returns an integer representing the position of a substring within the specified string if the substring is fount else it returns...

2 minutes read.

Excel VBA Choose Function

VBA Choose Function: The Choose function in VBA chooses function selects the corresponding value from a list of arguments depending as per the specified index. Syntax Choose (Index, [Choice-1], [Choice-2], ...) Parameter Index (required) – This...

2 minutes read.

Excel VBA: IF…..THEN …ELSE Statement

VBA : IF…..THEN …ELSE Statement: This function enables you to check one condition and, based on that, then run one of the two statement blocks present. If the ‘IF’ condition...

2 minutes read.

VBA ListBox

What is a ListBox? ListBox refers to a permanently displayed control (usually box-shaped) which contains a list of objects (or attribute, or elements) from which the user can select single or multiple attributes....

8 minutes read.

Excel VBA Oct Function

VBA Oct Function: The Oct function in VBA converts the given number into octal notation and returns the result as a string. Syntax Oct (Number) Parameter Number (required) – This parameter represents the numeric value...

1 minute read.

Excel VBA CCur Function

The CCur function in VBA is used to convert an expression into a Currency data type. It can take a maximum of 15 digits to the left of the decimal place and...

1 minute read.

Excel VBA UCase Function

The UCase function in VBA converts a String into upper case text. Syntax UCase (String) Parameter String (required) – This parameter represents the string that you want to convert to upper case. Return This function returns a string...

1 minute read.

VBA Subscript out of Range

What is Subscript out of Range? The VBA Subscript out of Range error (which is also called as Run-Time Error 9) mostly triggers when the user selects any cell, sheet, or workbook which does...

5 minutes read.

Steps to Create a Pivot Table

Steps to Create a Pivot Table: Pivot Tables can be easily automated through VBA coding. To create a Pivot Table in VBA, follow the below step by step procedure to...

4 minutes read.

Excel VBA ABS Function

VBA ABS Function: The ABS function in VBA returns the absolute value of the specified number. Syntax Abs (Number) Parameter Number (required) – This parameter represents the number that you want the absolute value of. Return This...

1 minute read.