Find Whole Cell Value, Not Part

ndendrinos · Apr 29th 2008

I use this code to "search" a value in columns A or B and if found to select the row the value is in.
Trouble is if I look for say value 11 (the code finds 1) I understand what happens but need your help to fix it hoping to keep it as simple as it is. (pls note I cannot use filters)
Thank you all.

Code

Dim wht As Variant, Found As Range
    wht = InputBox("Searchfor")
    Set Found = ActiveSheet.Columns("A:B").Find(what:=wht)
    If Found Is Nothing Then
        MsgBox "Not found"
        Exit Sub
    Else

Dave Hawley · Apr 29th 2008

Re: Find 11 Not 1

See Find Method. You HAVE to specify more settings than ONLY the value to find. In your case

Code

LookAt:=xlWhole

and others.

Fencliff · Apr 29th 2008

Re: Find 11 Not 1

If you look for value 11, the value 1 should not be found. I'm guessing you meant it the other way around?

To force an exact match, use the argument LookAt:=xlWhole :

Code

Set Found = ActiveSheet.Columns("A:B").Find(what:=wht, lookat:=xlWhole)

Edit: Whoopsie, beaten like a dog.

Dave Hawley · Apr 29th 2008

Re: Find Whole Cell Value, Not Part

Fen, that can also cause issues. From the help

Quote

Remarks
The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you don’t specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

Fencliff · Apr 29th 2008

Re: Find Whole Cell Value, Not Part

Yeah, I'm aware of that, and do a record-restore every time I use Find in one of my public-facing apps. However I didn't mention it, because I figured it might have been a little out of the scope of this discussion.

Dave Hawley · Apr 29th 2008

Re: Find Whole Cell Value, Not Part

Now I have edited the thread title to an non-selfish one (take note, ndendrinos) we can talk Find Method until the cows come home

ndendrinos · Apr 29th 2008

Re: Find Whole Cell Value, Not Part

Thank you Fencliff

Dave Hawley · Apr 29th 2008

If you wish to continue using this free service.

Please read the This Page which you agreed to when joining. In particular the point on Thread Titles. After you have done so please CLICK HERE and Private Message Dave Hawley with your acceptance, or non-acceptance of the rules. Please also include a link to this Thread. You can copy this directly from the Address bar of your Browser. When this is done your thread will be re-opened.

[fa]*[/fa]

ndendrinos · Apr 30th 2008

Re: Find Whole Cell Value, Not Part

Quote

"Yeah, I'm aware of that, and do a record-restore every time I use Find in one of my public-facing apps. "

Please elaborate this remark. I have tested the suggestion as given above and it works well. Not sure if this is relevant but the macro saves the workbook at the end of its run.
Thank you.

Fencliff · Apr 30th 2008

Re: Find Whole Cell Value, Not Part

You can restore the default settings (or actually record any settings you want to) by doing a dummy search with the values you want to "leave behind". The SearchFormat settings can be accessed through Application.FindFormat.

Unfortunately the general settings cannot be read. At one point I had a procedure that ran an elaborate set of tests that would determine what settings the user had before, but I've lost that long ago somewhere, and it's not really worth it.

ndendrinos · Apr 30th 2008

Re: Find Whole Cell Value, Not Part

Thank you Fencliff

Find Whole Cell Value, Not Part

Participate now!

Share