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

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

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" wrote in message
...
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

Not exactly

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


--
Regards,
Tom Ogilvy

"Jim Thomlinson" wrote in message
...
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

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
...
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" wrote in
message
...
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

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
...

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" wrote in
message
...

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