PDA

View Full Version : Solved: Offset To Add Formula



Nurofen
11-14-2007, 11:50 AM
Hi,

I'm trying to add a forumla every three cell in the range of R4:R414 with offset.

not sure how to insert the formula every time

Range("R4").FormulaR1C1 = "=R[-2]C[-1]+RC[-1]"
ActiveCell.Offset(3, 0).Select



Thank you for your time

Nurofen

figment
11-14-2007, 11:56 AM
to use your same setup the folowing code will work

Sub testing()
Dim a As Long
Range("R4").Select
Range("R4").FormulaR1C1 = "=R[-2]C[-1]+RC[-1]"
For a = 3 To 410 Step 3
ActiveCell.Offset(a, 0).FormulaR1C1 = "=R[-2]C[-1]+RC[-1]"
Next
End Sub


i would do something like this

Sub testing()
Dim a As Long
For a = 4 To 414 Step 3
Range("R" & a).FormulaR1C1 = "=R[-2]C[-1]+RC[-1]"
Next
End Sub

Nurofen
11-14-2007, 12:11 PM
Thanks figment,

works great


Thank for your time

Nurofen

xld
11-14-2007, 12:17 PM
Another way



Sub testing()
Dim i As Long
Dim rng As Range
Set rng = Range("R4")
For i = 7 To 414 Step 3
Set rng = Union(rng, Cells(i, "R"))
Next i
rng.FormulaR1C1 = "=R[-2]C[-1]+RC[-1]"
End Sub

Nurofen
11-14-2007, 12:31 PM
Thanks Xld


Thank you for your time

Nurofen