Tim Zych wrote:
I guess this is the result of the column object that everybody is
talking about (what is a column object)?

I think that only Jake Marx and Tom Ogilvy referred to a column object.
It's tempting to think in those terms, because VBA performs (except for
the return from the Typename Function) as though there is a Column
Object and a Row Object. Microsoft insists that those are Range
Objects, but I think that the world turns the same if you disbelieve
Microsoft and insist that there *is* a Row Object and Column Object.
Except for the Typename Function and scads of Microsoft documentation,
it's not clear to me how one could demonstrate that there is not a Row
Object nor a Column Object.

' This is how I thought it would (think it should) work

For Each v In Intersect(rng, rng.Columns(1))
Debug.Print v.Address
Next v

- $A$1
$A$2

It does. Intersect(rng, rng.Columns(1)) does not return a Range Object
that is a collection of Columns, but a Range Object that is a collection
of cells, as you can see from
MsgBox Intersect(rng, rng.Columns(1)).Count; or, for that matter, by
running the code that describes how you thought it would work.

Alan Beban

I suppose this is one of those things that I will just have to accept. I
would think that a column is a subset of a range, and is itself a range
object much like Areas. Typename(Range("A1:D5").Columns(1)) returns "Range"

However, another test seems to confirm what everyone here has said. Using
For..Each, what does Excel think is going on...

Dim v As Variant, rng As Range
Set rng = Range("A1:B2")

'--------------------------------
For Each v In rng.Columns(1)
Debug.Print v.Address
Next v

- $A$1:$A$2

I guess this is the result of the column object that everybody is
talking about (what is a column object)?
So Cells is not the default using for..each, like it is in other range
references. For Each v in rng.Columns(1).Cells does "fix" it.

'--------------------------------

' This is how I thought it would (think it should) work

For Each v In Intersect(rng, rng.Columns(1))
Debug.Print v.Address
Next v

- $A$1
$A$2


Thanks for everybody's input.

Tim Zych



"Tom Ogilvy" wrote in message
...

You have a column object:

? Range("B2:X5").Columns(1).count
1

See, the count is 1

Adding cells drills into the cells within the column object:
? Range("B2:X5").Columns(1).cells.count
4

so adding cells refers to B2:B5 as individual cells while the original
refers to the 1st column in B2:X5.

--
Regards,
Tom Ogilvy


"Tim Zych" wrote in message
...

Anybody know why this doesn't work?

Dim rng As Range
Set rng = Range("B2:X5").Columns(1)
Debug.Print rng.Address '<- this works
Debug.Print rng(1, 1).Address '<-this doesn't

Yes, rng.cells(1,1) does work. What's up with this?

XL2000, Win2000