Using SUMPRODUCT in VBA

[Guest]

I want to use the sumproduct function to calculate a value in my macro. I
need to use the "--" format of the function. Is this syntax correct?

myVar = application.worksheetfunction.sumproduct(--(myRange=OtherVar),
AnotherRange)

 

[Tom Ogilvy]

No. Sumproduct will not support this format in VBA. You have to use the
Evaluate function which acts as a virtual worksheet cell.

myVar = Evaluate("Sumproduct(--(" & myRange.Address & "=" & OtherVar & "),"
& AnotherRange.Address & ")"

 

[Guest]

Here is a bit more clarification...

When I run the macro, I get a type mismatch error. I've tried it with the
variable CurrQty defines as a single and as a variant. Both ways generate
same error.

For Each wkb In Application.Workbooks
If Left(wkb.Name, 3) = "sbt" Then Set DataWkb = wkb
Next wkb

Set DataWks = DataWkb.Worksheets(2)

IIFLastRow = IIFwks.Cells(Rows.Count, 1).End(xlUp).Row
DataLastRow = DataWks.Cells(Rows.Count, 1).End(xlUp).Row
With DataWks
Set StoreRng = .Range(.Cells(2, 1), .Cells(DataLastRow, 1))
Set ItemRng = .Range(.Cells(2, 2), .Cells(DataLastRow, 2))
Set QtyRng = .Range(.Cells(2, 4), .Cells(DataLastRow, 4))
End With

[intervening code snipped]

CurrQty = Application.WorksheetFunction. _
SumProduct(--(StoreRng = Store), --(ItemRng = Item), QtyRng)

'This line produces the error.

 

[Tom Ogilvy]

see my previous answer.

 

[Guest]

Thanks Tom. Still running into a problem though. I modified my code to the
following...

CurrQty = Evaluate("sumProduct(--(" & StoreRng.Address & "=" & _
Store & "), --(" & ItemRng.Address & "=" & Item & "), " & _
QtyRng.Address & ")")


The variables "Store" and "Item" are defined as Strings. StoreRng and
ItemRng each contain Strings (and empty cells). QtyRng contains integers
(and empty cells). When I Dim CurrQty as a Variant, the code executes, but
CurrQty returns #VALUE. When I step through the macro line by line and it
gets to the CurrQty line, if I hover over "CurrQty" in the editor it says
"CurrQty = Error 2015".

When I dim CurrQty as a single, I get a Type Mismatch Error on that line.
Would empty cells in the ranges cause this? When I hover over the
*rng.Address parts, I get the correct range address (which all are the same
dimensions), so I don't think that is causing the #VALUE error.

 

[Tom Ogilvy]

Check it in the immediate window:

set qtyRng = Range("D1200")
set StoreRng = Range("A1:A200")
set ItemRng = Range("C1:C200")
item = "A"
store = "ABCD"
? "sumProduct(--(" & StoreRng.Address & "=" & _
Store & "), --(" & ItemRng.Address & "=" & Item & "), " & _
QtyRng.Address & ")"

' Produces
sumProduct(--($A$1:$A$200=ABCD), --($C$1:$C$200=A), $D$1:$D$200)

so we see we are missing quotes around ABCD and A. So we add them:

"sumProduct(--(" & StoreRng.Address & "=""" & _
Store & """), --(" & ItemRng.Address & "=""" & Item & """),
" & _
QtyRng.Address & ")"

Now we test it again:

? "sumProduct(--(" & StoreRng.Address & "=""" & _
Store & """), --(" & ItemRng.Address & "=""" & Item & """),
" & _
QtyRng.Address & ")"

' Produces
sumProduct(--($A$1:$A$200="ABCD"), --($C$1:$C$200="A"), $D$1:$D$200)

so, looks good

CurrQty = Evaluate("sumProduct(--(" & StoreRng.Address & "=""" & _
Store & """), --(" & ItemRng.Address & "=""" & Item & """),
" & _
QtyRng.Address & ")")

--
Regards,
Tom Ogilvy