Avatar of newparadigmz
newparadigmz
Flag for United States of America asked on

Trying to write a SumProduct formula (using double negative method ) in VBA

I'm trying to recreate this formula in VBA and getting a "Type mismatch" error. Able to do it via R1C1 and then .filldown, but explicitly trying to avoid that has it's super slow in the real data set.

Can't figure out what I'm doing wrong. I've used this method in simpler SumIf formulas before...

Sub WriteFormulas()
    
    Dim rQnty As Range, rName As Range, rDate As Range
    Dim vQnty As Variant, vName As Variant, vDate As Variant
    Dim j As Double, dValue As Double

    With ThisWorkbook.Worksheets(1)
        Set rQnty = .Range("A2:A10")
        Set rName = .Range("B2:B10")
        Set rDate = .Range("C2:C10")
    End With
        
    vQnty = rQnty.Value
    vName = rName.Value
    vDate = rDate.Value
    
    For j = 1 To UBound(vQnty)
        dValue = WorksheetFunction.SumProduct((rQnty), --(rName = vName(j, 1)), --(rDate = vDate(j, 1)))
    Next j
   
    'Range("D2").FormulaR1C1 = "=SUMPRODUCT(RC[-3]:R[8]C[-3],--(RC[-2]:R[8]C[-2]=""John""),--(RC[-1]:R[8]C[-1]=""Jan""))"
   
End Sub

Open in new window

Capture.PNG
VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Norie

8/22/2022 - Mon
byundt

SUMPRODUCT does not work well in VBA. As flexible as it is in a worksheet formula, it must be handled with kid gloves in VBA.
  • VBA will not accept multiplying one parameter by another. You must separate each parameter with commas.
  • VBA will not accept Boolean expressions as one of the parameters. But you are able to use the Evaluate method to convert the Boolean expression into something that SUMPRODUCT will accept.

I wrote up the results of some extensive testing in this old EE thread:
ASKER CERTIFIED SOLUTION
Norie

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
newparadigmz

ASKER
Yes, that works! Thank You

byundt, the link to the N function was interesting but couldn't get it to work....

Norie, it doesn't seem to work when the Date column is actual dates, like 9/4/2019, even after format(date,"mm/dd/yyyy"). Any idea around that?
Norie

Strange, it worked for me.

Might be something to do with regional settings or something like that - I'll have a look when I get a chance.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck