Sumproduct & VBA

Hi all

I know there's been many threads regarding this subject, but I'm still
none the wiser as to why it is so difficult to get it to work
considering it can be selected from the (.) context menu.

I'm not a huge fan of nesting formula's, especially when it comes to
complex ones over hundreds/thousands of cells given the crappy
infrastructure I deal with, that said!

Can anyone give me a reasonably basic explanation as to why this does
not work as is throws up a Type Mismatch:

So as to give as clearer explanation as possible, here is what each
column has.

Column A = Year ( contains 2011 though to 2012 and running )
Column B = Month ( Numeric representation ( 1 = Jan ))
Columns F & G contain either a 0 or 1.


Sub Calc_Jan()

Dim Ssht As Worksheet, Tsht As Worksheet
Dim mySumA As Range, mySumB As Range, mySumF As Range, mySumG As Range
Dim c1 As Range, c2 As Range
Dim Anchor1 As Range, Anchor2 As Range
Dim myMth As String

Set Ssht = Sheets("KPI Input")
Set mySumA = Ssht.Range("$A$2:$A$2500") 'Numeric Cells
Set mySumB = Ssht.Range("$B$2:$B$2500") 'Numeric Cells
Set mySumF = Ssht.Range("$F$2:$F$2500") 'Numeric Cells
Set mySumG = Ssht.Range("$G$2:$G$2500") 'Numeric Cells

myMth = 1
Set Tsht = Sheets("KPI")
Set Anchor1 = Tsht.Range("$H$17:$H$21") 'Numeric Cells
Set Anchor2 = Tsht.Range("$H$24:$H$28") 'Numeric Cells

For Each c1 In Anchor1
If c1 <> "" Then
With c1
..Offset(0, 1).Value = WorksheetFunction.SumProduct((mySumA = c1) *
(mySumB = myMth) * (mySumF > 0))
End With
End If
Next c1

For Each c2 In Anchor2
If c2 <> "" Then
With c2
..Offset(0, 1).Value = WorksheetFunction.SumProduct((mySumA = c2) *
(mySumB = myMth) * (mySumG > 0))
End With
End If
Next c2

End Sub


TIA
Mick.

On Thursday, August 9, 2012 6:05:51 AM UTC-5, Living the Dream wrote:
> Hi all
>
>
>
> I know there's been many threads regarding this subject, but I'm still
>
> none the wiser as to why it is so difficult to get it to work
>
> considering it can be selected from the (.) context menu.
>
>
>
> I'm not a huge fan of nesting formula's, especially when it comes to
>
> complex ones over hundreds/thousands of cells given the crappy
>
> infrastructure I deal with, that said!
>
>
>
> Can anyone give me a reasonably basic explanation as to why this does
>
> not work as is throws up a Type Mismatch:
>
>
>
> So as to give as clearer explanation as possible, here is what each
>
> column has.
>
>
>
> Column A = Year ( contains 2011 though to 2012 and running )
>
> Column B = Month ( Numeric representation ( 1 = Jan ))
>
> Columns F & G contain either a 0 or 1.
>
>
>
>
>
> Sub Calc_Jan()
>
>
>
> Dim Ssht As Worksheet, Tsht As Worksheet
>
> Dim mySumA As Range, mySumB As Range, mySumF As Range, mySumG As Range
>
> Dim c1 As Range, c2 As Range
>
> Dim Anchor1 As Range, Anchor2 As Range
>
> Dim myMth As String
>
>
>
> Set Ssht = Sheets("KPI Input")
>
> Set mySumA = Ssht.Range("$A$2:$A$2500") 'Numeric Cells
>
> Set mySumB = Ssht.Range("$B$2:$B$2500") 'Numeric Cells
>
> Set mySumF = Ssht.Range("$F$2:$F$2500") 'Numeric Cells
>
> Set mySumG = Ssht.Range("$G$2:$G$2500") 'Numeric Cells
>
>
>
> myMth = 1
>
> Set Tsht = Sheets("KPI")
>
> Set Anchor1 = Tsht.Range("$H$17:$H$21") 'Numeric Cells
>
> Set Anchor2 = Tsht.Range("$H$24:$H$28") 'Numeric Cells
>
>
>
> For Each c1 In Anchor1
>
> If c1 <> "" Then
>
> With c1
>
> .Offset(0, 1).Value = WorksheetFunction.SumProduct((mySumA = c1) *
>
> (mySumB = myMth) * (mySumF > 0))
>
> End With
>
> End If
>
> Next c1
>
>
>
> For Each c2 In Anchor2
>
> If c2 <> "" Then
>
> With c2
>
> .Offset(0, 1).Value = WorksheetFunction.SumProduct((mySumA = c2) *
>
> (mySumB = myMth) * (mySumG > 0))
>
> End With
>
> End If
>
> Next c2
>
>
>
> End Sub
>
>
>
>
>
> TIA
>
> Mick.

