Instead of typing up a bunch of "Or" statements, how can I implement a function in this code?

Question

Sub test()

Dim DataRange As Range
Dim LastRow As Integer
Dim i As Integer
Dim SplitVal() As String
Dim OutputOffset As Long
OutputOffset = 0

LastRow = Cells(Rows.Count, "J").End(xlUp).Row

For i = 2 To LastRow
    If InStr(1, Cells(i, 10).Value, "Test1", vbTextCompare) <> 0 Or 
       InStr(1, Cells(i, 10).Value, "Test2", vbTextCompare) <> 0 Or 
       InStr(1, Cells(i, 10).Value, "Test3", vbTextCompare) <> 0 Then

      SplitVal = Split(Cells(i - 2, 10).Value, " ", 2)
      Cells(i + OutputOffset, 13).Value = SplitVal(0)
      Cells(i + OutputOffset, 14).Value = SplitVal(1)

      Cells(i + OutputOffset, 15).Value = Cells(i + 1, 10).Value
    End If
Next i


End Sub

Hey everyone. So as you can see my code goes through and checks for Test1,Test2,or Test3. Problem is I have 50+ accounts I need checking not 3!

How do I create and populate a list, make a function that replicates what I have above, and iterate the list using the function?

Thanks so much everyone!

Answer 1

That's a concern of its own; it belongs in its own scope. I use a function like this for short-circuiting otherwise redundant conditions - ParamArray is the secret sauce here:

Public Function MatchesAny(ByVal needle As String, ParamArray haystack() As Variant) As Boolean

    Dim i As Integer
    Dim found As Boolean

    For i = LBound(haystack) To UBound(haystack)
        found = (needle = CStr(haystack(i)))            
        If found Then Exit For
    Next

    MatchesAny = found

End Function

That would be used like this:

If MatchesAny(CStr(ActiveSheet.Cells(i, 10).Value), _
    "Test1", "Test2", "Test3", "Test4", "Test5", _
    "Test6", "Test7", "Test8", "Test9", "Test10", _
    "Test11", "Test12", "Test13", ..., "Test50") _
Then
    'match was found
End If

You can quite easily tweak the haystack to support passing a 1D array of values like @Jeeped's answer; the principle is the same: bail out as soon as you know your result; your current code will execute every single InStr statement, even if the first Boolean expression to be evaluated is True.

That function returns True if any item matches the specified string. Sometimes you might need a function that returns True if any item contains the specified string. That's another function:

Public Function ContainsAny(ByVal needle As String, ByVal caseSensitive As Boolean, ParamArray haystack() As Variant) As Boolean

    Dim i As Integer
    Dim found As Boolean

    For i = LBound(haystack) To UBound(haystack)
        found = Contains(needle, CStr(haystack(i)), caseSensitive)            
        If found Then Exit For
    Next

    ContainsAny = found

End Function

This one calls a simple wrapper function around InStr, which helps improve the readability of InStr() <> 0 calls:

Public Function Contains(ByVal needle As String, ByVal haystack As String, Optional ByVal caseSensitive As Boolean = False) As Boolean

    Dim compareMethod As VbCompareMethod

    If caseSensitive Then
        compareMethod = vbBinaryCompare
    Else
        compareMethod = vbTextCompare
    End If

    Contains = (InStr(1, haystack, needle, compareMethod) <> 0)

End Function

Usage of that one is similar, except we have a caseSensitive parameter that needs to be specified (you might want to tweak MatchesAny to have a similar signature) before the list of arguments. Again, same principle: bail out as soon as you know what to return.

Answer 2

Build an array of the 50 possibles to loop through. Exit the loop as soon as one is found.

Option Explicit

Sub test()

    Dim DataRange As Range
    Dim lastRow As Long
    Dim i As Integer
    Dim SplitVal() As String
    Dim OutputOffset As Long
    Dim v As Long, tests As Variant
    OutputOffset = 0

    tests = Array("Test1", "Test2", "Test3", "Test4", "Test5", "Test6", "Test7", "Test8", "Test9", _
                  "Test10", "Test11", "Test12", "Test13", "Test14", "Test15", "Test16", "Test17", "Test18", _
                  "Test19", "Test20", "Test21", "Test22", "Test23", "Test24", "Test25", "Test26", "Test27")

    With Worksheets("Sheet1")
        lastRow = .Cells(.Rows.Count, "J").End(xlUp).Row

        For i = 2 To lastRow
            For v = LBound(tests) To UBound(tests)
                If CBool(InStr(1, .Cells(i, 10).Value2, tests(v), vbTextCompare)) Then Exit For
            Next v

            If v <= UBound(tests) Then
                SplitVal = Split(.Cells(i - 2, 10).Value2, " ", 2)
                .Cells(i + OutputOffset, 13).Value = SplitVal(0)
                .Cells(i + OutputOffset, 14).Value = SplitVal(1)
                .Cells(i + OutputOffset, 15).Value2 = .Cells(i + 1, 10).Value2
            End If
        Next i
    End With

End Sub

I've added in some parent worksheet references.

Answer 3

Your 50 accounts are probably in a list which is available in your worksheet. You can create a strong of those accounts and use the instr function to find if there is a match.

    Sub test()

        Dim DataRange As Range
        Dim LastRow As Integer
        Dim i As Long
        Dim SplitVal() As String
        Dim OutputOffset As Long
        OutputOffset = 0

        Dim Spike As String
        For i = 3 To 11
            Spike = Spike & Cells(i, 1).Value & "|"
        Next i

        LastRow = Cells(Rows.Count, "J").End(xlUp).Row

        For i = 2 To LastRow
            If InStr(Spike, Cells(i, 10).Value) Then
    '        If InStr(1, Cells(i, 10).Value, "Test1", vbTextCompare) <> 0 Or
    '           InStr(1, Cells(i, 10).Value, "Test2", vbTextCompare) <> 0 Or
    '           InStr(1, Cells(i, 10).Value, "Test3", vbTextCompare) <> 0 Then

              SplitVal = Split(Cells(i - 2, 10).Value, " ", 2)
              Cells(i + OutputOffset, 13).Value = SplitVal(0)
              Cells(i + OutputOffset, 14).Value = SplitVal(1)

              Cells(i + OutputOffset, 15).Value = Cells(i + 1, 10).Value
            End If
        Next i

End Sub

In my example the list is in A3:A11 on the ActiveSheet. If that doesn't work for you, place the list on another sheet and change the above code as follows.

Dim WsList As Worksheet
Dim Spike As String
Set WsList = Worksheets("AccountList")
For i = 3 To 11
    Spike = Spike & WsList.Cells(i, 1).Value & "|"
Next i