Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ping - Bernie (or any other sage)
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""),--(D2:D1000),D2:D100)") MsgBox "Using the variable StartDate: " & Evaluate("SUMPRODUCT(--(A2:A100=--" & StartDate & "),--(A2:A100<=--""2005-06-30""), --(F2:F100=""Cleared""),--(D2:D1000),D2:D100)") 'What's going amiss here ? MsgBox "Using the variable EndDate: " & Evaluate("SUMPRODUCT(--(A2:A100=--""2005-06-01""),--(A2:A100<=--" & EndDate & "), --(F2:F100=""Cleared""),--(D2:D1000),D2:D100)") MsgBox "Using the both variables: " & Evaluate("SUMPRODUCT(--(A2:A100=--" & StartDate & "),--(A2:A100<=--" & EndDate & "), --(F2:F100=""Cleared""),--(D2:D1000),D2:D100)") End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PING: Bernie Deitrick - Calloway Golf Scoring | Excel Discussion (Misc queries) | |||
Bernie Deitrick | Excel Discussion (Misc queries) | |||
Thanks Bernie | Excel Programming | |||
Bernie My New Macro | Excel Worksheet Functions | |||
Bernie Deitrick | Excel Worksheet Functions |