Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Macro how to create email link for the email addresses in aRange or Selection | Excel Worksheet Functions | |||
Arange sorted data in multiple columns | Excel Discussion (Misc queries) | |||
Help to adapt Formula syntax to work with Visible Filtered Cells | Excel Worksheet Functions | |||
Help to adapt Formula syntax to work with Dynamic Named Ranges | Excel Worksheet Functions | |||
arange dates | Excel Discussion (Misc queries) |