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

--
Regards,
Tom Ogilvy



"RichIT" wrote:

Thanks Tom and Harald,

However in both cases, the result is the actual Formula (i.e.
R[-7]C[-4]) rather than the value of the resulting formula if that
makes sense. I would like a value to be put into B9, and then using i
(the for loop) give the value of the next cell.

Any ideas ?????
Tom Ogilvy wrote:
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

--
Regards,
Tom Ogilvy


"RichIT" wrote:

Hi All, thanks a million in advance!

my Current code is:

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

This does not work. Im trying to set cell B9= to A6 (in this example),
then repeat the code with i=5 meaning B9 should now = A5 (in this
example). Could anyone help please. Thanks

Rich