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

In your code, the indices in rng(1,1) are trying to refer the Column
number, and there is no Column numbered 1,1.

To see how the indices work, code

Set rng = Range("B2:X5").Columns
Debug.Print rng(3).Address

You will see that the index to the rng variable is referring not to the
3rd cell of anything, but to the third Column, $C$2:$C$5.

And since this syntax is iterative, rng(3)(2) would refer to the 4th
Column; i.e., the 2nd column starting from rng(3), $D$2:$D$5.

Alan Beban

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)
 

Doesn't rng(1,1) refers to the top left cell of rng?

The syntax works ok for every instance that I set a range,
except when I set a range using the columns property.

Dim rng1 As Range, rng2 As Range

Set rng1 = Range("B1:B3")
Set rng2 = Range("B1:D3").Columns(1)

Debug.Print rng1.Address
Debug.Print rng2.Address

Debug.Print rng1.Address = rng2.Address

Debug.Print rng1(1, 1).Address '<- OK
Debug.Print rng2(1, 1).Address '<- error



-----Original Message-----
In your code, the indices in rng(1,1) are trying to refer
the Column
number, and there is no Column numbered 1,1.

I don't think so. They refer to the first row, first
column of rng. Rng(R,C) refers to the Rth row and Cth
column of that range.

To see how the indices work, code

Set rng = Range("B2:X5").Columns
Debug.Print rng(3).Address

You will see that the index to the rng variable is
referring not to the
3rd cell of anything, but to the third Column, $C$2:$C$5.

And since this syntax is iterative, rng(3)(2) would refer
to the 4th
Column; i.e., the 2nd column starting from rng(3),
$D$2:$D$5.

Alan Beban

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)
 

Hi Tim,

I think Alan is correct on this. The Columns property returns a collection
(maybe I should say array) of Columns, not a true Range (which is a
collection of Cells). So if you do this:

Set rng2 = Range("B1:D3").Columns(1)

The object returned by the reference is actually a "Column" object that
holds the Cells B1, B2, and B3. As Alan noted, the shortcut that many use
to refer to ranges is actually getting a member of that collection. Since
the collection in this case holds Columns, you can't specify (1,1) because
there is no second dimension. The syntax .Cells(1,1) works fine because you
are retrieving a specific cell in the collection of Cells contained by the
Column object reference.

If you add .Cells to the end of the statement above, it will work as
expected. That is because you'll have a reference to a collection of Cells
instead of a Column that contains a collection of Cells.

That probably didn't come out right, but hopefully you catch my drift. <g

--
Regards,

Jake Marx
www.longhead.com


Tim Zych wrote:
Doesn't rng(1,1) refers to the top left cell of rng?

The syntax works ok for every instance that I set a range,
except when I set a range using the columns property.

Dim rng1 As Range, rng2 As Range

Set rng1 = Range("B1:B3")
Set rng2 = Range("B1:D3").Columns(1)

Debug.Print rng1.Address
Debug.Print rng2.Address

Debug.Print rng1.Address = rng2.Address

Debug.Print rng1(1, 1).Address '<- OK
Debug.Print rng2(1, 1).Address '<- error



-----Original Message-----
In your code, the indices in rng(1,1) are trying to refer the Column
number, and there is no Column numbered 1,1.

I don't think so. They refer to the first row, first
column of rng. Rng(R,C) refers to the Rth row and Cth
column of that range.

To see how the indices work, code

Set rng = Range("B2:X5").Columns
Debug.Print rng(3).Address

You will see that the index to the rng variable is referring not to
the 3rd cell of anything, but to the third Column, $C$2:$C$5.

And since this syntax is iterative, rng(3)(2) would refer to the 4th
Column; i.e., the 2nd column starting from rng(3), $D$2:$D$5.

Alan Beban

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

.