Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refer to 1 cell relative from named range
Try to retrieve thevalue from 1 cell relative from named range.
Something like this. Sub test() Set MyRange = Range("C1:F10") MyNumber = MyRange.Offset(1, -2).Value End Sub However the offset method gives me an array back which is not what I want. How can I retrieve the value from for example A1 or B1 or C1 with a relative reference? MyRange.Cells(1, -2) gives a runtime error. Any suggestions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refer to 1 cell relative from named range
On May 13, 2:24*pm, Bret Bernever wrote:
Try to retrieve thevalue from 1 cell relative from named range. Something like this. Sub test() * * Set MyRange = Range("C1:F10") * * MyNumber = MyRange.Offset(1, -2).Value End Sub However the offset method gives me an array back which is not what I want. How can I retrieve the value from for example A1 or B1 or C1 *with a relative reference? MyRange.Cells(1, -2) gives a runtime error. Any suggestions? It depends on where you want it to be relative from. If it's the first cell in the range, you can query the row and column properties of the range. Excel should always return the row and column of the first cell in the range. dim oRow as Single, oCol as Integer, MyRange as Range Set MyRange = Range("C1:F10") oRow = MyRange.Row oCol = MyRange.Column MyNumber = cells(oRow, oCol).offset(1,-2).value 'MyNumber should be the value in A2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refer to 1 cell relative from named range
Relative to what? Any cell in Range("C1:F10")? What would that mean? A
specific cell in that range? Which one? How do you know which one? The program can't read your mind, so you have to focus into *something* if you want to offset from it to somewhere else. Rick "Bret Bernever" wrote in message ... Try to retrieve thevalue from 1 cell relative from named range. Something like this. Sub test() Set MyRange = Range("C1:F10") MyNumber = MyRange.Offset(1, -2).Value End Sub However the offset method gives me an array back which is not what I want. How can I retrieve the value from for example A1 or B1 or C1 with a relative reference? MyRange.Cells(1, -2) gives a runtime error. Any suggestions? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refer to 1 cell relative from named range
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refer to 1 cell relative from named range
In article <7cf16ebb-1865-4284-8820-c0cecaa0b440
@f63g2000hsf.googlegroups.com, says... It depends on where you want it to be relative from. If it's the first cell in the range, Yes, that's my starting point. You're suggestion works very well. Thanks for cooperating on this. Problem solved! wkr Bret |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refer to 1 cell relative from named range
myrange.Cells(1) is C1 so you offset from that cell to locate A1
Set myrange = Range("C1:F10") myrange.Cells(1).Offset(0, -2).Select Gord Dibben MS Excel MVP On Tue, 13 May 2008 22:33:30 +0200, Bret Bernever wrote: I just want the code for a relative reference. Relative from the named range. As I already pointed out the named range = Set MyRange = Range("C1:F10") How do I refer to A1? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refer to 1 cell relative from named range
We seem to be talking past each other. The point of my post was that in a
range such as C1:F10, there are many cells. Since an offset to a single cell is specified from a single cell (not the entire range), the question arises... Which one of those cells would you want the offset to be from? The top left one? The bottom right one? The one nearest the center of the range? What if your range were this combined one instead... D1:H1,A3:A7? Etc., etc., etc. Rick "Bret Bernever" wrote in message ... In article , says... Relative to what? The named Range! Any cell in Range("C1:F10")? No, outside the Range What would that mean? A [snip] Thought my question was clear. Reading all your questions....probably not. Sorry for that! I just want the code for a relative reference. Relative from the named range. As I already pointed out the named range = Set MyRange = Range("C1:F10") How do I refer to A1? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
refer to cell relative to range | Excel Discussion (Misc queries) | |||
Refer a specific cell in a named cell range | Excel Programming | |||
???Refer to a specifc cell in a named range | Excel Worksheet Functions | |||
How does one refer to the n-1 element of a named range? | Excel Discussion (Misc queries) | |||
How do you refer to a dynamic named range? | Excel Programming |