Range object lacks Value property

[Raymond Langsford]

I've been fighting an intermittent problem in VBA code. I pass in a Range r
and want to look through a particular column of that range.

Dim c as Range

for each c in r.columns(col)
if c.value = target then
endif
next c

Usually this works fine. However, in certain as-yet-mysterious
circumstances, instead of c getting Range values consisting of single cells
from the column, c.Address( ) is the same as r.Address( ), and even more
perplexing to me, c.value give a Type Mismatch error, and when I put c in a
watch window, there is no Value property (although there is always a Value2
property). What is going on? How can I go about debugging this?

Thnx for any help.

Raymond Langsford

 

[Dave Peterson]

I've always had good luck if I'm more explicit.

Dim c as Range
for each c in r.columns(col).cells
if c.value = target.value then
'what happens here.
endif
next c

I'm not sure what Target is, though.

If you did:
Dim c as Range
for each c in r.columns(col)
msgbox c.address(0,0)
next c

You'll see if you're looking at the column or individual cells.

 

[Raymond Langsford]

OK... So I think I get it. Somehow another sheet is the active sheet at the
time the sheet containing the call to my VBA code is called? And that Range
on the active sheet is empty...

See how useful this newsgroup is? Typing the question helped solve it...

Raymond