Locating the third item in a Range
 

I am trying to get to a certain place in a range, for example the 3rd item.
If the range consists of three cells A1, A10, A20 and I use
Number = 3
s = Selection(Number).Address
v = Selection(Number).Value
then I get to cell A3 (third from the start) instead of A20 (which is what I
want)

Can I get to a certain place without using a loop?
Thanks in advance


--
jake

Locating the third item in a Range
 

Jake, since you have selected three individual cells in a
non-contiguous selection, Excel sees them as three areas. Try
selection.areas(3).cells(1)
James
Jakobshavn Isbrae wrote:
I am trying to get to a certain place in a range, for example the 3rd item.
If the range consists of three cells A1, A10, A20 and I use
Number = 3
s = Selection(Number).Address
v = Selection(Number).Value
then I get to cell A3 (third from the start) instead of A20 (which is what I
want)

Can I get to a certain place without using a loop?
Thanks in advance


--
jake

Locating the third item in a Range
 

Hi,

you don't need a loop.

try something like this:

Dim myRange As Range
Set myRange = Range("rng")
MsgBox myRange(3).Value

where rng is the named range on the worksheet.

HTH

Philip

"Jakobshavn Isbrae" wrote:

I am trying to get to a certain place in a range, for example the 3rd item.
If the range consists of three cells A1, A10, A20 and I use
Number = 3
s = Selection(Number).Address
v = Selection(Number).Value
then I get to cell A3 (third from the start) instead of A20 (which is what I
want)

Can I get to a certain place without using a loop?
Thanks in advance


--
jake

Locating the third item in a Range
 

Thank you for your response, but I am still getting the third cell down from
the start of the range instead of the third item in the range.
--
jake


"Philip" wrote:

Hi,

you don't need a loop.

try something like this:

Dim myRange As Range
Set myRange = Range("rng")
MsgBox myRange(3).Value

where rng is the named range on the worksheet.

HTH

Philip

"Jakobshavn Isbrae" wrote:

I am trying to get to a certain place in a range, for example the 3rd item.
If the range consists of three cells A1, A10, A20 and I use
Number = 3
s = Selection(Number).Address
v = Selection(Number).Value
then I get to cell A3 (third from the start) instead of A20 (which is what I
want)

Can I get to a certain place without using a loop?
Thanks in advance


--
jake

Locating the third item in a Range
 

Or simpler

Selection.Areas(3)(1).Value

Alan Beban

Zone wrote:
Jake, since you have selected three individual cells in a
non-contiguous selection, Excel sees them as three areas. Try
selection.areas(3).cells(1)
James
Jakobshavn Isbrae wrote:
I am trying to get to a certain place in a range, for example the 3rd item.
If the range consists of three cells A1, A10, A20 and I use
Number = 3
s = Selection(Number).Address
v = Selection(Number).Value
then I get to cell A3 (third from the start) instead of A20 (which is what I
want)

Can I get to a certain place without using a loop?
Thanks in advance


--
jake

Locating the third item in a Range
 

Good idea to test before you post.

Alan Beban

Philip wrote:
Hi,

you don't need a loop.

try something like this:

Dim myRange As Range
Set myRange = Range("rng")
MsgBox myRange(3).Value

where rng is the named range on the worksheet.

HTH

Philip

"Jakobshavn Isbrae" wrote:

I am trying to get to a certain place in a range, for example the 3rd item.
If the range consists of three cells A1, A10, A20 and I use
Number = 3
s = Selection(Number).Address
v = Selection(Number).Value
then I get to cell A3 (third from the start) instead of A20 (which is what I
want)

Can I get to a certain place without using a loop?
Thanks in advance


--
jake

Locating the third item in a Range
 

Try Zone's answer.

You chose the bad answer which was identical to your original attempt which
failed.

--
Regards,
Tom Ogilvy

"Jakobshavn Isbrae" wrote in
message ...
Thank you for your response, but I am still getting the third cell down
from
the start of the range instead of the third item in the range.
--
jake


"Philip" wrote:

Hi,

you don't need a loop.

try something like this:

Dim myRange As Range
Set myRange = Range("rng")
MsgBox myRange(3).Value

where rng is the named range on the worksheet.

HTH

Philip

"Jakobshavn Isbrae" wrote:

