|
Sumproduct gives #Value! when answer=0
I have a SUMProduct formula that I wrote in VBA. It works fine until the
combination in the criteria is such where there are 0 records, rather than it returning a 0, it says #Value, but I'm not sure how to deal with it. Does anyone have an idea?? Here is my code. Function Kountifs(mPositionC, mBeginDateC, mEndDateC, mEntityC) As Long Dim mPositionCriteria As String Dim mBeginDateCriteria As Variant Dim mEndDateCriteria As Variant Dim mTimeCriteria As String Dim mEntityCriteria As String Dim mQuestion1Criteria As String Dim mTimeRange As Range Dim mPositionRange As Range Dim mOrientMoYrRange As Range Dim mEntityRange As Range Dim mQuestion1Range As Range Dim mFormula As String Dim mBegMo As Integer, mBegYr As Integer Dim mEndMo As Integer, mEndYr As Integer mPositionCriteria = mPositionC ' This line of Code allows automatic RECALCULATION mBeginDateCriteria = mBeginDateC mEndDateCriteria = mEndDateC mEntityCriteria = mEntityC ' Needed if Subroutine vs Functio, change to passing variable later 'mPositionCriteria = Worksheets("RFJ").Range("N6") 'mBeginDateCriteria = Worksheets("RFJ").Range("N8") 'mEndDateCriteria = Worksheets("RFJ").Range("N9") mBegMo = Month(mBeginDateCriteria) mBegYr = Year(mBeginDateCriteria) If Month(mEndDateCriteria) = 12 Then mEndMo = 1 mEndYr = Year(mBeginDateCriteria) + 1 Else mEndMo = Month(mEndDateCriteria) + 1 mEndYr = Year(mBeginDateCriteria) End If ' Set Criterias mBeginDateCriteria = "=" & "DATE(" & mBegYr & "," & mBegMo & ",1)" mEndDateCriteria = "<" & "DATE(" & mEndYr & "," & mEndMo & ",1)" mTimeCriteria = "=" & Chr(34) & "First day of employment (Time 1)" & Chr(34) If mPositionCriteria = "<" Then mPositionCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records Else mPositionCriteria = "=" & Chr(34) & mPositionCriteria & Chr(34) End If If mEntityCriteria = "<" Then mEntityCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records Else mEntityCriteria = "=" & Chr(34) & mEntityCriteria & Chr(34) End If mQuestion1Criteria = "<" & Chr(34) & "*" & Chr(34) With Worksheets("Data") Set mTimeRange = .Range("DataTime") Set mPositionRange = .Range("DataPosition") Set mOrientMoYrRange = .Range("DataOrientMoYr") Set mEntityRange = .Range("DataEntity") Set mQuestion1Range = .Range("DataQuestion1") mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & mTimeCriteria & ")," mFormula = mFormula & "--(" & mPositionRange.Address & mPositionCriteria & ")," mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mBeginDateCriteria & ")," mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mEndDateCriteria & ")," mFormula = mFormula & "--(" & mEntityRange.Address & mEntityCriteria & ")," mFormula = mFormula & "-- (" & mQuestion1Range.Address & mQuestion1Criteria & ") )" 'mFormula = mFormula & mQuestion1Range.Address & ")" 'This sums correctly Kountifs = .Evaluate(mFormula) If IsNull(Kountifs) Then MsgBox "Zero" End With If IsError(Kountifs) Then MsgBox "Error in evaluating" Else MsgBox Kountifs End If End Function |
Sumproduct gives #Value! when answer=0
=sumproduct() won't work that way.
I'd bet that you have an error somewhere in one of your fields in the worksheet. DogLover wrote: I have a SUMProduct formula that I wrote in VBA. It works fine until the combination in the criteria is such where there are 0 records, rather than it returning a 0, it says #Value, but I'm not sure how to deal with it. Does anyone have an idea?? Here is my code. Function Kountifs(mPositionC, mBeginDateC, mEndDateC, mEntityC) As Long Dim mPositionCriteria As String Dim mBeginDateCriteria As Variant Dim mEndDateCriteria As Variant Dim mTimeCriteria As String Dim mEntityCriteria As String Dim mQuestion1Criteria As String Dim mTimeRange As Range Dim mPositionRange As Range Dim mOrientMoYrRange As Range Dim mEntityRange As Range Dim mQuestion1Range As Range Dim mFormula As String Dim mBegMo As Integer, mBegYr As Integer Dim mEndMo As Integer, mEndYr As Integer mPositionCriteria = mPositionC ' This line of Code allows automatic RECALCULATION mBeginDateCriteria = mBeginDateC mEndDateCriteria = mEndDateC mEntityCriteria = mEntityC ' Needed if Subroutine vs Functio, change to passing variable later 'mPositionCriteria = Worksheets("RFJ").Range("N6") 'mBeginDateCriteria = Worksheets("RFJ").Range("N8") 'mEndDateCriteria = Worksheets("RFJ").Range("N9") mBegMo = Month(mBeginDateCriteria) mBegYr = Year(mBeginDateCriteria) If Month(mEndDateCriteria) = 12 Then mEndMo = 1 mEndYr = Year(mBeginDateCriteria) + 1 Else mEndMo = Month(mEndDateCriteria) + 1 mEndYr = Year(mBeginDateCriteria) End If ' Set Criterias mBeginDateCriteria = "=" & "DATE(" & mBegYr & "," & mBegMo & ",1)" mEndDateCriteria = "<" & "DATE(" & mEndYr & "," & mEndMo & ",1)" mTimeCriteria = "=" & Chr(34) & "First day of employment (Time 1)" & Chr(34) If mPositionCriteria = "<" Then mPositionCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records Else mPositionCriteria = "=" & Chr(34) & mPositionCriteria & Chr(34) End If If mEntityCriteria = "<" Then mEntityCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records Else mEntityCriteria = "=" & Chr(34) & mEntityCriteria & Chr(34) End If mQuestion1Criteria = "<" & Chr(34) & "*" & Chr(34) With Worksheets("Data") Set mTimeRange = .Range("DataTime") Set mPositionRange = .Range("DataPosition") Set mOrientMoYrRange = .Range("DataOrientMoYr") Set mEntityRange = .Range("DataEntity") Set mQuestion1Range = .Range("DataQuestion1") mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & mTimeCriteria & ")," mFormula = mFormula & "--(" & mPositionRange.Address & mPositionCriteria & ")," mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mBeginDateCriteria & ")," mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mEndDateCriteria & ")," mFormula = mFormula & "--(" & mEntityRange.Address & mEntityCriteria & ")," mFormula = mFormula & "-- (" & mQuestion1Range.Address & mQuestion1Criteria & ") )" 'mFormula = mFormula & mQuestion1Range.Address & ")" 'This sums correctly Kountifs = .Evaluate(mFormula) If IsNull(Kountifs) Then MsgBox "Zero" End With If IsError(Kountifs) Then MsgBox "Error in evaluating" Else MsgBox Kountifs End If End Function -- Dave Peterson |
Sumproduct gives #Value! when answer=0
On the worksheet, the =sumproduct evaluates to 0 which is correct in the case
where certain criterias are selected. I have the function built with my VBA code and it evaluates the exact same criteria, but returns #Value!. Not sure where I would have an error that would cause something like this. Can you tell me will the VBA code return a 0 if you choose a combination of multiple criteria where the count =0? "Dave Peterson" wrote: =sumproduct() won't work that way. I'd bet that you have an error somewhere in one of your fields in the worksheet. DogLover wrote: I have a SUMProduct formula that I wrote in VBA. It works fine until the combination in the criteria is such where there are 0 records, rather than it returning a 0, it says #Value, but I'm not sure how to deal with it. Does anyone have an idea?? Here is my code. Function Kountifs(mPositionC, mBeginDateC, mEndDateC, mEntityC) As Long Dim mPositionCriteria As String Dim mBeginDateCriteria As Variant Dim mEndDateCriteria As Variant Dim mTimeCriteria As String Dim mEntityCriteria As String Dim mQuestion1Criteria As String Dim mTimeRange As Range Dim mPositionRange As Range Dim mOrientMoYrRange As Range Dim mEntityRange As Range Dim mQuestion1Range As Range Dim mFormula As String Dim mBegMo As Integer, mBegYr As Integer Dim mEndMo As Integer, mEndYr As Integer mPositionCriteria = mPositionC ' This line of Code allows automatic RECALCULATION mBeginDateCriteria = mBeginDateC mEndDateCriteria = mEndDateC mEntityCriteria = mEntityC ' Needed if Subroutine vs Functio, change to passing variable later 'mPositionCriteria = Worksheets("RFJ").Range("N6") 'mBeginDateCriteria = Worksheets("RFJ").Range("N8") 'mEndDateCriteria = Worksheets("RFJ").Range("N9") mBegMo = Month(mBeginDateCriteria) mBegYr = Year(mBeginDateCriteria) If Month(mEndDateCriteria) = 12 Then mEndMo = 1 mEndYr = Year(mBeginDateCriteria) + 1 Else mEndMo = Month(mEndDateCriteria) + 1 mEndYr = Year(mBeginDateCriteria) End If ' Set Criterias mBeginDateCriteria = "=" & "DATE(" & mBegYr & "," & mBegMo & ",1)" mEndDateCriteria = "<" & "DATE(" & mEndYr & "," & mEndMo & ",1)" mTimeCriteria = "=" & Chr(34) & "First day of employment (Time 1)" & Chr(34) If mPositionCriteria = "<" Then mPositionCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records Else mPositionCriteria = "=" & Chr(34) & mPositionCriteria & Chr(34) End If If mEntityCriteria = "<" Then mEntityCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records Else mEntityCriteria = "=" & Chr(34) & mEntityCriteria & Chr(34) End If mQuestion1Criteria = "<" & Chr(34) & "*" & Chr(34) With Worksheets("Data") Set mTimeRange = .Range("DataTime") Set mPositionRange = .Range("DataPosition") Set mOrientMoYrRange = .Range("DataOrientMoYr") Set mEntityRange = .Range("DataEntity") Set mQuestion1Range = .Range("DataQuestion1") mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & mTimeCriteria & ")," mFormula = mFormula & "--(" & mPositionRange.Address & mPositionCriteria & ")," mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mBeginDateCriteria & ")," mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mEndDateCriteria & ")," mFormula = mFormula & "--(" & mEntityRange.Address & mEntityCriteria & ")," mFormula = mFormula & "-- (" & mQuestion1Range.Address & mQuestion1Criteria & ") )" 'mFormula = mFormula & mQuestion1Range.Address & ")" 'This sums correctly Kountifs = .Evaluate(mFormula) If IsNull(Kountifs) Then MsgBox "Zero" End With If IsError(Kountifs) Then MsgBox "Error in evaluating" Else MsgBox Kountifs End If End Function -- Dave Peterson . |
Sumproduct gives #Value! when answer=0
If the data is ok (no errors), then evaluating the =sumproduct() formula will
give you a number. Maybe you should add a line after you finish creating the mFormula string. Debug.print mFormula This will cause your formula to be printed in the immediate window of the VBE. Hit ctrl-g if you don't see it. Then look at the ranges on that data worksheet that are used in the formula. I still bet that there's something wrong there. If that doesn't help, post the formula you see in your immediate window. Maybe it'll help... DogLover wrote: On the worksheet, the =sumproduct evaluates to 0 which is correct in the case where certain criterias are selected. I have the function built with my VBA code and it evaluates the exact same criteria, but returns #Value!. Not sure where I would have an error that would cause something like this. Can you tell me will the VBA code return a 0 if you choose a combination of multiple criteria where the count =0? "Dave Peterson" wrote: =sumproduct() won't work that way. I'd bet that you have an error somewhere in one of your fields in the worksheet. DogLover wrote: I have a SUMProduct formula that I wrote in VBA. It works fine until the combination in the criteria is such where there are 0 records, rather than it returning a 0, it says #Value, but I'm not sure how to deal with it. Does anyone have an idea?? Here is my code. Function Kountifs(mPositionC, mBeginDateC, mEndDateC, mEntityC) As Long Dim mPositionCriteria As String Dim mBeginDateCriteria As Variant Dim mEndDateCriteria As Variant Dim mTimeCriteria As String Dim mEntityCriteria As String Dim mQuestion1Criteria As String Dim mTimeRange As Range Dim mPositionRange As Range Dim mOrientMoYrRange As Range Dim mEntityRange As Range Dim mQuestion1Range As Range Dim mFormula As String Dim mBegMo As Integer, mBegYr As Integer Dim mEndMo As Integer, mEndYr As Integer mPositionCriteria = mPositionC ' This line of Code allows automatic RECALCULATION mBeginDateCriteria = mBeginDateC mEndDateCriteria = mEndDateC mEntityCriteria = mEntityC ' Needed if Subroutine vs Functio, change to passing variable later 'mPositionCriteria = Worksheets("RFJ").Range("N6") 'mBeginDateCriteria = Worksheets("RFJ").Range("N8") 'mEndDateCriteria = Worksheets("RFJ").Range("N9") mBegMo = Month(mBeginDateCriteria) mBegYr = Year(mBeginDateCriteria) If Month(mEndDateCriteria) = 12 Then mEndMo = 1 mEndYr = Year(mBeginDateCriteria) + 1 Else mEndMo = Month(mEndDateCriteria) + 1 mEndYr = Year(mBeginDateCriteria) End If ' Set Criterias mBeginDateCriteria = "=" & "DATE(" & mBegYr & "," & mBegMo & ",1)" mEndDateCriteria = "<" & "DATE(" & mEndYr & "," & mEndMo & ",1)" mTimeCriteria = "=" & Chr(34) & "First day of employment (Time 1)" & Chr(34) If mPositionCriteria = "<" Then mPositionCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records Else mPositionCriteria = "=" & Chr(34) & mPositionCriteria & Chr(34) End If If mEntityCriteria = "<" Then mEntityCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records Else mEntityCriteria = "=" & Chr(34) & mEntityCriteria & Chr(34) End If mQuestion1Criteria = "<" & Chr(34) & "*" & Chr(34) With Worksheets("Data") Set mTimeRange = .Range("DataTime") Set mPositionRange = .Range("DataPosition") Set mOrientMoYrRange = .Range("DataOrientMoYr") Set mEntityRange = .Range("DataEntity") Set mQuestion1Range = .Range("DataQuestion1") mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & mTimeCriteria & ")," mFormula = mFormula & "--(" & mPositionRange.Address & mPositionCriteria & ")," mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mBeginDateCriteria & ")," mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mEndDateCriteria & ")," mFormula = mFormula & "--(" & mEntityRange.Address & mEntityCriteria & ")," mFormula = mFormula & "-- (" & mQuestion1Range.Address & mQuestion1Criteria & ") )" 'mFormula = mFormula & mQuestion1Range.Address & ")" 'This sums correctly Kountifs = .Evaluate(mFormula) If IsNull(Kountifs) Then MsgBox "Zero" End With If IsError(Kountifs) Then MsgBox "Error in evaluating" Else MsgBox Kountifs End If End Function -- Dave Peterson . -- Dave Peterson |
Sumproduct gives #Value! when answer=0
I think there is more than one problem. I found one where field was numeric
so I had to change the code to represent the correct syntax. Now, I have my hads full. I have 2 lines of code, each for string data. Specially, when I add either of these fields into my long mFormula, I get an error 13, Data Type error. One thing I've noted, is both strings have - hyphens in it. Are there any rules on what can be in string data for passing. ' mFormula = mFormula & "--(" & mStatusRange.Address & mStatusCriteria & ")," ' mFormula = mFormula & "--(" & mEntityRange.Address & mEntityCriteria & ")," Also, it seems that when I pull these out of the long formula and evaluate each with this shorter code, I am not seeing the error. mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & mTimeCriteria & ")," mFormula = mFormula & "--(" & mEntityRange.Address & mEntityCriteria & ")," mFormula = mFormula & "-- (" & mQuestion1Range.Address & mQuestion1Criteria & ") )" This is the entire formula: mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & mTimeCriteria & ")," mFormula = mFormula & "--(" & mPositionRange.Address & mPositionCriteria & ")," mFormula = mFormula & "--(" & mDeptNoRange.Address & mDeptNoCriteria & "), " mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mBeginDateCriteria & ")," mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mEndDateCriteria & ")," mFormula = mFormula & "--(" & mShiftRange.Address & mShiftCriteria & "), " mFormula = mFormula & "-- (" & mQuestion1Range.Address & mQuestion1Criteria & ") )" "Dave Peterson" wrote: If the data is ok (no errors), then evaluating the =sumproduct() formula will give you a number. Maybe you should add a line after you finish creating the mFormula string. Debug.print mFormula This will cause your formula to be printed in the immediate window of the VBE. Hit ctrl-g if you don't see it. Then look at the ranges on that data worksheet that are used in the formula. I still bet that there's something wrong there. If that doesn't help, post the formula you see in your immediate window. Maybe it'll help... DogLover wrote: On the worksheet, the =sumproduct evaluates to 0 which is correct in the case where certain criterias are selected. I have the function built with my VBA code and it evaluates the exact same criteria, but returns #Value!. Not sure where I would have an error that would cause something like this. Can you tell me will the VBA code return a 0 if you choose a combination of multiple criteria where the count =0? "Dave Peterson" wrote: =sumproduct() won't work that way. I'd bet that you have an error somewhere in one of your fields in the worksheet. DogLover wrote: I have a SUMProduct formula that I wrote in VBA. It works fine until the combination in the criteria is such where there are 0 records, rather than it returning a 0, it says #Value, but I'm not sure how to deal with it. Does anyone have an idea?? Here is my code. Function Kountifs(mPositionC, mBeginDateC, mEndDateC, mEntityC) As Long Dim mPositionCriteria As String Dim mBeginDateCriteria As Variant Dim mEndDateCriteria As Variant Dim mTimeCriteria As String Dim mEntityCriteria As String Dim mQuestion1Criteria As String Dim mTimeRange As Range Dim mPositionRange As Range Dim mOrientMoYrRange As Range Dim mEntityRange As Range Dim mQuestion1Range As Range Dim mFormula As String Dim mBegMo As Integer, mBegYr As Integer Dim mEndMo As Integer, mEndYr As Integer mPositionCriteria = mPositionC ' This line of Code allows automatic RECALCULATION mBeginDateCriteria = mBeginDateC mEndDateCriteria = mEndDateC mEntityCriteria = mEntityC ' Needed if Subroutine vs Functio, change to passing variable later 'mPositionCriteria = Worksheets("RFJ").Range("N6") 'mBeginDateCriteria = Worksheets("RFJ").Range("N8") 'mEndDateCriteria = Worksheets("RFJ").Range("N9") mBegMo = Month(mBeginDateCriteria) mBegYr = Year(mBeginDateCriteria) If Month(mEndDateCriteria) = 12 Then mEndMo = 1 mEndYr = Year(mBeginDateCriteria) + 1 Else mEndMo = Month(mEndDateCriteria) + 1 mEndYr = Year(mBeginDateCriteria) End If ' Set Criterias mBeginDateCriteria = "=" & "DATE(" & mBegYr & "," & mBegMo & ",1)" mEndDateCriteria = "<" & "DATE(" & mEndYr & "," & mEndMo & ",1)" mTimeCriteria = "=" & Chr(34) & "First day of employment (Time 1)" & Chr(34) If mPositionCriteria = "<" Then mPositionCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records Else mPositionCriteria = "=" & Chr(34) & mPositionCriteria & Chr(34) End If If mEntityCriteria = "<" Then mEntityCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records Else mEntityCriteria = "=" & Chr(34) & mEntityCriteria & Chr(34) End If mQuestion1Criteria = "<" & Chr(34) & "*" & Chr(34) With Worksheets("Data") Set mTimeRange = .Range("DataTime") Set mPositionRange = .Range("DataPosition") Set mOrientMoYrRange = .Range("DataOrientMoYr") Set mEntityRange = .Range("DataEntity") Set mQuestion1Range = .Range("DataQuestion1") mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & mTimeCriteria & ")," mFormula = mFormula & "--(" & mPositionRange.Address & mPositionCriteria & ")," mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mBeginDateCriteria & ")," mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mEndDateCriteria & ")," mFormula = mFormula & "--(" & mEntityRange.Address & mEntityCriteria & ")," mFormula = mFormula & "-- (" & mQuestion1Range.Address & mQuestion1Criteria & ") )" 'mFormula = mFormula & mQuestion1Range.Address & ")" 'This sums correctly Kountifs = .Evaluate(mFormula) If IsNull(Kountifs) Then MsgBox "Zero" End With If IsError(Kountifs) Then MsgBox "Error in evaluating" Else MsgBox Kountifs End If End Function -- Dave Peterson . -- Dave Peterson . |
Sumproduct gives #Value! when answer=0
I would create a simpler formula. Then I would share what that formula string
looks like. I don't have a guess right now. DogLover wrote: I think there is more than one problem. I found one where field was numeric so I had to change the code to represent the correct syntax. Now, I have my hads full. I have 2 lines of code, each for string data. Specially, when I add either of these fields into my long mFormula, I get an error 13, Data Type error. One thing I've noted, is both strings have - hyphens in it. Are there any rules on what can be in string data for passing. ' mFormula = mFormula & "--(" & mStatusRange.Address & mStatusCriteria & ")," ' mFormula = mFormula & "--(" & mEntityRange.Address & mEntityCriteria & ")," Also, it seems that when I pull these out of the long formula and evaluate each with this shorter code, I am not seeing the error. mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & mTimeCriteria & ")," mFormula = mFormula & "--(" & mEntityRange.Address & mEntityCriteria & ")," mFormula = mFormula & "-- (" & mQuestion1Range.Address & mQuestion1Criteria & ") )" This is the entire formula: mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & mTimeCriteria & ")," mFormula = mFormula & "--(" & mPositionRange.Address & mPositionCriteria & ")," mFormula = mFormula & "--(" & mDeptNoRange.Address & mDeptNoCriteria & "), " mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mBeginDateCriteria & ")," mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mEndDateCriteria & ")," mFormula = mFormula & "--(" & mShiftRange.Address & mShiftCriteria & "), " mFormula = mFormula & "-- (" & mQuestion1Range.Address & mQuestion1Criteria & ") )" "Dave Peterson" wrote: If the data is ok (no errors), then evaluating the =sumproduct() formula will give you a number. Maybe you should add a line after you finish creating the mFormula string. Debug.print mFormula This will cause your formula to be printed in the immediate window of the VBE. Hit ctrl-g if you don't see it. Then look at the ranges on that data worksheet that are used in the formula. I still bet that there's something wrong there. If that doesn't help, post the formula you see in your immediate window. Maybe it'll help... DogLover wrote: On the worksheet, the =sumproduct evaluates to 0 which is correct in the case where certain criterias are selected. I have the function built with my VBA code and it evaluates the exact same criteria, but returns #Value!. Not sure where I would have an error that would cause something like this. Can you tell me will the VBA code return a 0 if you choose a combination of multiple criteria where the count =0? "Dave Peterson" wrote: =sumproduct() won't work that way. I'd bet that you have an error somewhere in one of your fields in the worksheet. DogLover wrote: I have a SUMProduct formula that I wrote in VBA. It works fine until the combination in the criteria is such where there are 0 records, rather than it returning a 0, it says #Value, but I'm not sure how to deal with it. Does anyone have an idea?? Here is my code. Function Kountifs(mPositionC, mBeginDateC, mEndDateC, mEntityC) As Long Dim mPositionCriteria As String Dim mBeginDateCriteria As Variant Dim mEndDateCriteria As Variant Dim mTimeCriteria As String Dim mEntityCriteria As String Dim mQuestion1Criteria As String Dim mTimeRange As Range Dim mPositionRange As Range Dim mOrientMoYrRange As Range Dim mEntityRange As Range Dim mQuestion1Range As Range Dim mFormula As String Dim mBegMo As Integer, mBegYr As Integer Dim mEndMo As Integer, mEndYr As Integer mPositionCriteria = mPositionC ' This line of Code allows automatic RECALCULATION mBeginDateCriteria = mBeginDateC mEndDateCriteria = mEndDateC mEntityCriteria = mEntityC ' Needed if Subroutine vs Functio, change to passing variable later 'mPositionCriteria = Worksheets("RFJ").Range("N6") 'mBeginDateCriteria = Worksheets("RFJ").Range("N8") 'mEndDateCriteria = Worksheets("RFJ").Range("N9") mBegMo = Month(mBeginDateCriteria) mBegYr = Year(mBeginDateCriteria) If Month(mEndDateCriteria) = 12 Then mEndMo = 1 mEndYr = Year(mBeginDateCriteria) + 1 Else mEndMo = Month(mEndDateCriteria) + 1 mEndYr = Year(mBeginDateCriteria) End If ' Set Criterias mBeginDateCriteria = "=" & "DATE(" & mBegYr & "," & mBegMo & ",1)" mEndDateCriteria = "<" & "DATE(" & mEndYr & "," & mEndMo & ",1)" mTimeCriteria = "=" & Chr(34) & "First day of employment (Time 1)" & Chr(34) If mPositionCriteria = "<" Then mPositionCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records Else mPositionCriteria = "=" & Chr(34) & mPositionCriteria & Chr(34) End If If mEntityCriteria = "<" Then mEntityCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records Else mEntityCriteria = "=" & Chr(34) & mEntityCriteria & Chr(34) End If mQuestion1Criteria = "<" & Chr(34) & "*" & Chr(34) With Worksheets("Data") Set mTimeRange = .Range("DataTime") Set mPositionRange = .Range("DataPosition") Set mOrientMoYrRange = .Range("DataOrientMoYr") Set mEntityRange = .Range("DataEntity") Set mQuestion1Range = .Range("DataQuestion1") mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & mTimeCriteria & ")," mFormula = mFormula & "--(" & mPositionRange.Address & mPositionCriteria & ")," mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mBeginDateCriteria & ")," mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mEndDateCriteria & ")," mFormula = mFormula & "--(" & mEntityRange.Address & mEntityCriteria & ")," mFormula = mFormula & "-- (" & mQuestion1Range.Address & mQuestion1Criteria & ") )" 'mFormula = mFormula & mQuestion1Range.Address & ")" 'This sums correctly Kountifs = .Evaluate(mFormula) If IsNull(Kountifs) Then MsgBox "Zero" End With If IsError(Kountifs) Then MsgBox "Error in evaluating" Else MsgBox Kountifs End If End Function -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
Sumproduct gives #Value! when answer=0
The problem appears to be the LEN of the mFormula. Have you ever had the
string being evaluated longer than 255 characters. This appears to be the issue. I am not sure how to change my formula if I have 7/8 criteria to evaluate as T/F to count. "Dave Peterson" wrote: I would create a simpler formula. Then I would share what that formula string looks like. I don't have a guess right now. DogLover wrote: I think there is more than one problem. I found one where field was numeric so I had to change the code to represent the correct syntax. Now, I have my hads full. I have 2 lines of code, each for string data. Specially, when I add either of these fields into my long mFormula, I get an error 13, Data Type error. One thing I've noted, is both strings have - hyphens in it. Are there any rules on what can be in string data for passing. ' mFormula = mFormula & "--(" & mStatusRange.Address & mStatusCriteria & ")," ' mFormula = mFormula & "--(" & mEntityRange.Address & mEntityCriteria & ")," Also, it seems that when I pull these out of the long formula and evaluate each with this shorter code, I am not seeing the error. mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & mTimeCriteria & ")," mFormula = mFormula & "--(" & mEntityRange.Address & mEntityCriteria & ")," mFormula = mFormula & "-- (" & mQuestion1Range.Address & mQuestion1Criteria & ") )" This is the entire formula: mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & mTimeCriteria & ")," mFormula = mFormula & "--(" & mPositionRange.Address & mPositionCriteria & ")," mFormula = mFormula & "--(" & mDeptNoRange.Address & mDeptNoCriteria & "), " mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mBeginDateCriteria & ")," mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mEndDateCriteria & ")," mFormula = mFormula & "--(" & mShiftRange.Address & mShiftCriteria & "), " mFormula = mFormula & "-- (" & mQuestion1Range.Address & mQuestion1Criteria & ") )" "Dave Peterson" wrote: If the data is ok (no errors), then evaluating the =sumproduct() formula will give you a number. Maybe you should add a line after you finish creating the mFormula string. Debug.print mFormula This will cause your formula to be printed in the immediate window of the VBE. Hit ctrl-g if you don't see it. Then look at the ranges on that data worksheet that are used in the formula. I still bet that there's something wrong there. If that doesn't help, post the formula you see in your immediate window. Maybe it'll help... DogLover wrote: On the worksheet, the =sumproduct evaluates to 0 which is correct in the case where certain criterias are selected. I have the function built with my VBA code and it evaluates the exact same criteria, but returns #Value!. Not sure where I would have an error that would cause something like this. Can you tell me will the VBA code return a 0 if you choose a combination of multiple criteria where the count =0? "Dave Peterson" wrote: =sumproduct() won't work that way. I'd bet that you have an error somewhere in one of your fields in the worksheet. DogLover wrote: I have a SUMProduct formula that I wrote in VBA. It works fine until the combination in the criteria is such where there are 0 records, rather than it returning a 0, it says #Value, but I'm not sure how to deal with it. Does anyone have an idea?? Here is my code. Function Kountifs(mPositionC, mBeginDateC, mEndDateC, mEntityC) As Long Dim mPositionCriteria As String Dim mBeginDateCriteria As Variant Dim mEndDateCriteria As Variant Dim mTimeCriteria As String Dim mEntityCriteria As String Dim mQuestion1Criteria As String Dim mTimeRange As Range Dim mPositionRange As Range Dim mOrientMoYrRange As Range Dim mEntityRange As Range Dim mQuestion1Range As Range Dim mFormula As String Dim mBegMo As Integer, mBegYr As Integer Dim mEndMo As Integer, mEndYr As Integer mPositionCriteria = mPositionC ' This line of Code allows automatic RECALCULATION mBeginDateCriteria = mBeginDateC mEndDateCriteria = mEndDateC mEntityCriteria = mEntityC ' Needed if Subroutine vs Functio, change to passing variable later 'mPositionCriteria = Worksheets("RFJ").Range("N6") 'mBeginDateCriteria = Worksheets("RFJ").Range("N8") 'mEndDateCriteria = Worksheets("RFJ").Range("N9") mBegMo = Month(mBeginDateCriteria) mBegYr = Year(mBeginDateCriteria) If Month(mEndDateCriteria) = 12 Then mEndMo = 1 mEndYr = Year(mBeginDateCriteria) + 1 Else mEndMo = Month(mEndDateCriteria) + 1 mEndYr = Year(mBeginDateCriteria) End If ' Set Criterias mBeginDateCriteria = "=" & "DATE(" & mBegYr & "," & mBegMo & ",1)" mEndDateCriteria = "<" & "DATE(" & mEndYr & "," & mEndMo & ",1)" mTimeCriteria = "=" & Chr(34) & "First day of employment (Time 1)" & Chr(34) If mPositionCriteria = "<" Then mPositionCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records Else mPositionCriteria = "=" & Chr(34) & mPositionCriteria & Chr(34) End If If mEntityCriteria = "<" Then mEntityCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records Else mEntityCriteria = "=" & Chr(34) & mEntityCriteria & Chr(34) End If mQuestion1Criteria = "<" & Chr(34) & "*" & Chr(34) With Worksheets("Data") Set mTimeRange = .Range("DataTime") Set mPositionRange = .Range("DataPosition") Set mOrientMoYrRange = .Range("DataOrientMoYr") Set mEntityRange = .Range("DataEntity") Set mQuestion1Range = .Range("DataQuestion1") mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & mTimeCriteria & ")," mFormula = mFormula & "--(" & mPositionRange.Address & mPositionCriteria & ")," mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mBeginDateCriteria & ")," mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mEndDateCriteria & ")," mFormula = mFormula & "--(" & mEntityRange.Address & mEntityCriteria & ")," mFormula = mFormula & "-- (" & mQuestion1Range.Address & mQuestion1Criteria & ") )" 'mFormula = mFormula & mQuestion1Range.Address & ")" 'This sums correctly Kountifs = .Evaluate(mFormula) If IsNull(Kountifs) Then MsgBox "Zero" End With If IsError(Kountifs) Then MsgBox "Error in evaluating" Else MsgBox Kountifs End If End Function -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . |
Sumproduct gives #Value! when answer=0
If you're close to the 255 limit, you may be able to use:
mTimeRange.Address(0,0) instead of mTimeRange.Address (for all your variables) the .address(0,0) will not include those $ signs in the address. If that doesn't help, take a look at Dick Kusleika's site: http://www.dailydoseofexcel.com/arch...rmulas-in-vba/ It's a technique to workaround that .formulaarray length limit when populating a cell in a worksheet. I haven't tested using .evaluate(), but you can. The last suggestion would be to loop through the range and do the count yourself. DogLover wrote: The problem appears to be the LEN of the mFormula. Have you ever had the string being evaluated longer than 255 characters. This appears to be the issue. I am not sure how to change my formula if I have 7/8 criteria to evaluate as T/F to count. "Dave Peterson" wrote: I would create a simpler formula. Then I would share what that formula string looks like. I don't have a guess right now. DogLover wrote: I think there is more than one problem. I found one where field was numeric so I had to change the code to represent the correct syntax. Now, I have my hads full. I have 2 lines of code, each for string data. Specially, when I add either of these fields into my long mFormula, I get an error 13, Data Type error. One thing I've noted, is both strings have - hyphens in it. Are there any rules on what can be in string data for passing. ' mFormula = mFormula & "--(" & mStatusRange.Address & mStatusCriteria & ")," ' mFormula = mFormula & "--(" & mEntityRange.Address & mEntityCriteria & ")," Also, it seems that when I pull these out of the long formula and evaluate each with this shorter code, I am not seeing the error. mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & mTimeCriteria & ")," mFormula = mFormula & "--(" & mEntityRange.Address & mEntityCriteria & ")," mFormula = mFormula & "-- (" & mQuestion1Range.Address & mQuestion1Criteria & ") )" This is the entire formula: mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & mTimeCriteria & ")," mFormula = mFormula & "--(" & mPositionRange.Address & mPositionCriteria & ")," mFormula = mFormula & "--(" & mDeptNoRange.Address & mDeptNoCriteria & "), " mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mBeginDateCriteria & ")," mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mEndDateCriteria & ")," mFormula = mFormula & "--(" & mShiftRange.Address & mShiftCriteria & "), " mFormula = mFormula & "-- (" & mQuestion1Range.Address & mQuestion1Criteria & ") )" "Dave Peterson" wrote: If the data is ok (no errors), then evaluating the =sumproduct() formula will give you a number. Maybe you should add a line after you finish creating the mFormula string. Debug.print mFormula This will cause your formula to be printed in the immediate window of the VBE. Hit ctrl-g if you don't see it. Then look at the ranges on that data worksheet that are used in the formula. I still bet that there's something wrong there. If that doesn't help, post the formula you see in your immediate window. Maybe it'll help... DogLover wrote: On the worksheet, the =sumproduct evaluates to 0 which is correct in the case where certain criterias are selected. I have the function built with my VBA code and it evaluates the exact same criteria, but returns #Value!. Not sure where I would have an error that would cause something like this. Can you tell me will the VBA code return a 0 if you choose a combination of multiple criteria where the count =0? "Dave Peterson" wrote: =sumproduct() won't work that way. I'd bet that you have an error somewhere in one of your fields in the worksheet. DogLover wrote: I have a SUMProduct formula that I wrote in VBA. It works fine until the combination in the criteria is such where there are 0 records, rather than it returning a 0, it says #Value, but I'm not sure how to deal with it. Does anyone have an idea?? Here is my code. Function Kountifs(mPositionC, mBeginDateC, mEndDateC, mEntityC) As Long Dim mPositionCriteria As String Dim mBeginDateCriteria As Variant Dim mEndDateCriteria As Variant Dim mTimeCriteria As String Dim mEntityCriteria As String Dim mQuestion1Criteria As String Dim mTimeRange As Range Dim mPositionRange As Range Dim mOrientMoYrRange As Range Dim mEntityRange As Range Dim mQuestion1Range As Range Dim mFormula As String Dim mBegMo As Integer, mBegYr As Integer Dim mEndMo As Integer, mEndYr As Integer mPositionCriteria = mPositionC ' This line of Code allows automatic RECALCULATION mBeginDateCriteria = mBeginDateC mEndDateCriteria = mEndDateC mEntityCriteria = mEntityC ' Needed if Subroutine vs Functio, change to passing variable later 'mPositionCriteria = Worksheets("RFJ").Range("N6") 'mBeginDateCriteria = Worksheets("RFJ").Range("N8") 'mEndDateCriteria = Worksheets("RFJ").Range("N9") mBegMo = Month(mBeginDateCriteria) mBegYr = Year(mBeginDateCriteria) If Month(mEndDateCriteria) = 12 Then mEndMo = 1 mEndYr = Year(mBeginDateCriteria) + 1 Else mEndMo = Month(mEndDateCriteria) + 1 mEndYr = Year(mBeginDateCriteria) End If ' Set Criterias mBeginDateCriteria = "=" & "DATE(" & mBegYr & "," & mBegMo & ",1)" mEndDateCriteria = "<" & "DATE(" & mEndYr & "," & mEndMo & ",1)" mTimeCriteria = "=" & Chr(34) & "First day of employment (Time 1)" & Chr(34) If mPositionCriteria = "<" Then mPositionCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records Else mPositionCriteria = "=" & Chr(34) & mPositionCriteria & Chr(34) End If If mEntityCriteria = "<" Then mEntityCriteria = "<" & Chr(34) & "*" & Chr(34) ' ALL Records Else mEntityCriteria = "=" & Chr(34) & mEntityCriteria & Chr(34) End If mQuestion1Criteria = "<" & Chr(34) & "*" & Chr(34) With Worksheets("Data") Set mTimeRange = .Range("DataTime") Set mPositionRange = .Range("DataPosition") Set mOrientMoYrRange = .Range("DataOrientMoYr") Set mEntityRange = .Range("DataEntity") Set mQuestion1Range = .Range("DataQuestion1") mFormula = "SUMPRODUCT(--(" & mTimeRange.Address & mTimeCriteria & ")," mFormula = mFormula & "--(" & mPositionRange.Address & mPositionCriteria & ")," mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mBeginDateCriteria & ")," mFormula = mFormula & "--(" & mOrientMoYrRange.Address & mEndDateCriteria & ")," mFormula = mFormula & "--(" & mEntityRange.Address & mEntityCriteria & ")," mFormula = mFormula & "-- (" & mQuestion1Range.Address & mQuestion1Criteria & ") )" 'mFormula = mFormula & mQuestion1Range.Address & ")" 'This sums correctly Kountifs = .Evaluate(mFormula) If IsNull(Kountifs) Then MsgBox "Zero" End With If IsError(Kountifs) Then MsgBox "Error in evaluating" Else MsgBox Kountifs End If End Function -- Dave Peterson . -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
| All times are GMT +1. The time now is 12:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com