Welcome to ExcelFox


If the pattern of your formula is exactly the same, and it is in non-contiguous range, then you should use FormulaR1C1 instead of Formula. A formula like "=SUM(R[-9]C,R[-6]C,R[-3]C)" in a cell means that you are summing the values that are 9, 6 and 3 rows above that cell. On the other hand, =SUM(R[9]C,R[6]C,R[3]C) would have meant 9, 6 and 3 rows below that cell. Now, all of these formulas are relative in nature. ie., it will change the reference ranges relative to the cell in which the formula is passed.


Now, =SUM(R9C,R6C,R3C) is an absolute reference (although partially), where you are fixing the reference rows to 9, 6 and 3. The column is still relative. These should now give you an idea how to build up your VBA sub-routine.


And example


Code:
Sub ExcelFox()


    Dim strFormula As String
    Const strRowIndex As String = "9/6/3"
    Dim lng As Long
    For lng = UBound(Split(strRowIndex, "/")) To 0 Step -1
        strFormula = strFormula & ",R[-" & Split(strRowIndex, "/")(lng) & "]C"
    Next lng
    strFormula = "=SUM(" & Mid(strFormula, 2) & ")"
    Range("B29:E29").FormulaR1C1 = strFormula
    
End Sub