CODEWITHSUNDEEP
vbaexcel
Robby
Robby

Reputation: 827

Excel - Select Case with multiple criteria

The code below is sort of an excerpt of my full Sub just because it'll be easier to read here. I need to add more criteria to some of the cases, and I'm not sure how to implement that.

To sum up what this is doing, in a row, if column K has a word that begins with B, M, or D, it runs a set of cases. If column K has a word that begins with an A, it runs a different set of cases. Again, this is working. But in some of these cases, I need it to have more criteria instead of only looking at column O.

For example, in the first case, it needs to check for "University of Illinois" and "UofI," but it also needs to check column P for the values of "Urbana" and "Chicago." How can I do that?

Sub Test4()
    Dim LastRow As Long
    Dim i As Long
    LastRow = Range("O" & Rows.Count).End(xlUp).Row
    For i = 84 To LastRow
        If Range("K" & i) Like "B*" Or Range("K" & i) Like "M*" Or Range("K" & i) Like "D*" Then
            Select Case Range("O" & i)
                Case "University of Illinois", "UofI"
                    Range("N" & i) = "1234"
            End Select
        End If
        If Range("K" & i) Like "A*" Then
            Select Case Range("O" & i)
                Case "New York University", "NYU"
                    Range("N" & i) = "5075"
            End Select
        End If
    Next i
End Sub

Upvotes: 1

Views: 8810

Answers (3)

user3598756
user3598756

Reputation: 29421

since you need to catch "word that begins with" you could go like this

Sub Test4()
    Dim LastRow As Long, i As Long

    LastRow = Range("O" & Rows.count).End(xlUp).row
    For i = 84 To LastRow
        Select Case Left(Range("K" & i).Value, 1)
            Case "B", "M", "D"
                Select Case Range("O" & i).Value
                    Case "University of Illinois", "UofI"
                        Range("N" & i) = "1234"
                End Select
            Case "A"
                Select Case Range("O" & i).Value
                    Case "New York University", "NYU"
                        Range("N" & i) = "5075"
                End Select
        End Select
    Next i
End Sub

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33692

Under normal circumstances, the Select Case statement doesn't work with the Like operator. However, there is a work-around.

In order for the Select Case to work with the Like, we will add a True expression.

Code

Sub Test4()

    Dim LastRow As Long
    Dim i As Long

    LastRow = Range("O" & Rows.Count).End(xlUp).Row
    For i = 84 To LastRow

        Dim wordStr
        wordStr = Range("K" & i)

        ' adding True to enter the below Case with Like
        Select Case True
            Case wordStr Like "B*", wordStr Like "M*", wordStr Like "D*"
                Select Case Range("O" & i)
                    Case "University of Illinois", "UofI"
                        Range("N" & i) = "1234"

                End Select

            Case wordStr Like "A*"
                Select Case Range("O" & i)
                    Case "New York University", "NYU"
                        Range("N" & i) = "5075"

                End Select

        End Select
    Next i

End Sub

Upvotes: 3

mojo3340
mojo3340

Reputation: 549

Select Case Range("O" & i)
    Case "University of Illinois", "UofI"
        Select Case Range("P" & i")
            Case "Urbana", "Chicago"
                Range("N" & i) = "1234"
        End Select
End Select

Does this suffice? Have not tested so please feedback so we can get you to a solution

Upvotes: 3

Related Questions