Sumproduct & VBA

  • Thread starter Thread starter Living the Dream
  • Start date Start date
L

Living the Dream

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.
 
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.
Click to expand...

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 said:
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
Click to expand...

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.
 
Back
Top