CODEWITHSUNDEEP
excelvba
Grace
Grace

Reputation: 1

Concatenate string to build formula in VBA

I tried to build VBA code to enter a formula to sum up a range.

I want a formula in cell E30

=SUM(E10:E20)

Sub calTotalOneOffExpense()

Dim startRow As Integer
Dim endRow As Integer
startRow = 10
endRow = 20

Range("E30:E30").Select
Dim sFormula As String

sFormula = "=Sum(E" & startRow & ":E" & endRow & ")"
ActiveCell.FormulaR1C1 = _
        sFormula

End Sub

The formula in E30 becomes

=SUM('E10':'E20')

Upvotes: 0

Views: 231

Answers (2)

Nicholas Hunter
Nicholas Hunter

Reputation: 1845

I don't think you understand the difference between Formula and FormulaR1C1. You should study the documentation. Formula expects A1-style notation.

ActiveCell.Formula = "=Sum(E" & startRow & ":E" & endRow & ")"

FormulaR1C1 expects R1C1-style notation

ActiveCell.FormulaR1C1 = "=Sum(R" & startRow & "C5:R" & endRowRow & "C5)"

which is equivalent ​to

ActiveCell.Formula = "=Sum($E$10:$E$20)"

Upvotes: 1

braX
braX

Reputation: 11735

Do not use .FormulaR1C1

Use .Formula instead.

sFormula = "=SUM(E" & startRow & ":E" & endRow & ")"
ActiveCell.Formula = sFormula

Upvotes: 0

Related Questions