[SOLVED] Case argument that verifies if value is in a range of cells

Case argument that verifies if value is in a range of cells

I have VBA code iterating through Excel data that boils down to

Sub Simple()
    For Each cell In Range("E3:E2000")
        Select Case cell.Value
            Case "AA"
            'Unique1
            Case "BB"
            'Unique2
            Case "CC", "DD", "FF", "JJ", "MM"
            'Standard Set 1
            Case "EE", "HH", "II", "NN"
            'Standard Set 2
            Case Else
            
        End Select
    Next cell
End Sub

Standard set 1 & 2 have at least a dozen situations in each and has potential in the future to have items added, removed, or moved to the other set. I would like to create a range of cells that contains all of the conditions for Set 1 and a separate range for Set 2.

How do I write a Case statement that checks that range?

Per other posts I tried to create a test program using VLookup:

Sub Test2()
    Data = Range("F2").Value
    'On Error Resume Next
        
    Select Case Data
        Case Application.WorksheetFunction.VLookup(Data, Sheet13.Range("A:A"), 1, False)
            MsgBox ("Std1")
        Case Application.WorksheetFunction.VLookup(Data, Sheet13.Range("B:B"), 1, False)
            MsgBox ("Std2")
        Case Else
            MsgBox ("Not Found")
    End Select
End Sub

If the Data is in A:A it returns "Std1", if its in B:B (or not at all); I get

Run-time error '1004' Unable to get the VLookup property of the WorksheetFunction class

I believe is because the VLookup would return a #N/A in a cell. I tried On Error Resume Next but that results in it always returning "Std1".

I'm not set on using VLookup. It is what looked the most promising. Everything else wants another for loop which would be extremely clunky since it would be nested inside a for loop iterating through a huge amount of data.

Solution

Working from your original code:

Sub Simple()
    Dim v, cell As Range
    For Each cell In Range("E3:E20")
        v = cell.Value
        If Len(v) > 0 Then
            Select Case v
                Case "AA"
                    Debug.Print v, "Unique 1"
                Case "BB"
                    Debug.Print v, "Unique 2"
                Case MatchedValue(v, sheet13.Columns("A"))
                    Debug.Print v, "Std A"
                Case MatchedValue(v, sheet13.Columns("B"))
                    Debug.Print v, "Std B"
                Case Else
                    Debug.Print v, "Unknown mapping"
            End Select
        End If
    Next cell
End Sub

'Is there an exact match for `v` in the (single-column) range `rng`?
'If Yes return `v` else return empty string
Function MatchedValue(v, rng As Range)
    Dim m
    m = Application.Match(v, rng, 0)
    MatchedValue = IIf(IsError(m), "", v)
End Function