Excel VBA Select Case Inside For... Next Loop in 7 Steps (+ Example)

In this Excel VBA Select Case Inside For… Next Loop Tutorial, you learn how to create an Excel VBA Select Case inside For… Next loop statement to:

Repeatedly execute a set of statements (including a nested Select Case statement) a specific number of times; and
(On each loop iteration) Conditionally execute a set of statements based on an expression's value.

This Excel VBA Select Case Inside For… Next Loop Tutorial is accompanied by an Excel workbook with the data and VBA code I use. Get this example workbook (for free) by clicking the button below.

The VBA code in the Excel workbook that accompanies this Excel VBA Select Case Inside For… Next Loop Tutorial is (always) stored in the Visual Basic Editor (VBE). If you don't know how to work with the VBE, I suggest you read my Visual Basic Editor (VBE) Tutorial. I link to this Tutorial in the Related Excel Macro and VBA Training Materials and Resources Section.

Table of Contents

Related Excel Macro and VBA Training Materials and Resources

The following Excel Macro and VBA Tutorials may help you better understand and implement this VBA Select Case Inside For… Next Loop Tutorial.

Tutorials about general macro and VBA constructs and structures:
- Tutorials for Beginners:
  - Excel Macros: Click here to open.
  - Excel VBA: Click here to open.
- Enable macros in Excel: Click here to open.
- Work with the Visual Basic Editor (VBE): Click here to open.
- Create Sub procedures: Click here to open.
- Work with:
  - Variables: Click here to open.
  - Data types: Click here to open.
  - Loops: Click here to open.
Tutorials with practical VBA applications and macro examples:
- MsgBox: Click here to open.

This Excel VBA Select Case Inside For… Next Loop Tutorial is part of a more comprehensive series of Excel VBA Select Case Tutorials.

Excel VBA Select Case Tutorial: Click here to open.
Excel VBA Select Case Or: Click here to open.
Excel VBA Select Case And Operator: Click here to open.
Excel VBA Select Case Multiple Test Expressions: Click here to open.
Excel VBA Select Case Like Wildcard: Click here to open.
Excel VBA Select Case Inside For… Next Loop: Click here to open.
Excel VBA Select Case Range of Cells: Click here to open.

You can find more Excel and VBA Tutorials in the organized Tutorials Archive: Click here to visit the Archives. The following are some of my most popular Excel Tutorials and Training Resources:

Excel Keyboard Shortcuts Cheat Sheet: Click here to open.
Work with the Excel XLOOKUP Function: Click here to open.
Excel Power Query (Get & Transform) Tutorial for Beginners: Click here to open.

If you want to learn how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

Premium Courses at the Power Spreadsheets Academy: Click here to open.
Books at the Power Spreadsheets Library: Click here to open.
VBA Cheat Sheets: Click here to open.

If you want to save time when working with macros and VBA, you may be interested in AutoMacro: Click here to learn more about AutoMacro (affiliate link). AutoMacro is an add-in for VBA that installs directly into the VBE. Depending on the version, AutoMacro comes loaded with:

Code generators.
An extensive code library.
The ability to create your own code library.
Advanced coding tools.

If you need help with Excel tasks/projects, you may be interested in working with me: Click here to learn more about working with me.

Excel VBA Select Case Inside For… Next Loop Snippet Template/Structure

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/select-case-inside-loop/
For Counter = Start To End Step StepSize
    Select Case TestExpression
        Case CaseExpression1
            CaseStatements1
        Case CaseExpression2
            CaseStatements2
        '...
        Case CaseExpression#
            CaseStatements#
        Case Else
            ElseStatements
    End Select
Next Counter

Step-by-Step Process to Create an Excel VBA Select Case Inside For… Next Loop Statement

(1) Enter the opening and closing statements of the For… Next loop:

For.
Next Counter.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/select-case-inside-loop/
For '...
    '...
Next Counter

(2) Specify the behavior of the For… Next loop's counter by setting the following items:

The loop counter's starting value.
The loop counter's ending value.
The value by which the loop counter changes with each iteration.
- The step size is optional.
- If you omit the step size, the loop counter increases (by default) by 1 with each iteration.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/select-case-inside-loop/
For Counter = Start To End Step StepSize
    '...
Next Counter

(3) Enter the opening and closing statements of the Select Case statement:

Select Case.
End Select.

To create an Excel VBA Select Case inside For… Next loop statement:

