VBA: Passing multiple values to Instr

Question

Right now I have a long list of "Industry" values that users have submitted through a form. I've written a macro that will search for particular terms in those values and paste values that conform to my significantly smaller list of "acceptable" Industry values. The point is to reclassify all of the current industry values users have submitted into my existing taxonomy. Here's the way my If-Then statements look as of right now:

If InStr(1, Cells(i, "A").Value, "Energy") > 0 Or InStr(1, Cells(i, "A").Value, "Electricity") > 0 Or InStr(1, Cells(i, "A").Value, "Gas") > 0 Or InStr(1, Cells(i, "A").Value, "Utilit") > 0 Then
Cells(i, "B").Value = "Energy & Utilities"
End If

It's pretty ugly. Works perfectly fine, but is a bear to read. My macro is littered with these barely-intelligible If-Then statements, and it's difficult to read through it all. Is there any way to pass multiple values to the "String2" argument of Instr?

Answer 1

[On Edit: I modified the function so that if the last argument is an integer it plays the role of the compareMode parameter in Instr (0 for case-sensitive search, which is the default, and 1 for case-insensitive). As a final tweak, you could also pass a Boolean rather than an integer for the optional final argument with True corresponding to case-insensitive and False corresponding to the default case-sensitive]

If you do this sort of thing a lot it makes sense to write a function which is similar to inStr but can handle multiple patterns. ParamArray is a natural tool to use:

Function Contains(str As String, ParamArray args()) As Boolean
    Dim i As Long, n As Long, mode As Integer

    n = UBound(args)
    If TypeName(args(n)) <> "String" Then
        mode = args(n)
        mode = Abs(mode) 'So True => -1 => 1 for case-insensitive search
        n = n - 1
    End If

    For i = 0 To n
        If InStr(1, str, args(i), mode) > 0 Then
            Contains = True
            Exit Function
        End If
    Next i
    Contains = False
End Function

Tested like:

Sub Test()
    Debug.Print Contains("General Electric", "Gas", "Electric", "Oil")
    Debug.Print Contains("General electric", "Gas", "Electric", "Oil")
    Debug.Print Contains("General electric", "Gas", "Electric", "Oil", False)
    Debug.Print Contains("General electric", "Gas", "Electric", "Oil", True)
    Debug.Print Contains("General electric", "Gas", "Electric", "Oil", 1)
    Debug.Print Contains("General Motors", "Gas", "Electric", "Oil")
End Sub

Output:

True
False
False
True
True
False