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]

You're welcome.

 

[RoryA]

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

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

Now I'm confused. It works just fine but it doesn't?

 

[Jonmo1]

It works fine after removing the £ symbols from the values being multiplied.
Even if those values remain as TEXT strings in the sheet.

OR, changing them to $, due to VBA being US centric.

 

[RoryA]

Oh I see. You hadn't mentioned that!

 

[Jonmo1]

Oh I see. You hadn't mentioned that!

Post #8

This may just be a regional thing, but when I open that book the values in G2:G10 are TEXT strings, not numbers.
TEXT*Nmber = #Value!

It appears to be the £ symbol.
If I remove those £ symbols, then your code works fine.

 

[RoryA]

But you quoted my post explaining why the original file didn't work (and that it would work if you changed the code to substitute the £ signs)

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

and stated that Evaluate did work, even though the whole thread was about the fact it didn't.

Anyway, it's all moot now.

 

[Jonmo1]

I read that to mean that evaluate(sumproduct) doesn't do implicit coercion.
But it DOES.
Even the code you posted that subsituted the £ was still doing implicit coercion.

I wouldn't say it's a moot point, but maybe the discussion doesn't belong in this thread anymore.

 

[RoryA]

My point was simply that exactly the same formula worked in the cell, but failed in Evaluate because within Evaluate it was not coercing the £ values to numbers. Yes, it will coerce plain numbers stored as text but that was not the issue.