CODEWITHSUNDEEP
vbaexcelformula
user1155383
user1155383

Reputation: 139

SUM formula VBA

I am trying to calculate the sum of changing cell range in vba. Unfortunately the cell values are variables. I can't seem to get the following formula to work. 

Private Sub calcOverheadRate(startCell As Integer, endCell As Integer)
     Total = endCell + 1
     Range("D" & Total).Formula = "=SUM("D" & startCell & ":" & "D" & endCell)"
End Sub

I get compile error: "Expected: end of statement

To solve this problem I changed the function to, 

Private Sub calcOverheadRate(startCell As Integer, endCell As Integer)

    Dim start As String
    Dim endC As String

    start = "D" & CStr(startCell)
    endC = "D" & CStr(endCell)

    Total = endCell + 1

    Range("D" & Total).Formula = "=SUM(start:endC)"

End Sub

The function compiles fine, when I run it, the value in the cell is "#NAME" where it references SUM(start:endC) not SUM(D5:D23)....

Any thoughts on how to solve this would be appreciated.

Upvotes: 1

Views: 15391

Answers (3)

SeanC
SeanC

Reputation: 15923

The quotes are the issue:
Range("D" & Total).Formula = "=SUM(" & startCell & ":" & endCell & ")"

Upvotes: 5

Margus
Margus

Reputation: 20038

How about you try using a table?

enter image description here

Here is a 1 min video on how to make a table in Excel:

http://www.screenr.com/VvZ8

Upvotes: 0

user1155383
user1155383

Reputation: 139

I have figured out the problem the & needs to be inside the quotation for string literals

Range("D" & Total).Formula = "=SUM(" & start & ":" & endC & ")"

Upvotes: 1

Related Questions