Select Case with mixed numeric and text data

[Goshute]

Objective is to classify ranges of data to a series of categories in Excel, but the data includes both numbers and text. strService is always passed to the function Dim'd as Text. However the text arguments on the case line, such as "H0013", "H0018" etc. are not being evaluated, the function is returning "UNKNOWN", how do I fix this?

Private Function fncCategory(ByVal strService) As String
    Select Case strService
        Case 100, 114, 116, 124, 126, 134, 136, 144, 146, 154, 156
            fncCategory = "CAT1"
        Case 118, 128, 138, 148, 158, "H0013", "H0018"
            fncCategory = "CAT2"
        Case 912 To 918, 1000 To 1005, "S9485", "H0035"
            fncCategory = "CAT3"
        ...
        Case Else
            fncCategory = "UNKNOWN"
    End Select
End Function

(I have a strong sense of deja vu on this question, but can't find where I asked this before.)

[HansV]

I copied your function into a module in Excel, then used the function in worksheet formulas. It works for both number and text arguments:

x1339.png

It behaves the same when used in VBA code:

x1340.png

(I had to change the function to public for this)

[Goshute]

The function works, I had a logic flaw on the calling sub which was retaining and feeding a "last known value" if the current field was blank. Thanks, Hans.