Find

[Consult-S]

Hi All,

I'm trying to find the word supplier in a range named HeaderRange1 as below. I want to use this as a way of stopping the macro proceeding further if it can't find the word "supplier" using the on error goto method.

HeaderRange1.Find(what:="Supplier", LookIn:=xlValues).Activate

However, it seems to be finding headings such as "Supplier Rate" and therefore reporting no error when the word "supplier" is missing. How can I modify the code above to just look strictly for the word "Supplier" and not "Supplier Rate"?

Thanks

Consult-S

 

[DonkeyOte]

Hi, try

Dim x As Long
x = HeaderRange1.Find(what:="Supplier", LookIn:=xlValues, LookAt:=xlWhole).Row

x would return row of complete match or bug out if no match as you wanted.

I thought xlWhole would be default (was on mine) but maybe not yours for some reason...

 

[Consult-S]

Cheers mate! Worked like a gem.

Mmmm?? The whole never came out as default on mine for some reason

Consult-S

 

[DonkeyOte]

I don't use Find a whole lot so I tried to recreate your issue manually and I found that when I used find without specifying "LookAt" (as you had not in your sample code) my routine automatically did a Whole rather than Part search so presumed XL used Whole by default unless otherwise specified... obviously not... glad it worked.

 

[VoG]

I may be wrong but I believe that for arguments that are not specified in the code, Excel uses the current setting in the find dialog box.

 

[DonkeyOte]

ah thanks for clarification -- I did test manually using Entire Contents so that would explain that... this place is getting worse than googling... there's pretty much an immediate answer out there for anything!