Unexpected change of excel formula coming out of VBA macro

2
votes

I have the below VBA example code that should populate the SumSQ range with the same formula as I've defined in the code

=SUMSQ(RC13-RC11,RC16-RC14,RC19-RC17,RC22-RC20,RC25-RC23)/(MONTH(TODAY())-MONTH(DATE(2016,1,1)))

Sub Prep()
    Dim Sh As Worksheet
    Dim CBS As Range
    Dim SumSQ As Range

    'Set range from C3 to final row of column C
    Set Sh = Worksheets("Sheet1")
    With Sh
        Set CBS = .Range("C6:C" & .Range("C" & .Rows.Count).End(xlUp).Row)
        Set SumSQ = .Range("AV6:AV" & CBS.End(xlDown).Row)
    End With

    SumSQ.Formula = "=SUMSQ(RC13-RC11,RC16-RC14,RC19-RC17,RC22-RC20,RC25-RC23)/(MONTH(TODAY())-MONTH(DATE(2016,1,1)))"

End Sub

However, for some reason in the actual spreadsheet after running the macro, the actual formulate that is getting populated for all cells in the range is:

=SUMSQ(R[7]C[423]-R[5]C[423];R[10]C[423]-R[8]C[423];R[13]C[423]-R[11]C[423];R[16]C[423]-R[14]C[423];R[19]C[423]-R[17]C[423])/(MONTH(TODAY())-MONTH(DATE(2016;1;1)))

In case it's of relevance, my locale settings use ; instead of , in formulas

1
vbaexcel
Just guessing, but can you change SumSQ.Formula to SumSQ.Formular1C1? – Vityata
@Vityata is correct.All your intended R1C1 references are also valid A1 addresses, which is why you're getting that result. – Rory
Are they R1C1 addresses? or are you referencing column RC (column 471) – Gary Evans
Haha Thank you! .formulaR1C1 worked perfectly – Bendy
your RC style reference seems doubtful. It is R2C2 OR R[2]C[2] for absolute and relative references. Please see RC Style Reference for getting my view point. – skkakkar

1 Answers

1
votes

So I am posting this as an answer as well: Can you change SumSQ.Formula to SumSQ.Formular1C1?