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

Tom; as uaual, the most succint explanation!

Regards,

Vasant.

"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

A column object?
Heretic :-)

Alan Beban

Tom Ogilvy wrote:
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.

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

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

Tim Zych wrote:

So Cells is not the default using for..each, like it is in other
range references.

I think two imprecise erroneous notions, which may be widespread,
contribute to Tim Zych's difficulty in accepting the treatment of
Range("whatever").Columns(1). One is reproduced above. Cells is *not*
the default using for..each in other range references; Item is the
default. Erroneously accepting that Cells is the default certainly makes
understanding difficult.

The other erroneous notion is that after Set rng = Range("whatever"),
rng.Item(1) [or its shorthand, rng(1)] refers to the first cell of rng.
That is not universally true, wasn't when Tim first encountered it,
and isn't now. Whether it's true in particular circumstances depends on
what "whatever" is. If Range("whatever") is a Range Object that is a
collection of cells, it is true; but if Range("whatever") is a Range
Object that is a collection of rows or columns, it is not true--never was.

Whichever.Item(n) uniformly refers to the nth member of the Whichever
collection; if Whichever is a collection of cells [and perhaps in other
cases that don't come readily to mind--but *not* collections of rows or
columns], Item(m,n) is permitted, because cells can be referred to by
either a single index that is their position in the range, or row and
column indexes referring to their row and column positions in the range.

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