Cuz you can't do it that way. Either put your formula in the range with the macro and convert to value or use evalueate. Example:

AN = Evaluate("SUMPRODUCT(--($A$2:$A$21=$A8),--($B$2:$B$21=$B8),--($C$2:$C$21=$C8))")

Thank you Don

I appreciate you pointing me in the right direction.

I'm still curious as to why MS included the (.)Sumproduct feature as
selectable after WorksheetFunction if it is not executable considering
it would have made life so much easier when declaring....

Thanks again..

Cheers
Mick.

"Living the Dream" > wrote:
> I'm still curious as to why MS included the (.)Sumproduct
> feature as selectable after WorksheetFunction if it is not
> executable considering it would have made life so much easier

You have a fundamental misunderstanding of who is providing which feature.

The general form of Excel SUMPRODUCT is:

SUMPRODUCT(array1, array2,....)

and that is exactly what WorksheetFunction.SumProduct implements in VBA.

When we write an Excel expression of the form:

SUMPRODUCT(($A$2:$A$2500=H17)*($B$2:$B$2500>1)*($F $2:$F$2500>0))

Excel constructs 3 arrays of logic values, combines those arrays by
multiplying element-by-element, and finally passes a single array to
SUMPRODUCT.

The point is: the implicit construction of arrays from expressions is an
Excel feature.

VBA does not have that sophisticated feature of implicitly constructing
arrays from expressions in that manner.

Instead, we must construct the arrays explicitly, if we do not want to rely
on the VBA Evaluate function, which relies on Excel.

And by the way, that gives rise to a much more efficient evaluation in some
cases.

For example, in your code snippet, the conceptual arrays (mySumB = myMth)
and (mySumF > 0) are invariant with respect to the loop. That is, they only
need to be evaluated one time outside the loop.

So you might write:

n = UBound(mySumB,1) ' mySumA, mySumB and mySumF must be same size
ReDim a1(1 to n) As Byte
ReDim a2(1 to n) As Byte
For i = 1 to n
' True is -1 in VBA, not 1 as in Excel
a1(i) = -(mySumB(i) = myMth)
a2(i) = -(mySumF(i) > 0)
Next

ReDim a3(1 to n) as Byte
For Each c1 in Anchor1
If c1 <> "" Then
For i = 1 to n
a3(i) = -(mySumA(i) = c1)
Next
c1.Offset(0,1) = WorksheetFunction.SumProduct(a1,a2,a3)
End If
Next

"Inconvenient"? Yes. But it is a limitation of the VBA language, not the
WorksheetFunction.SumProduct implementation.

------

PS: Unless you have other needs for the range variables, it would be much
more efficient if mySumA et al were variant arrays, not range variables. To
wit:

mySumA = Ssht.Range("$A$2:$A$2500")

Thus, you are accessing the range (and Excel) only once for each range.

Then you would reference mySumA(i,1) instead of mySumA(i).

Wow

Thank you Joe

This is a very detailed and comprehensive explanation, and code of which
I am looking forward to testing and hopefully implementing.

For the moment, Don's reply is working well, but I am ever keen to try
new things.

Cheers
Mick.