match entire string

[daviddoria]

I am trying to use cells.find to find a cell containing "Date". However, it also finds cells with "Start Date" and "End Date" and any other instance of the word date. Is there anyway to make it match ONLY IF the cell contains EXACTLY "Date" and nothing else?

Thanks,
Dave

[Leith Ross]

Hello Dave,

The Find method of a range object has a argument that controls whether the search looks for a partial match or complete match - LookAt. The constants are xlPart and xlWhole. Also, there is an argument to control if the search is case sensitive - MatchCase. Set this to true if the search is to be case sensitive.

Example - Whole word only, not case sensitive

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[daviddoria]

great thanks!

[TedH]

I'm working on something like daviddoria's problem, only I want to use the result to trigger an If..Then to run or be bypassed.

I plugged the code Leith Ross posted into my file top see what the output is, and it returned "DATE" into a random cell I assigned it to.

I adjusted the code to address my situation. Whne I run the macro on a sheet with "Rev 2" (which doesn't need to go thru the routine), I'm getting Error 91("Object variable or With block variable not set "). When I run a sheet with "Rev 1", it runs without a hitch.

Please Login or Register  to view this content.

[Leith Ross]

Hello TedH,

Please start a new thread.


Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

Sincerely,
Leith Ross