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.

 

[Zack Barresse]

You're getting the type mismatch error because of the ".Activate" part of that. You can't use that when you set a variable, as it then does not become an object, but rather an action and the two methods will collide resulting in an error.

HTH

 

[erik.van.geit]

Hi,

often computers do just what you ask them to do

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

that's just how you coded it

If Not FindName Is Nothing Then 
         MsgBox Prompt:="Name Not Found"...

it findname does NOT contain NOTHING you get the messagebox

code must be

If FindName Is Nothing Then 
         MsgBox Prompt:="Name Not Found

kind regards,
Erik

 

[pbt]

Erik,

I removed the "Not" in front of FindName and now nothing happens after I receive the InputBox and enter a name.

And taking firefytr's advice I do not have .Activate in my code. It is like above without the "Not"

Any idea
h.h.

 

[erik.van.geit]

Erik,

I removed the "Not" in front of FindName and now nothing happens after I receive the InputBox and enter a name.

And taking firefytr's advice I do not have .Activate in my code. It is like above without the "Not"

Any idea
h.h.

you didn't write anything to happen
what should happen ?

NOTE: consider using a userform with listbox or combobox, you won't need to type names, unless to add a new one

 

[pbt]

Maybe I need to explain a little as to what I'm trying to do.

I have a sheet with Names of people in column "B" spaced every 9th row. In between the Names I have other information for that person. This sheet will grow (and shrink) with additions and deletions of people.

I am looking for any easy way of just being able to click a button, enter the person's Name, and going to the cell where his name is for update of that persons information.

Hope this makes since.

h.h.

 

[pbt]

Sorry Erik, didnt't read (or understand)

you didn't write anything to happen

That is where I'm Lost. Need some help there, please.

Thanks
h.h.

 

[agihcam]

how about;

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
    With ActiveSheet.Range("b4:b65536")
    Set FindName = .Find(Name, , , xlWhole)
    If Not FindName Is Nothing Then
         FindName.Select
         Else
         MsgBox Prompt:="Name Not Found.", Title:="   SORRY"
         Exit Sub
    End If
    End With
End Sub

 

[erik.van.geit]

thanks for the assist, agihcam

I would advise again to produce a listbox instead of needing to type in names
this list would show you all names in alphabetical order... interested ?

greetings,
Erik

 

[pbt]

Thanks agihcam

That worked just fine.

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.

By changing "xlWhole" to "xlPart" it will only take a few key strokes from the user.

Again, thank you both for your input.
Harry