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.

 

[Marcelo Branco]

What is the active sheet when the sub reaches the Evaluate code line?

For testing purposes, assuming data in Sheet1 (adjust the sheet name to suit), see if this works

prop.Offset(0, 2).Value = Application.Evaluate("=SumProduct(([COLOR=#ff0000]Sheet1![/COLOR]" & _
      Range(myUsedRange.Offset(0, 3), myUsedRange.Offset(0, 5)).Address & "=" _
      & Chr(34) & prop.Value & Chr(34) & ")*([COLOR=#ff0000]Sheet1![/COLOR]" & myUsedRange.Offset(0, 6).Address & "))")

Hope this helps

M.

 

[George J]

Thanks for the reply Marcelo.

I have added the sheet name and when stepping through, make sure the sheet is selected.
It still comes out as #value
I put prop.Offset(0, 3).formula = "=SumProduct ... etc
But this comes out correct with the formula in the cell - so why will it not evaluate from vba??

 

[Norie]

George

It works for me with some simple data.

Are there any error values in the data involved in the formula?

 

[RoryA]

Does this work:

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

 

[Jonmo1]

Check that the formula is being constructed as you think it is..

Run it this way, and look in the immediate window for the resulting text string.

Debug.Print "=SumProduct((Sheet1!" & _
      Range(myUsedRange.Offset(0, 3), myUsedRange.Offset(0, 5)).Address & "=" _
      & Chr(34) & prop.Value & Chr(34) & ")*(Sheet1!" & myUsedRange.Offset(0, 6).Address & "))"

 

[George J]

Thanks for the replies guys.

I put this into a smaller workbook with test code to make sure there was not something I was missing with the amount of data. It still comes out the same processing 9 rows. Not sure if this is a formatting issue, but there are not error values within the data and no error messages come up. I got the error 2015 when using the immediate window to see what was happening.

I tried RoryA's suggestion, but the result is the same.

Here is a link to my example workbook. (Data on sheet 2)
https://drive.google.com/file/d/0B1MotIND4aIDcFY2STBGdmJkZGc/view?usp=sharing

 

[Jonmo1]

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.

 

[Norie]

They are also Numbers stored as Text for me and I'm in the same region.

 

[RoryA]

The issue is that the formula version performs implicit coercion, but it doesn't do that within Evaluate. You can fix it by using:

        sFormula = "=SumProduct((" & Range(myUsedRange.Offset(0, 3), myUsedRange.Offset(0, 5)).Address & "=" _
       & prop.Address & ")*(SUBSTITUTE(" & myUsedRange.Offset(0, 6).Address & ",""£"","""")))"
    prop.Offset(0, 2).Value = myUsedRange.Worksheet.Evaluate(sFormula)