VBA SUMPRODUCT not same as worksheet SUMPRODUCT

Yamezz

Active Member
Joined
Nov 22, 2006
Messages
367
Office Version
  1. 2019
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.
VBA Code:
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?
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Changing the code to
VBA Code:
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))
results in a compile error: Syntax error
 
Upvote 0
You might be better off using Evaluate like this:

Excel Formula:
XeroWETRetail = Evaluate("SUMPRODUCT(--('Xero Sales Invoice'!T2:T" & XeroRow & "=""200-002"")," & _
                             "'Xero Sales Invoice'!Q2:Q" & XeroRow & ",'Xero Sales Invoice'!R2:R" & XeroRow & ")")

The problem with using the WorksheetFunction.SUMPRODUCT is the

--(Xero.Sheets("Xero Sales Invoice").Range("T2:T" & XeroRow) = ""200-002"")

part. If you use WorksheetFunction, then everything inside it must be native VBA code, NOT spreadsheet formula syntax. You could get it to work by writing a loop going through the T2:Tx range, and making an array, but that's a bunch of extra work.
 
Upvote 0
Do you have any error values (#N/A, #REF! etc) in any of those 3 columns (R,Q or T) ?
ie Ctrl+F look for # using Look in: Values
 
Upvote 0
Do you have any error values (#N/A, #REF! etc) in any of those 3 columns (R,Q or T) ?
ie Ctrl+F look for # using Look in: Values
No I don't, but I also don't even get to run the code. When I go to step through, that's when the VBA editor presents me with the error.
 
Upvote 0
You seem to be referring to 2 different error conditions since a mismatch error indicates the code did run.
Show us your error message and what line is highlighted.
Also show us the entire Sub.
 
Upvote 0
Sorry, you're right. Here's the extent of the sub so far. It stops on the last line with a type mismatch error. Q and R are all numbers and T is all account codes as strings.
VBA Code:
Sub ShopifyToXeroConversion5()

Dim SalesFile As Variant, SalesReport As Workbook, Xero As Workbook, TypeFile As Variant, TypeReport As Workbook, InvoiceRows As Long, tmp As String
Dim PaymentFile As Variant, FirstOrder As String, LastOrder As String, PaymentReport As Workbook, StartReportDate As Long, EndReportDate As Long
Dim LastPayRow As Long, XeroRow As Long, ReportDate As Date, strReportDate As String, EndReportTime As Double, StartReportTime As Double, XeroWETRetail As Double
Dim TotalFees As Double, DUP As Double, TotalSales As Double, QtyZero As Integer

Application.ScreenUpdating = False

        'SELECT AND OPEN RELEVANT FILES
Set Xero = ThisWorkbook
Xero.Sheets("Temp").UsedRange.Clear
SalesFile = Application.GetOpenFilename(FileFilter:="CSV File (*.csv),*.csv", Title:="Choose a Shopify SALES report file to open")

If SalesFile = False Then
    Exit Sub
End If

PaymentFile = Application.GetOpenFilename(FileFilter:="CSV File (*.csv),*.csv", Title:="Choose a SHOPIFY PAYMENT TRANSACTIONS report file to open")

If PaymentFile = False Then
    Exit Sub
End If
                    
InvoiceRows = Xero.Sheets("Xero Sales Invoice").Cells.Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row

Xero.Sheets("Xero Sales Invoice").Range("A2", "AA" & InvoiceRows).Delete

Set SalesReport = Application.Workbooks.Open(SalesFile)
Set PaymentReport = Application.Workbooks.Open(PaymentFile) 'List of card transactions that constitute the payment (does not show cash or $0 sales)

Dim LastSalesRow As Long, LastSalesCol As Long, TempRowsA As Long, TempRowsB As Long, SKU As String, c As Range, d As Range, TempListA As Range, OutOfBalance As Currency
Dim TempListB As Range, Qty As Single, Paid As String, sFormula As String, sDiscFormula As String, WineDiscFormula As String, TotalInvoiceValue As Currency
Dim AccCode As String, DataRows As Long, Desc As String, TaxType As String, e As Range, rReportTime As Range, cell As Range, PayoutAmount As Currency
Dim FirstInvRow As Long, LastInvRow As Long, Cash As Currency, LastPbTrow As Long, LastTypeRow As Long

        'DETERMINE OR ENTER THE DATE THE INVOICE WILL BE WRITTEN
ReportDate = Mid(SalesFile, (InStr(10, SalesFile, "_")) + 1, 10)
ReportDate = Format(ReportDate, "dd/mm/yy")
strReportDate = Format(ReportDate, "dd mmmm yyyy")

        'FIND COORDINATES AND CONSTANTS TO BE USED LATER
LastSalesRow = SalesReport.ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
LastSalesCol = SalesReport.ActiveSheet.Cells.Find("*", SearchOrder:=xlByColumns, searchdirection:=xlPrevious).Column
LastPayRow = PaymentReport.ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row

SalesReport.ActiveSheet.Range("C2:C" & LastSalesRow).SpecialCells(xlCellTypeVisible).Copy 'Copy Product Codes
Xero.Sheets("Temp").Range("A1").PasteSpecial Paste:=xlPasteValues

Xero.Sheets("Temp").Range("$A$1:A" & Cells(Rows.Count, "A").End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlNo
    
TempRowsA = Xero.Sheets("Temp").Range("A" & Rows.Count).End(xlUp).Row
DataRows = Xero.Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
Set TempListA = Xero.Sheets("Temp").Range("A1", "A" & TempRowsA)

For Each c In TempListA
    SKU = c.Value
    Desc = Application.WorksheetFunction.VLookup(SKU, Xero.Sheets("Data").Range("A4", "D" & DataRows), 2, False)
    AccCode = Application.WorksheetFunction.VLookup(SKU, Xero.Sheets("Data").Range("A4", "D" & DataRows), 3, False)
    TaxType = Application.WorksheetFunction.VLookup(SKU, Xero.Sheets("Data").Range("A4", "D" & DataRows), 4, False)
    Qty = Application.WorksheetFunction.SumIfs(SalesReport.ActiveSheet.Range("D2 : D" & LastSalesRow), _
        SalesReport.ActiveSheet.Range("C2:C" & LastSalesRow), SKU, _
        SalesReport.ActiveSheet.Range("E2:E" & LastSalesRow), ">0")
    QtyZero = Application.WorksheetFunction.SumIfs(SalesReport.ActiveSheet.Range("D2 : D" & LastSalesRow), _
        SalesReport.ActiveSheet.Range("C2:C" & LastSalesRow), SKU, _
        SalesReport.ActiveSheet.Range("E2:E" & LastSalesRow), "=0")
    TotalSales = Application.WorksheetFunction.SumIf(SalesReport.ActiveSheet.Range("C2:C" & LastSalesRow), SKU, _
        SalesReport.ActiveSheet.Range("J2 :J" & LastSalesRow))
    DUP = TotalSales / Qty

    XeroRow = Xero.Sheets("Xero Sales Invoice").Cells.Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row
    If Qty > 0 Then
        Xero.Sheets("Xero Sales Invoice").Range("A" & XeroRow + 1) = "Daily Sales Shopify"
        Xero.Sheets("Xero Sales Invoice").Range("K" & XeroRow + 1) = "S" & Format(ReportDate, "yyyymmdd")
        Xero.Sheets("Xero Sales Invoice").Range("M" & XeroRow + 1) = strReportDate
        Xero.Sheets("Xero Sales Invoice").Range("N" & XeroRow + 1) = "'" & strReportDate
        Xero.Sheets("Xero Sales Invoice").Range("O" & XeroRow + 1) = SKU
        Xero.Sheets("Xero Sales Invoice").Range("Q" & XeroRow + 1) = Qty
        Xero.Sheets("Xero Sales Invoice").Range("R" & XeroRow + 1) = Round(DUP, 2)
        Xero.Sheets("Xero Sales Invoice").Range("P" & XeroRow + 1) = Desc
        Xero.Sheets("Xero Sales Invoice").Range("T" & XeroRow + 1) = AccCode
        Xero.Sheets("Xero Sales Invoice").Range("U" & XeroRow + 1) = TaxType
    End If
            If QtyZero > 0 Then
        XeroRow = Xero.Sheets("Xero Sales Invoice").Cells.Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row
        Xero.Sheets("Xero Sales Invoice").Range("A" & XeroRow + 1) = "Daily Sales Shopify"
        Xero.Sheets("Xero Sales Invoice").Range("K" & XeroRow + 1) = "S" & Format(ReportDate, "yyyymmdd")
        Xero.Sheets("Xero Sales Invoice").Range("M" & XeroRow + 1) = strReportDate
        Xero.Sheets("Xero Sales Invoice").Range("N" & XeroRow + 1) = "'" & strReportDate
        Xero.Sheets("Xero Sales Invoice").Range("O" & XeroRow + 1) = SKU
        Xero.Sheets("Xero Sales Invoice").Range("Q" & XeroRow + 1) = QtyZero
        Xero.Sheets("Xero Sales Invoice").Range("R" & XeroRow + 1) = 0
        Xero.Sheets("Xero Sales Invoice").Range("P" & XeroRow + 1) = Desc
        Xero.Sheets("Xero Sales Invoice").Range("T" & XeroRow + 1) = AccCode
        Xero.Sheets("Xero Sales Invoice").Range("U" & XeroRow + 1) = TaxType
    End If
Next c

'Apply Fees to Xero Invoice
TotalFees = Application.WorksheetFunction.Sum(PaymentReport.ActiveSheet.Range("J2:J" & LastPayRow))
XeroRow = Xero.Sheets("Xero Sales Invoice").Cells.Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row
Xero.Sheets("Xero Sales Invoice").Range("A" & XeroRow + 1) = "Daily Sales Shopify"
Xero.Sheets("Xero Sales Invoice").Range("K" & XeroRow + 1) = "S" & Format(ReportDate, "yyyymmdd")
Xero.Sheets("Xero Sales Invoice").Range("M" & XeroRow + 1) = strReportDate
Xero.Sheets("Xero Sales Invoice").Range("N" & XeroRow + 1) = "'" & strReportDate
Xero.Sheets("Xero Sales Invoice").Range("O" & XeroRow + 1) = "SHOPIFYFEES"
Xero.Sheets("Xero Sales Invoice").Range("P" & XeroRow + 1) = "Shopify Credit Card Commission Fees"
Xero.Sheets("Xero Sales Invoice").Range("Q" & XeroRow + 1) = 1
Xero.Sheets("Xero Sales Invoice").Range("R" & XeroRow + 1) = -TotalFees
Xero.Sheets("Xero Sales Invoice").Range("T" & XeroRow + 1) = "SH-29999"
Xero.Sheets("Xero Sales Invoice").Range("U" & XeroRow + 1) = "GST on Expenses"

XeroRow = Xero.Sheets("Xero Sales Invoice").Cells.Find("*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row 'Update last Xero Invoice row

'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))
XeroWETRetail = Evaluate("SUMPRODUCT(--('Xero Sales Invoice'!T2:T" & XeroRow & "=""200-002"")," & _
                             "'Xero Sales Invoice'!Q2:Q" & XeroRow & ",'Xero Sales Invoice'!R2:R" & XeroRow & ")")
 
Upvote 0
The only way that I can get that line to generate the mismatch error is to have a cell in an error value in one of those 3 columns.

Please make sure you have no filters applied and no hidden rows and then try using Find, look for # and makes sure Values are selected in the last box and Match entire cell contents is unticked.

1716123217421.png
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,201
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top