D
DogLover
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
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