I am trying to get to a certain place in a range, for example the 3rd
item.
If the range consists of three cells A1, A10, A20 and I use
Number = 3
s = Selection(Number).Address
v = Selection(Number).Value
then I get to cell A3 (third from the start) instead of A20 (which is
what I
want)

Can I get to a certain place without using a loop?
Thanks in advance


--
jake

Locating the third item in a Range
 

Hi Tom

I haven't seen an answer from Zone in OfficeOnline Discussion Groups yet..
I will look for it in Google Groups
--
jake


"Tom Ogilvy" wrote:

Try Zone's answer.

You chose the bad answer which was identical to your original attempt which
failed.

--
Regards,
Tom Ogilvy

"Jakobshavn Isbrae" wrote in
message ...
Thank you for your response, but I am still getting the third cell down
from
the start of the range instead of the third item in the range.
--
jake


"Philip" wrote:

Hi,

you don't need a loop.

try something like this:

Dim myRange As Range
Set myRange = Range("rng")
MsgBox myRange(3).Value

where rng is the named range on the worksheet.

HTH

Philip

"Jakobshavn Isbrae" wrote:

I am trying to get to a certain place in a range, for example the 3rd
item.
If the range consists of three cells A1, A10, A20 and I use
Number = 3
s = Selection(Number).Address
v = Selection(Number).Value
then I get to cell A3 (third from the start) instead of A20 (which is
what I
want)

Can I get to a certain place without using a loop?
Thanks in advance


--
jake

Locating the third item in a Range
 

selection.areas(3).cells(1)

or as Alan Beban added

selection.areas(3)(1).value


--
Regards,
Tom Ogilvy

"Jakobshavn Isbrae" wrote in
message ...
Hi Tom

I haven't seen an answer from Zone in OfficeOnline Discussion Groups yet..
I will look for it in Google Groups
--
jake


"Tom Ogilvy" wrote:

Try Zone's answer.

You chose the bad answer which was identical to your original attempt
which
failed.

--
Regards,
Tom Ogilvy

"Jakobshavn Isbrae" wrote in
message ...
Thank you for your response, but I am still getting the third cell down
from
the start of the range instead of the third item in the range.
--
jake


"Philip" wrote:

Hi,

you don't need a loop.

try something like this:

Dim myRange As Range
Set myRange = Range("rng")
MsgBox myRange(3).Value

where rng is the named range on the worksheet.

HTH

Philip

"Jakobshavn Isbrae" wrote:

I am trying to get to a certain place in a range, for example the
3rd
item.
If the range consists of three cells A1, A10, A20 and I use
Number = 3
s = Selection(Number).Address
v = Selection(Number).Value
then I get to cell A3 (third from the start) instead of A20 (which
is
what I
want)

Can I get to a certain place without using a loop?
Thanks in advance


--
jake

Locating the third item in a Range
 

Tom, Alan, and ZONE


Thank you very much. Works like a charm. Its good to replace 7 lines of
code with one.

Still don't know why ZONE's post appears on Google Groups and not
OfficeOnline Disussion.
--
jake


"Tom Ogilvy" wrote:

selection.areas(3).cells(1)

or as Alan Beban added

selection.areas(3)(1).value


--
Regards,
Tom Ogilvy

"Jakobshavn Isbrae" wrote in
message ...
Hi Tom

I haven't seen an answer from Zone in OfficeOnline Discussion Groups yet..
I will look for it in Google Groups
--
jake


"Tom Ogilvy" wrote:

Try Zone's answer.

You chose the bad answer which was identical to your original attempt
which
failed.

--
Regards,
Tom Ogilvy

"Jakobshavn Isbrae" wrote in
message ...
Thank you for your response, but I am still getting the third cell down
from
the start of the range instead of the third item in the range.
--
jake


"Philip" wrote:

Hi,

you don't need a loop.

try something like this:

Dim myRange As Range
Set myRange = Range("rng")
MsgBox myRange(3).Value

where rng is the named range on the worksheet.

HTH

Philip

"Jakobshavn Isbrae" wrote:

I am trying to get to a certain place in a range, for example the
3rd
item.
If the range consists of three cells A1, A10, A20 and I use
Number = 3
s = Selection(Number).Address
v = Selection(Number).Value
then I get to cell A3 (third from the start) instead of A20 (which
is
what I
want)

Can I get to a certain place without using a loop?
Thanks in advance


--
jake