| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Locating values in a range and bringing up the corresponding price | Excel Worksheet Functions | |||
| Locating duplicates within range of time punch data | Excel Worksheet Functions | |||
| Locating a range of dates and listing somewhere else? | Excel Discussion (Misc queries) | |||
| Locating variable range to copy | New Users to Excel | |||
| Locating a value in a range from a reference. | Excel Worksheet Functions | |||
Linear Mode
