Excel VBA-汇总一列 [英] Excel VBA - Sum up a column

问题描述

我试图在Excel VBA中汇总一列，但是因为有空格，所以总和为0.

I'm trying to sum up a column in Excel VBA , however because there are blank spaces, I'm getting 0 as the sum.

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始终为零.我无法删除工作表上的空白.谁能提出以编程方式求和的方法?

dExternalTotal is always zero. I cannot delete the blank spaces on the work sheet. Can anyone suggest a way to do the sum programatically?

推荐答案

如果要在Excel中添加一列数字，可以执行以下操作. (我正在使用Excel 2010，但不应有所作为.)

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.)

示例: 假设您要以B10列的形式将B列的单元格添加到B100&中.希望答案在单元格X中或为变量X(X可以是任何单元格或您创建的任何变量，例如Dim X为整数等).这是代码:

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:

Range("B5") = "=SUM(B10:B100)"

或

X = "=SUM(B10:B100)

"= Sum(B10:B100)括号内没有引号，但Range(" B5)括号内有引号.等号和引号之间有一个空格.正确的.

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.

某些单元格为空并不重要，它只会将它们视为包含零！

It will not matter if some cells are empty, it will simply see them as containing zeros!

这应该为您做到！

这篇关于Excel VBA-汇总一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！