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?

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

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?

Refer to 1 cell relative from named range
 

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?

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

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?

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?