CODEWITHSUNDEEP
vbaexcel
user21354
user21354

Reputation: 21

VBA coding for Sumproduct() function with condition and named column range

Excel 2013 I am trying to use my excel sumproduct function in VBA but no luck. I have two formulas one is without condition that work fine in VB but the other one with condition is not working. Here are the formulas

formula with no condition work fine in Excel 

=SUMPRODUCT(tblProduct[Qty],tblProduct[amount])

and its VBA code that working fine in VBA excel userform

vGrndTot = Application.SumProduct(Range("tblProduct[Qty]"), Range("tblProduct[amount]"))

Conditional SUMPRODUCT working fine in excel

=SUMPRODUCT(--(tblProduct[prodID]="XYZ"),tblProduct[Qty],tblProduct[amount])

and its VBA code that i am tying to use but it not work (error type missmatch)

vTot = Application.SumProduct(--(Range("tblProduct[prodID]") = "XYZ"), Range("tblProduct[Qty]"), Range("tblProduct[amount]"))

any hint what I am doing wrong ? I read so many question some said use evaluate (that too didn't work) some said use +0, or * 1 but i had no luck.

Upvotes: 0

Views: 2697

Answers (1)

user21354
user21354

Reputation: 21

It took my 2 days but finally I got it with 2 key point

to evaluate the formula must appears EXACTLY SAME as in the worksheet cell including doublequotes. and to have doublequotes it need to have 4 doublequotes, strange though (see the vbDoubleQuote value below).

here is the final solution 

' vProdID = coming from tblProdList one by one

vbDoubleQuote = """"

 vFormula2Evalaute = "=SUMPRODUCT(--(tblProduct[prodID]=" & vbDoubleQuote & vProdID & vbDoubleQuote & ") ,tblProduct[Qty],tblProduct[UP])"

MsgBox Evaluate(vFormula2Evalaute)

Upvotes: 0

Related Questions