Using Sumproduct in VBA

[George J]

I am struggling with this - i think my text string is correct and when copying it to a cell to test, works okay, but I get error 2015 in VBA. I can't use sumif as the numbers are entered as text on the workbook.

prop.Offset(0, 2).Value = Application.Evaluate("=SumProduct((" & _
      Range(myUsedRange.Offset(0, 3), myUsedRange.Offset(0, 5)).Address & "=" _
      & Chr(34) & prop.Value & Chr(34) & ")*(" & myUsedRange.Offset(0, 6).Address & "))")

This is the code I have which returns #value on the workbook. When entering the text to be evaluated as a formula however: =SumProduct(($D$2:$F$2083="my text")*($G$2:$G$2083))
It seems to work fine, so not sure why an error is coming up.

Can anyone point me in the right direction?
thanks.

 

[Jonmo1]

The issue is that the formula version performs implicit coercion, but it doesn't do that within Evaluate.

Yes it does.
It's using the sumproduct function by itself in VBA that doesn't do the coercion

This doesn't work
x = Application.Sumproduct((range=criteria),ValueToSum)

This does
x = Evaluate("=SUMPRODUCT((range=criteria),ValueToSum)")

 

[George J]

Thanks for the help guys. Not sure I would ever have got there on my own.
I never thought about the £ making a difference in VBA as it worked in the formula.

Really appreciate it guys.

 

[Jonmo1]

You're welcome.

I don't see how it could have worked in a formula either.

Can you post a formula that worked in the same book that you posted ?

 

[RoryA]

FYI, I was using SUMPRODUCT within Evaluate.

The issue is, as I stated, that the conversion to numeric does not happen within the formula if you are passing the formula string to Evaluate. This appears to be due to the * operator failing when multiplying the strings. (you can't use the version of SumProduct you posted here because the criteria is a three column array, so you have to multiply)

 

[George J]

It should be in column Q
I was sending the formula from VBA to check that it was coming out as I thought and the formula seems to work.

 

[RoryA]

Can you post a formula that worked in the same book that you posted ?

The code in the workbook puts the formulas in there, and they do work.

 

[Jonmo1]

FYI, I was using SUMPRODUCT within Evaluate.

The issue is, as I stated, that the conversion to numeric does not happen within the formula if you are passing the formula string to Evaluate. This appears to be due to the * operator failing when multiplying the strings. (you can't use the version of SumProduct you posted here because the criteria is a three column array, so you have to multiply)

Seems to work Just fine. Even with the (multi column array)*(single column array)

 

[Jonmo1]

I see why the formula worked with £ in the formula, but the VBA evaluate did not..

VBA tends to be US centric, regardless of your Regional Settings.
It would seem that VBA doesn't recognize £ as a currency sympol, therefor the Text values could not be coerced into numbers.
But the FORMULA version DOES recognize £ as a currency symbol.

 

[Jonmo1]

It would seem that VBA doesn't recognize £ as a currency sympol, therefor the Text values could not be coerced into numbers.
But the FORMULA version DOES recognize £ as a currency symbol.

Confirmed,
I changed my regional settings to use £ as currency symbol
I get the same result you did, formula works but VBA evaluate does not.

Change it back to $ as currency symbol, then neither formula or VBA evaluate works, both return #value.
Until I remove the £ from column G, then both work.

 

[George J]

Good to know.

Great knowledge and assistance. Glad I was able to call on the A team.