CODEWITHSUNDEEP
excelvba
user1916528
user1916528

Reputation: 389

How to use multiple criteria in a Select Case in Excel

I would like to modify the following select case to use the values in G11 and G13 to place a value in C17, as well as a value in A17. It currently just uses G11 to put a value in A17. How can I modify it to also look at G13 and place a value in C17?

For example, if the value in G11 is between .01 and 4999.99, and the value in G13 is "Inspector", then the value in C17 is "100".

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Set KeyCells = Range("G11")

        If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then

            Select Case Range("G11").Value
                Case Is < 0.01
                    Range("A17").Value = ""
                Case 0.01 To 4999.99
                    Range("A17").Value = "$0 - $4,999.99"
                Case 5000 To 9999.99
                    Range("A17").Value = "$5,000 - $9,999.99"
                Case 10000 To 19999.99
                    Range("A17").Value = "$10,000 - $19,999.99"
                Case 20000 To 34999.99
                    Range("A17").Value = "$20,000 - $34,999.99"
                Case 35000 To 49999.99
                    Range("A17").Value = "$35,000 - $49,999.99"
                Case 50000 To 99999.99
                    Range("A17").Value = "$50,000 - $99,999.99"
                Case 100000 To 149999.99
                    Range("A17").Value = "$100,000 - $149,999.99"
                Case 150000 To 199999.99
                    Range("A17").Value = "$150,000 - $199,999.99"
                Case Is >= 200000
                    Range("A17").Value = "$200,000 and up"
            End Select
        End If
    End Sub

Upvotes: 1

Views: 509

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

As per my comment

Select Case True
    Case Range("G11")>.01 and Range("G11")<4999.99 and Range("G13") = "Inspector"
        *Do your code*
   ...

By putting the Select Case as true, then in the Case lines you can use the And and Or operators.

But as you can see this then becomes a IF/ElseIF/Else situation.

Upvotes: 3

Related Questions