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)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
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) | |||
rng(1,1) syntax doesn't work for set rng = arange.columns(1) | Excel Programming |