SUM formula VBA

Question

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.

SeanC · Answer 1 · 2012-09-26T13:32:02.227

5

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

edited Sep 26 '12 at 13:32

answered Jul 05 '12 at 13:24

SeanC

15,695
5
45
66

score 1 · Answer 2 · edited Jul 05 '12 at 06:50

1

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

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

edited Jul 05 '12 at 06:50

Jean-François Corbett

37,420
30
139
188

answered Jul 05 '12 at 03:36

user1155383

139
3
5
9

score 0 · Answer 3 · answered Jul 06 '12 at 03:55

0

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

answered Jul 06 '12 at 03:55

Margus

19,694
14
55
103

SUM formula VBA

3 Answers3

Linked