bromy2004
asked on
SUMPRODUCT not working
Why isn't this working?
popvalue = WorksheetFunction.SumProdu ct((PopShe et.Range(" Values")) * (PopSheet.Range("RepNum") = PopRep) * (PopSheet.Range("BudgetNam e") = PopBudget) * (PopSheet.Range("Dates") = DateSerial(Year(PopDate), Month(PopDate), 1)))
PopDate=01/01/2010 as Date
PopRep=100 as String
PopBudget=DAY SALE as String
PopValue as Double
PopSheet as Worksheet
I keep getting a "Type Mismatch" error
All 4 Named Ranges are Valid.
Typing the Same function into Excel works.
Removing the Last 3 Sections is the only way i can get any sort of Value
popvalue = WorksheetFunction.SumProdu
PopDate=01/01/2010 as Date
PopRep=100 as String
PopBudget=DAY SALE as String
PopValue as Double
PopSheet as Worksheet
I keep getting a "Type Mismatch" error
All 4 Named Ranges are Valid.
Typing the Same function into Excel works.
Removing the Last 3 Sections is the only way i can get any sort of Value
popvalue = WorksheetFunction.SumProduct((PopSheet.Range("Values")) * (PopSheet.Range("RepNum") = PopRep) * (PopSheet.Range("BudgetName") = PopBudget) * (PopSheet.Range("Dates") = DateSerial(Year(PopDate), Month(PopDate), 1)))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you are going to use the Evaluate method, it is safer to use the evaluate method of the relevant worksheet, rather than the application, unless you have fully qualified all the range references in the formula with sheet names or you are sure that the right sheet is active.
FWIW
Rory
FWIW
Rory
ASKER
Is there a suitable VBA code sample that i could substitute instead of the SUMPRODUCT?
There's one at the end of Kevin's post?
ASKER
Without Using Evaluate.
with VBA Syntax
with VBA Syntax
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This should do you well:
PopValue = Application.Evaluate("SUMP RODUCT(Val ues*(PopSh eet!RepNum =" & PopRep & ")*(PopSheet!BudgetName=" & PopBudget & ")*(PopSheet!Dates=DateSer ial(" & Year(PopDate) & "," & Month(PopDate) & ",1))")
Kevin
PopValue = Application.Evaluate("SUMP
Kevin
ASKER
I ended up using
PopCommand = "SUMPRODUCT((" & PopSheet.Range("Values").Address & ") * (" & PopSheet.Range("RepNum").Address & "= " & Chr(34) & PopRep & Chr(34) & ") * (" & PopSheet.Range("BudgetName").Address & " = " & Chr(34) & PopBudget & Chr(34) & ") * (" & PopSheet.Range("Dates").Address & " = DATEVALUE(" & Chr(34) & DateSerial(Year(PopDate), Month(PopDate), 1) & Chr(34) & ")))"
PopValue = PopSheet.Evaluate(PopCommand)
Oh dang, the quotes!
PopValue = Application.Evaluate("SUMP RODUCT(Pop Sheet!Valu es*(PopShe et!RepNum= """ & PopRep & """)*(PopSheet!BudgetName= " & PopBudget & ")*(PopSheet!Dates=DateSer ial(" & Year(PopDate) & "," & Month(PopDate) & ",1))")
Kevin
PopValue = Application.Evaluate("SUMP
Kevin
(1) http://www.mrexcel.com/forum/showthread.php?t=65622
(2) http://www.ozgrid.com/forum/showthread.php?t=16044
Good luck