自动求和单元格 VBA Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13227900/
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
Autosum Cells VBA Excel
提问by Anthony
Im trying to autosum the values within a block of cells in Excel using VBA. I kept getting a type mismatch error while debugging the following code. Where did I go wrong?
我正在尝试使用 VBA 对 Excel 中的单元格块内的值进行自动求和。在调试以下代码时，我不断收到类型不匹配错误。我哪里做错了？
Sub autosumtest()
Dim total As Integer
Worksheets("Sheet1Test").Select
Range("F16:G20").Select
total = CInt("=SUM(Selection.Values)")
MsgBox (total)
End Sub
EDIT 1:Here is the simple sample of my test data called Autosum Range:
EDIT 1:这是我的测试数据的简单示例，称为 Autosum Range：
回答by Patrick Honorez
You can use the [] to evaluate any spreadsheet expression.
您可以使用 [] 来计算任何电子表格表达式。
Sub autosumtest()
Dim total As Integer
total = CInt([=sum(sheet1Test!F16:G20)])
MsgBox (total)
End Sub
回答by CustomX
Your range F16 to G20will contain different formats. Make sure they have the Numberformatting and problem solved.
您的范围F16 to G20将包含不同的格式。确保他们已Number解决格式和问题。
You can also use the following;
您还可以使用以下内容；
Sub autosumtest()
Dim total As Integer
total = [=SUM(Sheet1Test!F16:G20)]
MsgBox (total)
End Sub
回答by Jüri Ruut
Yes, there's a type mismatch, as "=SUM(F16:G20)" is a string.
是的，存在类型不匹配，因为“=SUM(F16:G20)”是一个字符串。
Worksheetfunction should be used for summing a desired range. Unless there's a wish to something further with selection, .Select is not needed.
Worksheetfunction 应该用于求和所需的范围。除非希望进一步选择，否则不需要 .Select 。
Option Explicit
Sub autosumtest()
Dim total As Integer
Dim rng As Range
Set rng = Worksheets("Sheet1Test").Range("F16:G20")
total = CInt(WorksheetFunction.Sum(rng))
MsgBox (total)
End Sub
回答by brettdj
Ok, given the length of somethe other answers and the redundant CInt....
好的，考虑到其他一些答案的长度和多余的CInt......
Sub Easy()
MsgBox Application.Sum(Sheets("Sheet1Test").Range("F16:G20"))
End Sub
