Formula Vs FormulaR1C1 To Pass Calculation Range Through VBA

[Excel Fox]

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

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

[labkhand]

Thanks for your response. I tried using your suggested solution by trying out the following test procedure. After the "InsertMetricsTable" procedure call, the debug.print is printing the correct sum formula which is "=SUM(R[-B11]C,R[-B14]C,R[-B17]C,R[-B20]C,R[-B23]C,R[-B26]C,R[-B29]C,R[-B32]C,R[-B35]C,R[-B38]C)" but when I am trying to assign it to the range formulaR1C1, I get an error saying: Run-time 1004: Application-defined or object-defined error. I am not sure what's wrong now!

Application.ScreenUpdating = False
Set oMetrics = ActiveSheet
Call InsertMetricsTables
sForMetricsTotalLineFormula = "=SUM(" & Mid(sForMetricsTotalLineFormula, 2) & ")"
Debug.Print "After = " & sForMetricsTotalLineFormula
Range("B39:D39").FormulaR1C1 = sForMetricsTotalLineFormula
Application.ScreenUpdating = True

[labkhand]

Thanks for your response. I tried using your suggested solution which works now.