Locating the third item in a Range

[Guest]

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

 

[Zone]

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

 

[Guest]

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

 

[Guest]

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.

 

[Alan Beban]

Or simpler

Selection.Areas(3)(1).Value

Alan Beban

 

[Alan Beban]

Good idea to test before you post.

Alan Beban

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

 

[Tom Ogilvy]

Try Zone's answer.

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

 

[Guest]

Hi Tom

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

 

[Tom Ogilvy]

selection.areas(3).cells(1)

or as Alan Beban added

selection.areas(3)(1).value

 

[Guest]

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.