Formula Vs FormulaR1C1 To Pass Calculation Range Through VBA

Hi All,

I am trying to build a SUM function throughout my VBA code by getting the addresses of the relevant cells which need to be part of the SUM function. Somthing like this:

Code: [View]

sFormula="=SUM("
more code...
sFormula = sFormula & .Offset(1, 1).Address(False, False) & ", "

Then I remove the extra comma at the end of this string formula before trying to assign it to several formulas of adjacent cells since they need to sum as follows:

Code: [View]

Range("B29").Select
ActiveCell.Formula = sFormula
ActiveCell.Resize(, 4).Select
Selection.Formula = sFormula

This isn't working and the SUM function isn't working either. I update related cells and the sum still remains zero. What is going on and how do I fix this so I can build the SUM function in the code and later on use the appropriate cell addresses as part of the SUM function so that I can assign it to several adjecent cells formula. Is this something to do with FormulaR1C1 style?


I also recorded the following macro which is what I basically am trying to do which isn't working:

Code: [View]

Range("B29").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-9]C,R[-6]C,R[-3]C)"
    Range("B29").Select
    Selection.AutoFill Destination:=Range("B29:E29"), Type:=xlFillDefault

Thanks for your help.

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: [View]

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

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!

Code: [View]

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

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

In my suggestion, where did the character 'B' come in? The result of my function would have been "=SUM(R[-11]C,R[-14]C,R[-17]C,R[-20]C,R[-23]C,R[-26]C,R[-29]C,R[-32]C,R[-35]C,R[-38]C)"

Yes, there shouldn't be any "B"s in the formula and that was part of the issue resolution. YOur original suggestion was the key resolving this isue. Thanks!