Hi, thanks everyone (especially Tom Ogilvy) for all your help much
appreciated.
The following is the original answer and 2 of the solutions i was
given.

my Current code was:

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

And the following two solutions work:
Solution 1 displays each answer before it moves on.
Solution 2 is very simple. In the end I will by adding these values
(Solution3 given below) but only if each row meets a criteria (solution
not given here yet)


Sub Solution1()
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


Sub Solution2()
Dim iMyRow As Integer
For iMyRow = 2 To 5
Cells(9, 2) = Cells(iMyRow, 6)
Next
End Sub

Sub Solution3()
Dim iMyRow As Integer
Dim memory1 As Integer
Dim memory2 As Integer
memory2 = 0
For iMyRow = 2 To 5
Cells(9, 2) = Cells(iMyRow, 6)
mem = Cells(9, 2).Value
memory2 = memory2 + memory1
Next
Cells(9, 2) = memory2
End Sub