Problem with message box Find

pbt

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
I have the following code which when ran gives me my message box that the "Name Not Found", even though it is there.
Code:
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
Code:
SearchFormat:=False).Activate
like this
Code:
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.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
If Not FindName Is Nothing Then 
         MsgBox Prompt:="Name Not Found"...
it findname does NOT contain NOTHING you get the messagebox

code must be
Code:
If FindName Is Nothing Then 
         MsgBox Prompt:="Name Not Found
kind regards,
Erik
 

pbt

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

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

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
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

Well-known Member
Joined
Oct 18, 2005
Messages
1,613

ADVERTISEMENT

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

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
how about;
Code:
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
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
 

Forum statistics

Threads
1,136,876
Messages
5,678,288
Members
419,753
Latest member
Sallylwy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top