Hide and seek
 

Hi all,
XL2003
I am using the following lines in part of a proceedu

With Sheets(A).Range("D9:D1000")
Set C = .Find(B, LookIn:=xlValues)
If Not C Is Nothing Then
(More code here...)

This works as expected, until I hide column D on Sheets(A). (ie: when D:D is
hidden, C is Nothing). I would really like to keep this column hidden. Is
there a reasonably tidy solution, or does the 'Find' function intentionally
overlook hidden cells.
Thanks in advance.
Regards - Dave.

Hide and seek
 

It's better to specify all the parms to the .find method. If you don't, you'll
be inheriting the settings from the last .find -- either in code or manually by
the user.

Then try looking in xlformulas.

Dave wrote:

Hi all,
XL2003
I am using the following lines in part of a proceedu

With Sheets(A).Range("D9:D1000")
Set C = .Find(B, LookIn:=xlValues)
If Not C Is Nothing Then
(More code here...)

This works as expected, until I hide column D on Sheets(A). (ie: when D:D is
hidden, C is Nothing). I would really like to keep this column hidden. Is
there a reasonably tidy solution, or does the 'Find' function intentionally
overlook hidden cells.
Thanks in advance.
Regards - Dave.

--

Dave Peterson

Hide and seek
 

Hi Dave,
Thanks for that. Works a treat now. I got the 'xlValues' from the
Find-example in the VBA help.
Can you tell me why 'xlFormulas' works, since the number I'm Finding isn't a
formula, or contained within a formula?
And also why it works on hidden cells when 'xlValues' doesn't?
And how do you know these things?
And yes, I did ask a lot of questions when I was a kid.
Regards - Dave.

"Dave Peterson" wrote:

It's better to specify all the parms to the .find method. If you don't, you'll
be inheriting the settings from the last .find -- either in code or manually by
the user.

Then try looking in xlformulas.

Dave wrote:

Hi all,
XL2003
I am using the following lines in part of a proceedu

With Sheets(A).Range("D9:D1000")
Set C = .Find(B, LookIn:=xlValues)
If Not C Is Nothing Then
(More code here...)

This works as expected, until I hide column D on Sheets(A). (ie: when D:D is
hidden, C is Nothing). I would really like to keep this column hidden. Is
there a reasonably tidy solution, or does the 'Find' function intentionally
overlook hidden cells.
Thanks in advance.
Regards - Dave.

--

Dave Peterson

Hide and seek
 

I have no idea why there's a difference.

And I either learned it here in the newsgroups or by trial and error and had a
eureka moment.

Dave wrote:

Hi Dave,
Thanks for that. Works a treat now. I got the 'xlValues' from the
Find-example in the VBA help.
Can you tell me why 'xlFormulas' works, since the number I'm Finding isn't a
formula, or contained within a formula?
And also why it works on hidden cells when 'xlValues' doesn't?
And how do you know these things?
And yes, I did ask a lot of questions when I was a kid.
Regards - Dave.

"Dave Peterson" wrote:

It's better to specify all the parms to the .find method. If you don't, you'll
be inheriting the settings from the last .find -- either in code or manually by
the user.

Then try looking in xlformulas.

Dave wrote:

Hi all,
XL2003
I am using the following lines in part of a proceedu

With Sheets(A).Range("D9:D1000")
Set C = .Find(B, LookIn:=xlValues)
If Not C Is Nothing Then
(More code here...)

This works as expected, until I hide column D on Sheets(A). (ie: when D:D is
hidden, C is Nothing). I would really like to keep this column hidden. Is
there a reasonably tidy solution, or does the 'Find' function intentionally
overlook hidden cells.
Thanks in advance.
Regards - Dave.

--

Dave Peterson


--

Dave Peterson

Hide and seek
 

Ok, eureka moment. That's cool...
Thanks for replying
Dave.