Excel VBA - 总结一列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2960695/
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
Excel VBA - Sum up a column
提问by Kojof
I'm trying to sum up a column in Excel VBA , however because there are blank spaces, I'm getting 0 as the sum.
我试图总结 Excel VBA 中的一列，但是因为有空格，我得到 0 作为总和。
Dim rExternalTotal As Range , dExternalTotal as Double
Set rExternalTotal = Range(rReportData.Offset(0, 0), rReportData.Offset(261, 0).End(xlUp))
dExternalTotal = Application.WorksheetFunction.Sum(rExternalTotal)
dExternalTotal is always zero. I cannot delete the blank spaces on the work sheet. Can anyone suggest a way to do the sum programatically?
dExternalTotal 始终为零。我无法删除工作表上的空格。任何人都可以建议一种以编程方式进行求和的方法吗？
回答by Jake
Here is what you can do if you want to add a column of numbers in Excel. ( I am using Excel 2010 but should not make a difference.)
如果您想在 Excel 中添加一列数字，您可以执行以下操作。（我使用的是 Excel 2010，但应该没什么区别。）
Example: Lets say you want to add the cells in Column B form B10 to B100 & want the answer to be in cell X or be Variable X ( X can be any cell or any variable you create such as Dim X as integer, etc). Here is the code:
示例：假设您要将 B 列中的单元格从 B10 形式添加到 B100 并希望答案在单元格 X 中或变量 X（X 可以是任何单元格或您创建的任何变量，例如 Dim X 作为整数等） . 这是代码：
Range("B5") = "=SUM(B10:B100)"
or
或者
X = "=SUM(B10:B100)
There are no quotation marks inside the parentheses in "=Sum(B10:B100) but there are quotation marks inside the parentheses in Range("B5"). Also there is a space between the equals sign and the quotation to the right of it.
"=Sum(B10:B100) 的括号内没有引号，而 Range("B5") 的括号内有引号。等号和它右边的引号之间也有一个空格.
It will not matter if some cells are empty, it will simply see them as containing zeros!
某些单元格是否为空并不重要，它只会将它们视为包含零！
This should do it for you!
这应该为你做！
回答by goggin13
I think you are misinterpreting the source of the error; rExternalTotal appears to be equal to a single cell.
rReportData.offset(0,0) is equal to rReportData
rReportData.offset(261,0).end(xlUp) is likely also equal to rReportData, as you offset by 261 rows and then use the .end(xlUp) function which selects the top of a contiguous data range.
If you are interested in the sum of just a column, you can just refer to the whole column:
我认为您误解了错误的来源；rExternalTotal 似乎等于单个单元格。rReportData.offset(0,0) 等于 rReportData
rReportData.offset(261,0).end(xlUp) 也可能等于 rReportData，因为您偏移 261 行，然后使用 .end(xlUp) 函数选择连续数据范围的顶部。
如果您只对一列的总和感兴趣，则可以参考整列：
dExternalTotal = Application.WorksheetFunction.Sum(columns("A:A"))
or
或者
dExternalTotal = Application.WorksheetFunction.Sum(columns((rReportData.column))
The worksheet function sum will correctly ignore blank spaces.
工作表函数 sum 将正确忽略空格。
Let me know if this helps!
让我知道这是否有帮助！
回答by Libertine
I have a label on my form receiving the sum of numbers from Column D in Sheet1. I am only interested in rows 2 to 50, you can use a row counter if your row count is dynamic. I have some blank entries as well in column D and they are ignored.
我的表单上有一个标签，用于接收来自 Sheet1 中 D 列的数字总和。我只对第 2 到 50 行感兴趣，如果您的行数是动态的，您可以使用行计数器。我在 D 列中也有一些空白条目，它们被忽略了。
Me.lblRangeTotal = Application.WorksheetFunction.Sum(ThisWorkbook.Sheets("Sheet1").Range("D2:D50"))
