Option Explicit 
 
Sub xlInArray() 
     '
     '****************************************************************************************
     '       Title       xlInArray
     '       Target Application:  any
     '       Function:   determines if user-specified X is in the current selection
     '       Limitations:    none
     '       Passed Values:  none
     '
     '****************************************************************************************
     '
     '
    Dim A() 
    Dim I           As Long 
    Dim ProcTitle   As String 
    Dim Prompt      As String 
    Dim strX        As String 
    Dim X 
    Dim xlCell      As Range 
     
     
    ProcTitle = "xlInArray" 
    Prompt = "enter value to be tested against values in selection." & vbCrLf & vbCrLf & _ 
    "click on CANCEL to exit procedure" 
     
    ReDim A(1 To Selection.Cells.Count) 
    I = 0 
    For Each xlCell In Selection 
        If IsNumeric(xlCell) = False Then 
            MsgBox "ERROR:  one or more values in selection" & vbCrLf & _ 
            "are not numeric.  Make a new selection" & vbCrLf & _ 
            "and start again.", vbCritical + vbOKOnly, ProcTitle 
            Exit Sub 
        End If 
        I = I + 1 
        A(I) = xlCell 
    Next xlCell 
    Selection.Cells.Interior.ColorIndex = 35 
GetX: 
    strX = InputBox(Prompt, ProcTitle) 
    If strX = "" Then Goto CleanUp 
    If IsNumeric(strX) = False Then 
        If Left(Prompt, 5) <> "ERROR" Then _ 
        Prompt = "ERROR:  data entered is not numeric" & vbCrLf & vbCrLf & Prompt 
        Goto GetX 
    End If 
    X = strX 
    I = InArray(X, A) 
    Select Case I 
    Case Is = 0 
        MsgBox X & " was not found in selection.", vbInformation, ProcTitle 
    Case Is > 0 
        MsgBox X & " was found in selection at index " & I, vbInformation, ProcTitle 
    End Select 
    Goto GetX 
     
CleanUp: 
    Set xlCell = Nothing 
    Selection.Cells.Interior.ColorIndex = 0 
     
End Sub 
 
Function InArray(X, A) As Long 
     '
     '****************************************************************************************
     '       Title       InArray
     '       Target Application:  any
     '       Function:   determines if X is in the array A
     '           if X is in A, then on return InArray = I where X = A(I)
     '           if X is not in A, on return InArray = 0
     '       Limitations:    X and A must be of numeric type
     '       Passed Values:
     '           X   [in, numeric]
     '           A   [in, numeric array]
     '
     '****************************************************************************************
     '
     '
    Dim I       As Long 
    Dim dblX    As Double 
     
    dblX = CDbl(X) 
    For I = LBound(A) To UBound(A) 
        If dblX = CDbl(A(I)) Then 
            InArray = I 
            Exit Function 
        End If 
    Next I 
    InArray = 0 
     
End Function