Sub Dosomething()
Range ("B9").Select
Dim i As Integer
For i = 4 to 7
ActiveCell.FormulaR1C1 = "=R[" & -i & "]C[4]"
ActiveCell.Value = ActiveCell.Value
Msgbox "Value in B9 is " & ActiveCell.Value & vbnewline & _
"Value came from " & Range("B9").Offset(-i,4).Address(0,0,xlA1)
Next i
End Sub

--
Regards,
Tom Ogilvy


"RichIT" wrote:

Earlier Tom and Harald helped me with some code to come up with

Sub Dosomething()
Range ("B9").Select
Dim i As Integer
For i = 4 to 7
ActiveCell.FormulaR1C1 = "R[" & -i & "]C[-1]"
Msgbox Activecell.formula
Next i
End Sub

However I now get the formula ( R[-7][4]) in B9 and not the value of
the resulting formula.
Any more help greatly appreciated

Thanks