vba - 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

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.