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
?
[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
There is no way to pass multiple parameters to InStr, but you could create your own sub that handles it. I would create a sub that takes the string to look in and an array of strings to search for. It could then loop over the array and call InStr for each string. Have it return a final result of whether it found all of them or not, and then replace all of your ugly conditional statements with one call to your sub.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With