CODEWITHSUNDEEP
vbaexcel
Bendy
Bendy

Reputation: 3576

Unexpected change of excel formula coming out of VBA macro

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

Upvotes: 2

Views: 91

Answers (1)

Vityata
Vityata

Reputation: 43565

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

Upvotes: 1

Related Questions