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. |
Sumproduct & VBA
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))") |
Sumproduct & VBA
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. |
Sumproduct & VBA
"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$25001)*($F $2:$F$25000)) 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). |
Sumproduct & VBA
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. |
All times are GMT +1. The time now is 11:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com