Find

Consult-S

Board Regular
Joined
Jun 7, 2005
Messages
92
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Hi, try

Code:
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...
 
Upvote 0
Cheers mate! Worked like a gem.

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

Consult-S
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top