Setting the Options of the Find method

[Ken Loomis]

I use this statement to create a range of cells from the 'InColumn' column

Set rFound = Columns(InColumn).Find(findIt)

Things were working fine, then all of a sudden, it stopped finding anything
regardless of what I set 'findIt' equal to.

It was driving me crazy and I finally tried to record a whole macro and
start over. Problem was I couldn't record a 'Find' macro.

But in the process of trying, I looked at the options for the find and saw
that the "Match entire cell contents" was selected. I turned that off and,
since findIt will only be found as a substring, when went back and ran the
replace macro, it magically worked again. Jeez, this lesson took hours.

So this is how I am trying to think of this VBA stuff, and please help me
out here,

"Find" is a method(?) that belongs to a range object(?)

the Find method has parameters(?)/setting(?)/options(?)

If those parameters (or whatever they are called) are already set, the
method uses whatever those are set to

So, here are my questions:

1) How do I access those settings values? Seems polite to store these before
simply changing them.

2) How do I change them? So I can clean things up after I am done, like I
was taught in Kindergarten ;-)

3) Is there a way to use the Find method and just specify my desired
settings, without changing them?

Thanks for any help and insights.

Ken Loomis

 

[Geof Wyght]

Ken,
I'll start to answer a few basic questions. If you're in
the Excel VBE, hit the F2 button which exposes the Object
Browser window. On the left hand pane enter "r" to get
down the list and locate "range". Over on the right hand
pane you'll see a list of properties and methods of the
range object. The green bricks are methods. The hand with
the page are properties.

Even though I've been programming in Excel since 1997, I
still rely on the Object Browser a lot.
Geof.

 

[Geof Wyght]

Ken,
The other two things I rely on a lot is Help and
Intellisense. Click anywhere in the word "Find" on your
line of code and hit the F1 key. It should show all of the
arguments and all of the values that each argument can
take. The Intellisense helps as you're filling in the
arguments (in Excel 97 and up, I believe).
Geof.

 

[Ken Loomis]

Geof, thanks, that helps a lot.

Ken

Ken,
The other two things I rely on a lot is Help and
Intellisense. Click anywhere in the word "Find" on your
line of code and hit the F1 key. It should show all of the
arguments and all of the values that each argument can
take. The Intellisense helps as you're filling in the
arguments (in Excel 97 and up, I believe).
Geof.

 

[Dick Kusleika]

Ken

As you discovered, the Find method will use the last used setting for any
arguments that you omit. The last used setting is set in both VBA and the
user-interface.

"Find" is a method(?) that belongs to a range object(?)

the Find method has parameters(?)/setting(?)/options(?)

If those parameters (or whatever they are called) are already set, the
method uses whatever those are set to

So, here are my questions:

1) How do I access those settings values? Seems polite to store these before
simply changing them.

I believe the only way to change those is the through the arguments of the
Find method and through the user interface. That seems to imply that you
can't get/change those settings anywhere else.

2) How do I change them? So I can clean things up after I am done, like I
was taught in Kindergarten ;-)

3) Is there a way to use the Find method and just specify my desired
settings, without changing them?

No. The best course, in my opinion, is to explicitly set every argument
when using the Find method every time. Since these settings are easily
viewed in the UI, don't worry that the user will have to change them. It's
most likely that the user won't know what they were before. That may not
pass the Kindergarten test, but I think it's the best option.

See also http://www.dicks-blog.com/excel/2004/03/the_find_method.html

 

[Ken Loomis]

Thanks for that confirmation.

If the anyway to store the settings for the Find method so I can set them
back to what they were after I am with the find?

Ken Loomis

 

[Tom Ogilvy]

No.

--
Regards,
Tom Ogilvy

Thanks for that confirmation.

If the anyway to store the settings for the Find method so I can set them
back to what they were after I am with the find?

Ken Loomis