VBA 中的 SUMIF 函数无法正常工作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6329572/
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
SUMIFs function within VBA not working correctly
提问by James
Public Function GRIDSALES(rev_date As Date, grid_date As Date) As Variant
Application.Volatile (True)
Set Final_Price = Sheets("KRONOS").Range("$H:$H")
Set Team = Sheets("KRONOS").Range("$DO:$DO")
Set First_PD = Sheets("KRONOS").Range("$Q:$Q")
GRIDSALES1 = Application.WorksheetFunction.SumIfs( _
Final_Price _
, Team, "<>9" _
, First_PD, ">=" & rev_date, First_PD, "<=EoMonth(" & grid_date & ")")
End Function
VBA code above complies without any error but does not generate an answer. I am trying to create a UDF that's sums the Final Price for all sales teams (excluding team 9) between two dates rev_dateand grid_date. What am I missing?
上面的 VBA 代码没有任何错误，但不会生成答案。我正在尝试创建一个 UDF，它将两个日期rev_date和grid_date. 我错过了什么？
采纳答案by Alen
Despise "hardcode" the objetive range of the sumif (antipattern?) the 1 in GRIDSALES1 and "<=EoMonth("instead of Application.WorksheetFunction.EoMonth(grid_date, 0), that sunifs is has results as double it should be as:
鄙视“硬编码” sumif（反模式？）GRIDSALES1 中的 1 的目标范围，"<=EoMonth("而不是Application.WorksheetFunction.EoMonth(grid_date, 0)， sunifs 的结果应该是两倍：
Function ASALES(rev_date As Date, grid_date As Date) As Double
Dim Final_Price As Range
Dim Team As Range
Dim First_PD As Range
Application.Volatile (True)
Set Final_Price = Sheets("Sheet1").Range("H5:$H11")
Set Team = Sheets("Sheet1").Range("E5:E11")
Set First_PD = Sheets("Sheet1").Range("F5:F11")
ASALES = Application.WorksheetFunction.SumIfs( _
Final_Price _
, Team, "<>9" _
, First_PD, ">=" & rev_date _
, First_PD, "<=" & Application.WorksheetFunction.EoMonth(grid_date, 0))
End Function
回答by Excellll
Your output should be the function name. Change GRIDSALES1 to GRIDSALES.
您的输出应该是函数名称。将 GRIDSALES1 更改为 GRIDSALES。
