SUM 公式 VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11337510/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SUM formula VBA
提问by user1155383
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.
我正在尝试计算 vba 中更改单元格范围的总和。不幸的是，单元格值是变量。我似乎无法让以下公式起作用。
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)....
该函数编译得很好，当我运行它时，单元格中的值是“#NAME”，它引用的SUM(start:endC)不是SUM(D5:D23)....
Any thoughts on how to solve this would be appreciated.
关于如何解决这个问题的任何想法将不胜感激。
回答by SeanC
The quotes are the issue:Range("D" & Total).Formula = "=SUM(" & startCell & ":" & endCell & ")"
引号是问题：Range("D" & Total).Formula = "=SUM(" & startCell & ":" & endCell & ")"
回答by user1155383
I have figured out the problem the & needs to be inside the quotation for string literals
我已经发现 & 需要在字符串文字的引号内的问题
Range("D" & Total).Formula = "=SUM(" & start & ":" & endC & ")"
回答by Margus
How about you try using a table?
你试试用一张桌子怎么样？
Here is a 1 min video on how to make a table in Excel:
这是有关如何在 Excel 中制作表格的 1 分钟视频：
