Strange Range.item behavior?

[Jeremy Gollehon]

Can someone explain this to me?

From the immediate window:
?Range("A1").Item(3,1).address
$A$3
?Range("A1").Item(3).address
$A$3
?Range(Range("A1"), Range("G10")).Item(3,1).address
$A$3
?Range(Range("A1"), Range("G10")).Item(3).address
$C$1 <----!!!???

The last one there is where my confusion lies.

I'm sorting a range with the code below(rough example):
---------------------------------------------------------------------------------------------
Set rDataRng = Range(Range("A1"), Range("G10"))
With rDataRng
.Value = .Value
.Sort Key1:=.Item(2, 1), Order1:=xlDescending, OrderCustom:=1, _
Orientation:=xlLeftToRight
End With
---------------------------------------------------------------------------------------------

I initially had .Item(2) in there as the Key1 entry, which caused me to
stumble upon this (what I see as) strange behavior.
Is this a bug or one of those "by design" features?

Thanks for any input.
-Jeremy

[Jim Thomlinson]

You are only referencing one dimension of a two dimension array. Arguments
are read right to left (backwards from what you would think) so you are
referencing the columns, not the rows... As a general rule you should alway
reference bot dimensions of the array to avoid any confusion... because it
certainly can be confusing...

HTH

"Jeremy Gollehon" wrote:

> Can someone explain this to me?
>
> From the immediate window:
> ?Range("A1").Item(3,1).address
> $A$3
> ?Range("A1").Item(3).address
> $A$3
> ?Range(Range("A1"), Range("G10")).Item(3,1).address
> $A$3
> ?Range(Range("A1"), Range("G10")).Item(3).address
> $C$1 <----!!!???
>
> The last one there is where my confusion lies.
>
> I'm sorting a range with the code below(rough example):
> ---------------------------------------------------------------------------------------------
> Set rDataRng = Range(Range("A1"), Range("G10"))
> With rDataRng
> .Value = .Value
> .Sort Key1:=.Item(2, 1), Order1:=xlDescending, OrderCustom:=1, _
> Orientation:=xlLeftToRight
> End With
> ---------------------------------------------------------------------------------------------
>
> I initially had .Item(2) in there as the Key1 entry, which caused me to
> stumble upon this (what I see as) strange behavior.
> Is this a bug or one of those "by design" features?
>
> Thanks for any input.
> -Jeremy
>
>
>

[Bernie Deitrick]

Jim, and Jeremy,

The arguments are not read right to left, and singel indices are not
referring to rows or columns. (They just seem to sometimes, when working
with single column or single row ranges). Excel steps through rows first
when simply indexing a range.

MsgBox Range("A1:Z10000").Item(100000).Address

works, because the 100000 doesn't refer to a column or a row, but rather the
100000th member when stepping through rowwise.

HTH,
Bernie
MS Excel MVP


"Jim Thomlinson" <JimThomlinson@discussions.microsoft.com> wrote in message
news:593AAB31-14B0-43AB-99C7-9D13A2CF13E9@microsoft.com...
> You are only referencing one dimension of a two dimension array. Arguments
> are read right to left (backwards from what you would think) so you are
> referencing the columns, not the rows... As a general rule you should
alway
> reference bot dimensions of the array to avoid any confusion... because it
> certainly can be confusing...
>
> HTH
>
> "Jeremy Gollehon" wrote:
>
> > Can someone explain this to me?
> >
> > From the immediate window:
> > ?Range("A1").Item(3,1).address
> > $A$3
> > ?Range("A1").Item(3).address
> > $A$3
> > ?Range(Range("A1"), Range("G10")).Item(3,1).address
> > $A$3
> > ?Range(Range("A1"), Range("G10")).Item(3).address
> > $C$1 <----!!!???
> >
> > The last one there is where my confusion lies.
> >
> > I'm sorting a range with the code below(rough example):
>
> --------------------------------------------------------------------------
-------------------
> > Set rDataRng = Range(Range("A1"), Range("G10"))
> > With rDataRng
> > .Value = .Value
> > .Sort Key1:=.Item(2, 1), Order1:=xlDescending, OrderCustom:=1, _
> > Orientation:=xlLeftToRight
> > End With
>
> --------------------------------------------------------------------------
-------------------
> >
> > I initially had .Item(2) in there as the Key1 entry, which caused me to
> > stumble upon this (what I see as) strange behavior.
> > Is this a bug or one of those "by design" features?
> >
> > Thanks for any input.
> > -Jeremy
> >
> >
> >

