Formula Vs FormulaR1C1 To Pass Calculation Range Through VBA

**labkhand** · 10-17-2013, 06:23 AM

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:

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:

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:

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.