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.

回答1:

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

回答2:

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

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

回答3:

How about you try using a table?

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

http://www.screenr.com/VvZ8