[Tom Ogilvy]

Not exactly

? Range(Range("A1"), Range("G10")).Item(8).address
$A$2


--
Regards,
Tom Ogilvy

"Jim Thomlinson" <JimThomlinson@discussions.microsoft.com> wrote in message
news:593AAB31-14B0-43AB-99C7-9D13A2CF13E9@microsoft.com...
> You are only referencing one dimension of a two dimension array. Arguments
> are read right to left (backwards from what you would think) so you are
> referencing the columns, not the rows... As a general rule you should
alway
> reference bot dimensions of the array to avoid any confusion... because it
> certainly can be confusing...
>
> HTH
>
> "Jeremy Gollehon" wrote:
>
> > Can someone explain this to me?
> >
> > From the immediate window:
> > ?Range("A1").Item(3,1).address
> > $A$3
> > ?Range("A1").Item(3).address
> > $A$3
> > ?Range(Range("A1"), Range("G10")).Item(3,1).address
> > $A$3
> > ?Range(Range("A1"), Range("G10")).Item(3).address
> > $C$1 <----!!!???
> >
> > The last one there is where my confusion lies.
> >
> > I'm sorting a range with the code below(rough example):
>
> --------------------------------------------------------------------------
-------------------
> > Set rDataRng = Range(Range("A1"), Range("G10"))
> > With rDataRng
> > .Value = .Value
> > .Sort Key1:=.Item(2, 1), Order1:=xlDescending, OrderCustom:=1, _
> > Orientation:=xlLeftToRight
> > End With
>
> --------------------------------------------------------------------------
-------------------
> >
> > I initially had .Item(2) in there as the Key1 entry, which caused me to
> > stumble upon this (what I see as) strange behavior.
> > Is this a bug or one of those "by design" features?
> >
> > Thanks for any input.
> > -Jeremy
> >
> >
> >

[Jeremy Gollehon]

Thanks for the clarification Bernie. Thinking about this more sparked my
memory of where I first learned to use .item.
http://www.cpearson.com/excel/cells.htm

Low and behold, right there in the article:
--------------------------------------------------------------------------
Cells can also be referenced with a single index number. The counting goes
left to right, then down one row and left to right again. E.g.,
Range("A1:B2")(1) refers to Cell A1
Range("A1:B2")(2) refers to Cell B1
Range("A1:B2")(3) refers to Cell A2
Range("A1:B2")(4) refers to Cell B2
--------------------------------------------------------------------------

Thanks to Jim and Bernie for your replies.
-Jeremy



"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:uWnNcQeQFHA.244@TK2MSFTNGP12.phx.gbl...
> Jim, and Jeremy,
>
> The arguments are not read right to left, and singel indices are not
> referring to rows or columns. (They just seem to sometimes, when working
> with single column or single row ranges). Excel steps through rows first
> when simply indexing a range.
>
> MsgBox Range("A1:Z10000").Item(100000).Address
>
> works, because the 100000 doesn't refer to a column or a row, but rather
> the
> 100000th member when stepping through rowwise.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Jim Thomlinson" <JimThomlinson@discussions.microsoft.com> wrote in
> message
> news:593AAB31-14B0-43AB-99C7-9D13A2CF13E9@microsoft.com...
>> You are only referencing one dimension of a two dimension array.
>> Arguments
>> are read right to left (backwards from what you would think) so you are
>> referencing the columns, not the rows... As a general rule you should
> alway
>> reference bot dimensions of the array to avoid any confusion... because
>> it
>> certainly can be confusing...
>>
>> HTH
>>
>> "Jeremy Gollehon" wrote:
>>
>> > Can someone explain this to me?
>> >
>> > From the immediate window:
>> > ?Range("A1").Item(3,1).address
>> > $A$3
>> > ?Range("A1").Item(3).address
>> > $A$3
>> > ?Range(Range("A1"), Range("G10")).Item(3,1).address
>> > $A$3
>> > ?Range(Range("A1"), Range("G10")).Item(3).address
>> > $C$1 <----!!!???
>> >
>> > The last one there is where my confusion lies.
>> >
>> > I'm sorting a range with the code below(rough example):
>>
>> --------------------------------------------------------------------------
> -------------------
>> > Set rDataRng = Range(Range("A1"), Range("G10"))
>> > With rDataRng
>> > .Value = .Value
>> > .Sort Key1:=.Item(2, 1), Order1:=xlDescending, OrderCustom:=1, _
>> > Orientation:=xlLeftToRight
>> > End With
>>
>> --------------------------------------------------------------------------
> -------------------
>> >
>> > I initially had .Item(2) in there as the Key1 entry, which caused me to
>> > stumble upon this (what I see as) strange behavior.
>> > Is this a bug or one of those "by design" features?
>> >
>> > Thanks for any input.
>> > -Jeremy
>> >
>> >
>> >
>
>

