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