VBA SUMPRODUCT not same as worksheet SUMPRODUCT

[Yamezz]

I have Quantities in column Q, Unit Prices in column U and Account Codes in column T

On the worksheet I can successfully sum the total of each Account Code via =SUMPRODUCT(--(T2:T44="200-002"),Q2:Q44,R2:R44), but when I try the same thing in VBA, I get a type mismatch error.

XeroWETRetail = Application.WorksheetFunction.SumProduct(--(Xero.Sheets("Xero Sales Invoice").Range("T2:T" & XeroRow) = "200-002"), _
 Xero.Sheets("Xero Sales Invoice").Range("Q2:Q" & XeroRow), Xero.Sheets("Xero Sales Invoice").Range("R2:R" & XeroRow))

What am I overlooking?

 

[Yamezz]

Alex, I am baffled. I confirm there are no errors as per your last post. I have just re-run the code for the umpteenth time and now it executes correctly. Nothing has changed - the input files for the code are exactly unmodified from their original form. I will continue on with my project and see what happens. Thanks for your help.

 

[Alex Blakenburg]

Thanks for the update. Let us know how you go.

 

[Yamezz]

Actually, I do have a further query on this. I need to run essentially the same SUMPRODUCT on the source data (as a check to see how different the source data is from the macro-generated data - there will be rounding errors I need to deal with later). The first EVALUATE snippet you gave me refers to a known sheet name in the workbook where the macro is stored, but the second instance needs to refer to SalesReport.Activesheet, a different workbook with a sheet name that will be different each time the file is downloaded. How do I refer to that location in the EVALUATE code?

 

[Alex Blakenburg]

If you want to run it on the activeworkbook on its active sheet, just don't reference the workbook or sheet and it will default to the activesheet eg
Note: You will need to do the same thing when you calculate the LastRow (in this currently called XeroRow) and you won't be able to refer to ThisWorkbook it would need to be the ActiveWorkbook.

XeroWETRetail = Evaluate("SUMPRODUCT(--(T2:T" & XeroRow & "=""200-002"")," & _
                             "Q2:Q" & XeroRow & ",R2:R" & XeroRow & ")")

 

[Yamezz]

Alex, if I was to step through a list of account codes to retrieve the SUMPRODUCT, how would I use a variable in this line of code? I have tried a few variations, but Excel is not happy with any of them. Something like

XeroWETRetail = Evaluate("SUMPRODUCT(--(T2:T" & XeroRow & "=" & AccCode")," & "Q2:Q" & XeroRow & ",R2:R" & XeroRow & ")")

 

[Alex Blakenburg]

Try this:

XeroWETRetail = Evaluate("SUMPRODUCT(--(T2:T" & XeroRow & "=""" & AccCode & """)," & _
                             "Q2:Q" & XeroRow & ",R2:R" & XeroRow & ")")

 

[Yamezz]

Thanks Alex. While implementing this, I received the same old type mismatch error, which only occurred sometimes, depending on where I was looking prior to pressing F8. I've since realised that immediately prior to the problem line of code, the focus of Excel was not on the correct workbook.
Now, onto my next problem! (New thread coming).