Option Explicit 
 
Function instrArray(strArray, strWanted, _ 
    Optional CaseCrit As Boolean = False, _ 
    Optional FirstOnly As Boolean = True, _ 
    Optional Location As String = "exact") As Long 
     '
     '****************************************************************************************
     '       Title       instrArray
     '       Target Application:  any
     '       Function:   searches string array for some "wanted" text
     '       Limitations:
     '       Passed Values:
     '           strArray    [in, string array]  array to be searched
     '           strWanted   [in, string]  text for which strArray is searched
     '           CaseCrit    [in, Boolean, Optional]
     '               if true, case (upper/lower) of each character is critical and must match
     '               if false, case is not critical {default}
     '           FirstOnly   [in, Boolean, Optional]
     '               if true, proc exits after first instance is found {default}
     '               if false, proc search to end of array and last instance # is returned
     '           Location    [in, string, Optional] text matching constraint:
     '               = "any"     as long as strWanted is found anywhere in strArray(k),i.e.,
     '                               instr(strArray(k),strWanted) > 0, then instrArray = K
     '               = "left"    match is successful only if
     '                               Left(strArray(K),Len(strWanted) = StrWanted
     '               = "right"    match is successful only if
     '                               Right(strArray(K),Len(strWanted) = StrWanted
     '               = "exact"    match is successful only if
     '                               strArray(K) = StrWanted       {default}
     '
     '****************************************************************************************
     '
     '
    Dim I       As Long 
    Dim Locn    As String 
    Dim strA    As String 
    Dim strB    As String 
     
    instrArray = 0 
    Locn = LCase(Location) 
    Select Case FirstOnly 
    Case Is = True 
        For I = LBound(strArray) To UBound(strArray) 
            Select Case CaseCrit 
            Case Is = True 
                strA = strArray(I):     strB = strWanted 
            Case Is = False 
                strA = LCase(strArray(I)):  strB = LCase(strWanted) 
            End Select 
            If instrArray2(Locn, strA, strB) > 0 Then 
                instrArray = I 
                Exit Function 
            End If 
        Next I 
    Case Is = False 
        For I = UBound(strArray) To LBound(strArray) Step -1 
            Select Case CaseCrit 
            Case Is = True 
                strA = strArray(I):     strB = strWanted 
            Case Is = False 
                strA = LCase(strArray(I)):  strB = LCase(strWanted) 
            End Select 
            If instrArray2(Locn, strA, strB) > 0 Then 
                instrArray = I 
                Exit Function 
            End If 
        Next I 
    End Select 
     
End Function 
 
Function instrArray2(Locn, strA, strB) 
     '
     '****************************************************************************************
     '       Title       instrArray2
     '       Target Application:  any
     '       Function    called by instrArray to complete test of strB in strA
     '       Limitations:    NONE
     '       Passed Values:
     '           Locn    [input, string] text matching constraint (see instrArray)
     '           strA    [input, string] 1st character string
     '           strB    [input, string] 2nd character string
     '
     '****************************************************************************************
     '
     '
     
    Select Case Locn 
    Case Is = "any" 
        instrArray2 = InStr(strA, strB) 
    Case Is = "left" 
        If Left(strA, Len(strB)) = strB Then instrArray2 = 1 
    Case Is = "right" 
        If Right(strA, Len(strB)) = strB Then instrArray2 = 1 
    Case Is = "exact" 
        If strA = strB Then instrArray2 = 1 
    Case Else 
    End Select 
     
End Function 
Sub instrArray_Test() 
     '
     '****************************************************************************************
     '
     '       Function:   demonstrates use of function instrArray
     '
     '****************************************************************************************
     '
     '
    Dim CaseCrit    As Boolean 
    Dim FirstOnly   As Boolean 
    Dim Location    As String 
    Dim N           As Integer 
    Dim Rtn         As Long 
    Dim strArray()  As String 
    Dim strToFind   As String 
     
    CaseCrit = Cells(4, 9) 
    FirstOnly = Cells(5, 9) 
    Location = Cells(7, 9) 
    N = 1 
FindNextWord: 
    N = N + 1 
    If Cells(N, 1) = "" Then 
        N = N - 1 
        GoTo GetStrToFind 
    Else 
        ReDim Preserve strArray(1 To N - 1) 
        strArray(N - 1) = Cells(N, 1) 
    End If 
    GoTo FindNextWord 
     
GetStrToFind: 
    strToFind = InputBox("text to find?", "Demo of instrArray") 
    If strToFind = "" Then Exit Sub 
    Rtn = instrArray(strArray, strToFind, CaseCrit, FirstOnly, Location) 
    Select Case Rtn 
    Case Is = 0 
        MsgBox "text to find = " & strToFind & vbCrLf & vbCrLf & _ 
        "return from instrArray = " & Rtn & vbCrLf & vbCrLf & _ 
        "[ this means that the text was not found in the array" & vbCrLf & _ 
        "  as constrained case matching and location matching ]" 
    Case Else 
        MsgBox "text to find = " & strToFind & vbCrLf & vbCrLf & _ 
        "return from instrArray = " & Rtn & vbCrLf & vbCrLf & _ 
        "[ this corresponds to row  " & (Rtn + 1) & "  ]" 
    End Select 
     
    GoTo GetStrToFind 
     
End Sub