[Alan Beban]

Yep, posted 7 years ago. Nice to see it still referred to, thanks to
Chip Pearson's inclusion of it on his Web site.

Alan Beban

Jeremy Gollehon wrote:
> Thanks for the clarification Bernie. Thinking about this more sparked my
> memory of where I first learned to use .item.
> http://www.cpearson.com/excel/cells.htm
>
> Low and behold, right there in the article:
> --------------------------------------------------------------------------
> Cells can also be referenced with a single index number. The counting goes
> left to right, then down one row and left to right again. E.g.,
> Range("A1:B2")(1) refers to Cell A1
> Range("A1:B2")(2) refers to Cell B1
> Range("A1:B2")(3) refers to Cell A2
> Range("A1:B2")(4) refers to Cell B2
> --------------------------------------------------------------------------
>
> Thanks to Jim and Bernie for your replies.
> -Jeremy
>
>
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:uWnNcQeQFHA.244@TK2MSFTNGP12.phx.gbl...
>
>>Jim, and Jeremy,
>>
>>The arguments are not read right to left, and singel indices are not
>>referring to rows or columns. (They just seem to sometimes, when working
>>with single column or single row ranges). Excel steps through rows first
>>when simply indexing a range.
>>
>>MsgBox Range("A1:Z10000").Item(100000).Address
>>
>>works, because the 100000 doesn't refer to a column or a row, but rather
>>the
>>100000th member when stepping through rowwise.
>>
>>HTH,
>>Bernie
>>MS Excel MVP
>>
>>
>>"Jim Thomlinson" <JimThomlinson@discussions.microsoft.com> wrote in
>>message
>>news:593AAB31-14B0-43AB-99C7-9D13A2CF13E9@microsoft.com...
>>
>>>You are only referencing one dimension of a two dimension array.
>>>Arguments
>>>are read right to left (backwards from what you would think) so you are
>>>referencing the columns, not the rows... As a general rule you should
>>
>>alway
>>
>>>reference bot dimensions of the array to avoid any confusion... because
>>>it
>>>certainly can be confusing...
>>>
>>>HTH
>>>
>>>"Jeremy Gollehon" wrote:
>>>
>>>
>>>>Can someone explain this to me?
>>>>
>>>>From the immediate window:
>>>>?Range("A1").Item(3,1).address
>>>>$A$3
>>>>?Range("A1").Item(3).address
>>>>$A$3
>>>>?Range(Range("A1"), Range("G10")).Item(3,1).address
>>>>$A$3
>>>>?Range(Range("A1"), Range("G10")).Item(3).address
>>>>$C$1 <----!!!???
>>>>
>>>>The last one there is where my confusion lies.
>>>>
>>>>I'm sorting a range with the code below(rough example):
>>>
>>>--------------------------------------------------------------------------
>>
>>-------------------
>>
>>>> Set rDataRng = Range(Range("A1"), Range("G10"))
>>>> With rDataRng
>>>> .Value = .Value
>>>> .Sort Key1:=.Item(2, 1), Order1:=xlDescending, OrderCustom:=1, _
>>>> Orientation:=xlLeftToRight
>>>> End With
>>>
>>>--------------------------------------------------------------------------
>>
>>-------------------
>>
>>>>I initially had .Item(2) in there as the Key1 entry, which caused me to
>>>>stumble upon this (what I see as) strange behavior.
>>>>Is this a bug or one of those "by design" features?
>>>>
>>>>Thanks for any input.
>>>>-Jeremy
>>>>
>>>>
>>>>
>>
>>
>
>