Problem with message box Find

[pbt]

I have the following code which when ran gives me my message box that the "Name Not Found", even though it is there.

Sub SearchForName()
    Dim Name As Variant
    Dim FindName As Variant
    Name = InputBox(Prompt:="Please Enter the Name You Want to Find", _
    Title:="                          SEARCH")
    If Name = "" Then Exit Sub
    
    Set FindName = Range("B4:B65536").Find(What:=Name, After:=Range("B4"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

    If Not FindName Is Nothing Then
         MsgBox Prompt:="Name Not Found.", Title:="   SORRY"
         Exit Sub
    End If
End Sub

Using the macro recorder to do this Find, I inserted this part

SearchFormat:=False).Activate

like this

MatchCase:=False, SearchFormat:=False).Activate

This will find the name but it gives me a Type Mismatch error

What do I have wrong, please
h.h.

 

[erik.van.geit]

Erik, I did think of a list as an option, but considering that there will be over 200 names on this sheet and having to create the list "off to the side"
I just thought it would be to much maintenance.

Hi,

it's not my goal to insist or persuade, but just to be sure you are correctly informed
Let it be clear there is no maintenance whatsoever for a listbox.

perhaps you could combine both methods
if more than 1 solution would come up, when writing some characters in your inputbox, then a listbox can be displayed with all matching names

best regards,
Erik