vba 循环内的 Application.WorksheetFunction.Sum
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8928757/
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
Application.WorksheetFunction.Sum within a loop
提问by Somebody
I have this code which retrieves some data from a sql query and place it in an excel Sheet:
我有这个代码，它从一个 sql 查询中检索一些数据并将其放在一个 Excel 表中：
I = 4
Do While Not rs.EOF
I = I + 1
Sheet1.Range("A" & I).Value = rs(0)
Sheet1.Range("B" & I).Value = rs(1)
Sheet1.Range("C" & I).Value = rs(2)
Sheet1.Range("D" & I).Value = rs(3)
Sheet1.Range("E" & I).Value = rs(4)
Sheet1.Range("F" & I).Value = rs(5)
Sheet1.Range("G" & I).Value = rs(6)
Sheet1.Range("H" & I).Value = rs(7)
Sheet1.Range("I" & I).Value = rs(8)
Sheet1.Range("J" & I).Value = rs(9)
Sheet1.Range("K" & I).Value = rs(10)
Sheet1.Range("L" & I).Value = rs(11)
Sheet1.Range("M" & I).Value = rs(12)
Sheet1.Range("N" & I).Value = rs(13)
Sheet1.Range("O" & I).Value = rs(14)
Sheet1.Range("P" & I).Value = rs(15)
Sheet1.Range("Q" & I).Value = Application.WorksheetFunction.Sum(Sheet1.Range("E" & I), Sheet1.Range("P" & I))
'Sheet1.Range("Q" & I).Value = Sheet1.Range("E" & I).Value + Sheet1.Range("F" & I).Value + Sheet1.Range("G" & I).Value + Sheet1.Range("H" & I).Value
rs.MoveNext
Loop
Now, I'm trying to sum a range of columns from col "E" to col "P" but it didn't work, it just sum those cells only ("E" & I and "P" & I), not the range.
现在，我试图对从 col "E" 到 col "P" 的一系列列求和，但它没有用，它只是对这些单元格求和（“E”& I 和“P”& I），而不是范围。
Any help please?
请问有什么帮助吗？
回答by JMax
First, you can optimize and sanitize you code using the withstatement:
首先，您可以使用以下with语句优化和清理代码：
Sanitize your code using with
使用清理您的代码 with
Don't do
不要做
Sheet1.Range("A" & I).Value = rs(0)
Sheet1.Range("B" & I).Value = rs(1)
Sheet1.Range("C" & I).Value = rs(2)
...
Do
做
With Sheet1
.Range("A" & I).Value = rs(0)
.Range("B" & I).Value = rs(1)
.Range("C" & I).Value = rs(2)
...
End With
Suming a range instead of unit cells
求和范围而不是单元格
Understanding your original statement
理解你的原始陈述
In your original statement:
在您的原始声明中：
Application.WorksheetFunction.Sum(Sheet1.Range("E" & I), Sheet1.Range("P" & I))
You are calling the Worksheet Function with several arguments (as you would do with SUM(E1, P1)but you want to sum a range, i.e. SUM(E1:P1).
您正在使用多个参数调用工作表函数（就像您所做的那样，SUM(E1, P1)但您想对一个范围求和，即SUM(E1:P1).
Solving your issue
解决您的问题
Better try:
最好尝试：
Application.WorksheetFunction.Sum(Sheet1.Range("E" & I & ":P" & I))
Note that you could also define your Range this way:
请注意，您也可以通过这种方式定义范围：
With Sheet1
Application.WorksheetFunction.Sum(.Range(.Cells(I, "E"), .Cells(I, "P"))
End With
This latter can be very useful because you can use the .Cellswith either a letter or a Long for the column.
后者非常有用，因为您可以使用.Cells带有字母或 Long 的列作为列。
回答by Siddharth Rout
Why not use a loop instead of such long code
为什么不使用循环而不是这么长的代码
Option Explicit
Sub Sample()
Dim i As Long, j As Long, k As Long
'~~> Rest of your code
Do While Not rs.EOF
i = i + 1
j = 0
With Sheets1
k = 1
For j = 0 To 15
.Cells(i, k).Value = rs(j)
k = k + 1
Next
.Range("Q" & i).Value = _
Application.WorksheetFunction.Sum(.Range("E" & i & ":P" & i))
End With
rs.MoveNext
Loop
'~~> Rest of your code
End Sub
