S
SA3214
Bernie,
I am still having trouble ... the first variable StartDate works fine but
when I try to use the second variable EndDate I get a zero result ... (see
code below) ... Can you identify the problem for please before I am
completely bald.
Regards & TIA
Jim Burton
------------------------------------
Sub WhyNotThis()
Dim StartDate As String
Dim EndDate As String
StartDate = DateAdd("d", -Day(Date) + 1, Date)
EndDate = DateAdd("m", 1, StartDate)
EndDate = DateAdd("d", -1, EndDate)
MsgBox "Start Date is " & StartDate & Chr(13) & Chr(13) & "End Date is " &
EndDate
MsgBox "Using your first code: " &
Evaluate("SUMPRODUCT(--(A2:A100>=--""2005-06-01""),--(A2:A100<=--""2005-06-30""),
--(F2:F100=""Cleared""),--(D2100>0),D2100)")
MsgBox "Using the variable StartDate: " &
Evaluate("SUMPRODUCT(--(A2:A100>=--" & StartDate &
"),--(A2:A100<=--""2005-06-30""), --(F2:F100=""Cleared""),--(D2100>0),D2100)")
'What's going amiss here ?
MsgBox "Using the variable EndDate: " &
Evaluate("SUMPRODUCT(--(A2:A100>=--""2005-06-01""),--(A2:A100<=--" & EndDate
& "), --(F2:F100=""Cleared""),--(D2100>0),D2100)")
MsgBox "Using the both variables: " & Evaluate("SUMPRODUCT(--(A2:A100>=--" &
StartDate & "),--(A2:A100<=--" & EndDate &
"), --(F2:F100=""Cleared""),--(D2100>0),D2100)")
End Sub
I am still having trouble ... the first variable StartDate works fine but
when I try to use the second variable EndDate I get a zero result ... (see
code below) ... Can you identify the problem for please before I am
completely bald.
Regards & TIA
Jim Burton
------------------------------------
Sub WhyNotThis()
Dim StartDate As String
Dim EndDate As String
StartDate = DateAdd("d", -Day(Date) + 1, Date)
EndDate = DateAdd("m", 1, StartDate)
EndDate = DateAdd("d", -1, EndDate)
MsgBox "Start Date is " & StartDate & Chr(13) & Chr(13) & "End Date is " &
EndDate
MsgBox "Using your first code: " &
Evaluate("SUMPRODUCT(--(A2:A100>=--""2005-06-01""),--(A2:A100<=--""2005-06-30""),
--(F2:F100=""Cleared""),--(D2100>0),D2100)")
MsgBox "Using the variable StartDate: " &
Evaluate("SUMPRODUCT(--(A2:A100>=--" & StartDate &
"),--(A2:A100<=--""2005-06-30""), --(F2:F100=""Cleared""),--(D2100>0),D2100)")
'What's going amiss here ?
MsgBox "Using the variable EndDate: " &
Evaluate("SUMPRODUCT(--(A2:A100>=--""2005-06-01""),--(A2:A100<=--" & EndDate
& "), --(F2:F100=""Cleared""),--(D2100>0),D2100)")
MsgBox "Using the both variables: " & Evaluate("SUMPRODUCT(--(A2:A100>=--" &
StartDate & "),--(A2:A100<=--" & EndDate &
"), --(F2:F100=""Cleared""),--(D2100>0),D2100)")
End Sub