VBA Code Correction

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

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
>
>

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
> >
> >

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
> > >
> > >
>
>