newparadigmz
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...
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
Capture.PNG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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").
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.
Might be something to do with regional settings or something like that - I'll have a look when I get a chance.
I wrote up the results of some extensive testing in this old EE thread: