rng(1,1) syntax doesn't work for set rng = arange.columns(1)
 

Not sure this is a totally satisfactory explanation, but here's my
best guess. Look at

? Range("B2:B5").Cells.Count, Range("B2:B5").Columns(1).Count
4 1

When you reference the column property, it returns a unit rather
than a range of cells.

Since the

rng(1, 1)

syntax is shorthand for

rng.item(rowindex, columnindex)

syntax, my best guess is that specifying a rowindex is ambiguous and
therefore returns the error.

In article ,
"Tim Zych" wrote:

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

rng(1,1) syntax doesn't work for set rng = arange.columns(1)
 

Since the

rng(1, 1)

syntax is shorthand for

rng.item(rowindex, columnindex)

That's exactly my understanding too. I posted another
sample in response to Alan. What am I missing?


-----Original Message-----
Not sure this is a totally satisfactory explanation, but
here's my
best guess. Look at

? Range("B2:B5").Cells.Count, Range("B2:B5").Columns
(1).Count
4 1

When you reference the column property, it returns a unit
rather
than a range of cells.

Since the

rng(1, 1)

syntax is shorthand for

rng.item(rowindex, columnindex)

syntax, my best guess is that specifying a rowindex is
ambiguous and
therefore returns the error.

In article ,
"Tim Zych" wrote:

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
.

rng(1,1) syntax doesn't work for set rng = arange.columns(1)
 

What you're missing is that Range("B2:X5").Column(1) is not the same as
Range("B2:X5).

Range("whatever") and Range("whatever").Areas(n) are Range Objects that
are also Collection Objects; in this case collections of cells.
Range("whatever").Columns and Range("whatever").Rows are also Range
Objects that are Collection Objects; but in this case collections of
columns and rows, respectively.

Thus the Item Property, which refers to the items in a collection, after
Set rng = Range("whatever").Columns(1) is looking for a single index
number; e.g., in rng(3) the reference is to the 3rd item in the
collection of columns, i.e., the 3rd column of the range (and since
range indices refer to cells, columns, etc. outside the stated
range--e.g., after Set rng = Range("A1"), rng(2,2) is meaningful to
refer to Cell B2 even though it's outside of rng--rng(3) in the above
case refers to the 3rd column even though that is outside the one-column
range).

In short (somewhat belatedly), after
Set rng = Range("B2:X5").Columns(1), rng(1,1), which is indeed shorthand
for rng.Item(1,1) is failing because the Item Property applied to a
collection of columns is looking for a single index number, the number
of the column.

Alan Beban

Tim Zych wrote:
Since the

rng(1, 1)

syntax is shorthand for

rng.item(rowindex, columnindex)


That's exactly my understanding too. I posted another
sample in response to Alan. What am I missing?



-----Original Message-----
Not sure this is a totally satisfactory explanation, but

here's my

best guess. Look at

? Range("B2:B5").Cells.Count, Range("B2:B5").Columns

(1).Count

4 1

When you reference the column property, it returns a unit

rather

than a range of cells.

Since the

rng(1, 1)

syntax is shorthand for

rng.item(rowindex, columnindex)

syntax, my best guess is that specifying a rowindex is

ambiguous and

therefore returns the error.

In article ,
"Tim Zych" wrote:


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

.