Nest this Select Case statement;
Inside the For… Next loop (you created in steps #1 and #2).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/select-case-inside-loop/
For Counter = Start To End Step StepSize
    Select Case '...
        '...
    End Select
Next Counter

(4) Specify the test expression VBA uses to identify the set of statements to execute.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/select-case-inside-loop/
For Counter = Start To End Step StepSize
    Select Case TestExpression
        '...
    End Select
Next Counter

(5) Specify the case expressions used by VBA to identify the set of statements to execute. Each case expression is preceded by the Case keyword.

Case CaseExpression1.
Case CaseExpression2.
…
Case CaseExpression#.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/select-case-inside-loop/
For Counter = Start To End Step StepSize
    Select Case TestExpression
        Case CaseExpression1
            '...
        Case CaseExpression2
            '...
        '...
        Case CaseExpression#
            '...
        '...
    End Select
Next Counter

(6) Specify the set of statements to execute when the applicable case expression (you specified in step #5) matches the test expression (you specified in step #4).

CaseStatements1.
CaseStatements2.
…
CaseStatements#.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/select-case-inside-loop/
For Counter = Start To End Step StepSize
    Select Case TestExpression
        Case CaseExpression1
            CaseStatements1
        Case CaseExpression2
            CaseStatements2
        '...
        Case CaseExpression#
            CaseStatements#
        '...
    End Select
Next Counter

(7) Specify the set of statements to execute if no case expression (you specified in step #5) matches the test expression (you specified in step #4). These catch-all statements:

Follow the Case Else keyword; and
Are optional.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/select-case-inside-loop/
For Counter = Start To End Step StepSize
    Select Case TestExpression
        Case CaseExpression1
            CaseStatements1
        Case CaseExpression2
            CaseStatements2
        '...
        Case CaseExpression#
            CaseStatements#
        Case Else
            ElseStatements
    End Select
Next Counter

How (and Why) the VBA Select Case Inside For… Next Loop Statement Works

The For… Next loop repeatedly executes a set of statements a specific number of times.

The Select Case statement does the following:

Compare a test expression to several case expressions.
Determine how to proceed based on the case expression that matches the test expression.

You can nest the Select Case statement inside the For… Next loop. In such cases:

The Select Case statement is repeatedly executed.
When the test expression matches an individual case expression, the Select Case statement:
- Executes the set of statements associated to the applicable Case clause; and
- Exits the Select Case statement (but not the For… Next loop).

A nested Select Case statement must be a stand-alone unit. In other words: A nested Select Case statement must:

Be complete; and
Comply with the applicable syntax/structure requirements.

Excel VBA Select Case Inside For… Next Loop Example Macro

The VBA Select Case inside For… Next loop example macro (below) does the following:

Loop through all integers between 1 and 10; and
Display a message box (in each iteration).

The message inside the message box varies, depending on the loop counter's (current) value. I use a VBA Select Case inside For… Next loop statement to specify the proper message.

Sub SelectCaseInsideForNextLoop()
    'Source: https://powerspreadsheets.com/
    'More information: https://powerspreadsheets.com/select-case-inside-loop/
    
    'Declare:
        'Loop counter variable:
        'Variable to represent message to be displayed in message box
    Dim iCounter As Long
    Dim MyMsgBoxMessage As String
    
    'Loop from 1 to 10
    For iCounter = 1 To 10
        
        'Assign a string to the MyMsgBoxMessage variable depending on the current loop counter value
        Select Case iCounter
            Case 1 To 3: MyMsgBoxMessage = "Loop counter's value is 1 to 3"
            Case 4 To 6: MyMsgBoxMessage = "Loop counter's value is 4 to 6"
            Case 7 To 9: MyMsgBoxMessage = "Loop counter's value is 7 to 9"
            Case Else: MyMsgBoxMessage = "Loop counter's value is 10"
        End Select
        
        'Display message box with string held by the MyMsgBoxMessage variable
        MsgBox MyMsgBoxMessage
    
    Next iCounter

End Sub

The VBA Select Case inside For… Next loop statement inside the VBA Select Case inside For… Next loop example macro specifies 4 possible messages:

For the first 3 iterations (iCounter = 1 to iCounter =3): “Loop counter's value is 1 to 3”.
For the next 3 iterations (iCounter = 4 to iCounter = 6): “Loop counter's value is 4 to 6”.
For the next 3 iterations (iCounter = 7 to iCounter = 9): “Loop counter's value is 7 to 9”.
For the last iteration (iCounter = 10): “Loop counter's value is 10”.

The GIF below displays the message boxes shown by Excel when I execute the Excel VBA Select Case inside For… Next loop example macro.

Excel VBA Select Case inside For... Next loop example macro results

Download the Excel VBA Select Case Inside For… Next Loop Example Workbook