"Run-time error '424': Object Required"

Question

I want a function to take two parameters, a searching cell and a cell to search in, where the searching cell contains a comma separated list. I want each element to be tested to see if it is in the 'search in' cell. For example:

List_of_String: word1, word2, word3  
Where_to_Search: bbbbbword1 'Return TRUE'  
Where_to_Search: bbbbhhhhhmmmmm 'Return FALSE

I have written:

Function Search_in_String(List_of_Strings_to_Search As String, Where_to_Search As String)

String_array = Split(List_of_Strings_to_Search, ",")
Dim is_in_num As Integer
Dim is_in As Boolean

is_in = False

For i = 0 To (UBound(String_array) - 1)
    is_in_num = InStr(Where_to_Search, Trim(String_array(i)))
    If is_in_num > 0 Then is_in = True
    If is_in Then Exit For
Next i

Search_in_String = is_in
End Function



Sub test()
    MsgBox Search_in_String(BJ7.Value, Bk7.Value)
End Sub

The test() function I'm using to test my Search_in_String function (which I believe is also not working). When I step in (F8) I get the error:

Run-Time error '424': Object required

Answer 1

Change the sub test() for this:

Sub test()
    MsgBox Search_in_String(Range("BJ7").Value, Range("BJ7").Value)
End Sub