Putting R1C1 formula on variable rows to reference the same column

Question

I need help to write a code which puts an R1C1 formula into a row’s cells.

The start position of the row’s will vary each time the macro is run. Ie. If the macro is run the first time, the formula will be entered into Row B16 as R[-5]C[3]. R[-5] in the case is E12.

However, when the macro is run another time, & its entered into row B25, I still want it to reference to E3, but it references to E20.

Here is my code

Dim cell As Range, MyRange As Range 

Set MyRange = Range("B1:B5000")

For Each cell In MyRange

If cell = " " And cell.Offset(, 1) <> "Record" Then

cell.FormulaR1C1 = "=SUM(R[-5]C[3]: SUM(R[-5]C[4])"

End If

Next cell

End With

You are right, my code was trying to say This row - 5, this column + 3: this row -5, this column + 4 The problem I have is that This row could be any row & I would like to use relative referencing as this formula copies down to the next row So what I’m trying to do is this

Cell B16 = E11+F11
Cell B17 = E12+F12
Cell B18 = E13+F13 etc

Then when the macro is run again & start cell is E25, then

Cell E25 = E20+F20
Cell E26 = E21+F21
Cell E26= E22+F22 etc

So, regardless of which cell the macro points to, it will always start the calculation from E11+F11

Answer 1

Here is my code

Dim cell As Range, MyRange As Range Set MyRange = Range("B1:B5000")

For Each cell In MyRange

If cell = " " And cell.Offset(, 1) <> "Record" Then

cell.FormulaR1C1 = "=SUM(R[-5]C[3]: SUM(R[-5]C[4])"

End If

Next cell

End With

Answer 2

You're using relative references in your formula, and the formula won't work as you're trying to say =SUM(E1:SUM(F1) if the entered in cell B6. Any rows higher than that and it will try and reference off the sheet.

To use absolute referencing use R3C5 (row 3, column 5 = E3).
At best your formula was trying to say This row - 5, this column + 3: this row -5, this column + 4

Maybe try "=SUM(R3C5:R3C4)" which is the same as =SUM($E$3:$F$3).

Also - cell = " " - the cell must contain a single space? Should it be cell = ""?

Edit: In response to your edit - if you want the first formula to always look at E11:F11, and the next to be E12:F12, etc you can use one of these solutions:

To add the formula to all rows in one hit - this doesn't check for a cell with a space:

Public Sub Test()

    Dim MyRange As Range
    Dim lOffset As Long

    Set MyRange = Range("B1:B5000")

    With MyRange
        lOffset = 11 - .Row
        .FormulaR1C1 = "=IF(RC[1]<>""Record"",SUM(R[" & lOffset & "]C5:R[" & lOffset & "]C6),"""")"
    End With

End Sub  

To check that each cell has a space in it before adding the formula:

Public Sub Test1()

    Dim MyRange As Range
    Dim rCell As Range
    Dim lOffset As Long

    Set MyRange = Range("B30:B5000")

    lOffset = 11 - MyRange.Row
    For Each rCell In MyRange
        If rCell = " " And rCell.Offset(, 1) <> "Record" Then
            rCell.FormulaR1C1 = "=SUM(R[" & lOffset & "]C5:R[" & lOffset & "]C6)"
        End If
    Next rCell

End Sub

Here's the results for the second code block showing the formula always starts in row 11:

And if you change the range the formula goes in:

Edit 2:
If rows 20:24 have Record then this will place =SUM($E11:$F11) in row 25. If row 26 has a record then row 27 will have =SUM($E12:$F12)

Public Sub Test1()

    Dim MyRange As Range
    Dim rCell As Range
    Dim lOffset As Long

    Set MyRange = Range("B20:B30")

    lOffset = 11
    For Each rCell In MyRange
        If rCell = " " And rCell.Offset(, 1) <> "Record" Then
            rCell.FormulaR1C1 = "=SUM(R[" & lOffset - rCell.Row & "]C5:R[" & lOffset - rCell.Row & "]C6)"
            lOffset = lOffset + 1
        End If
    Next rCell

End Sub