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