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