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

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.
-----Original Message-----
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



.

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.
-----Original Message-----
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, thanks, that helps a lot.

Ken


"Geof Wyght" wrote in message
...
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.
-----Original Message-----
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



.

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...nd_method.html

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

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

"Dick Kusleika" wrote in message
...
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...nd_method.html

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

No.

--
Regards,
Tom Ogilvy

"Ken Loomis" wrote in message
...
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

"Dick Kusleika" wrote in message
...
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...nd_method.html

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com