| 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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Ping - Bernie (or any other sage)
Maybe it's the difference you (from the UK) and VBA treats dates (USA centric).
I think I'd stop using strings. Option Explicit Sub WhyNotThis() Dim StartDate As Date Dim EndDate As Date 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 the variable StartDate: " & _ Evaluate("SUMPRODUCT(--(A2:A100=" & StartDate & ")," _ & "--(A2:A100<=--""2005-06-30"")," _ & "--(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 If that doesn't help (untested with my USA settings)... Option Explicit Sub WhyNotThis() Dim StartDate As Date Dim EndDate As Date 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 the variable StartDate: " & _ Evaluate("SUMPRODUCT(--(A2:A100=" & clng(StartDate) & ")," _ & "--(A2:A100<=--""2005-06-30"")," _ & "--(F2:F100=""Cleared""),--(D2:D1000),D2:D100)") MsgBox "Using the both variables: " & _ Evaluate("SUMPRODUCT(--(A2:A100=" & clng(StartDate) & ")," _ & "--(A2:A100<=--" & clng(EndDate) & ")," _ & "--(F2:F100=""Cleared""),--(D2:D1000),D2:D100)") End Sub "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""),--(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 -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Ping - Bernie (or any other sage)
If you are looking at a specific month, would any ideas here help?
Sub Demo() Dim s As String Dim Dte As Date Dte = Date s = "Sumproduct(--(Year(A2:A100)=#), --(Month(A2:A100)=#), --(F2:F100=""Cleared""),--(D2:D100 0),D2:D100)" s = Replace(s, "#", Year(Dte), , 1) s = Replace(s, "#", Month(Dte), , 1) MsgBox Evaluate(s) End Sub HTH :) -- Dana DeLouis Win XP & Office 2003 "SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Ping - Bernie (or any other sage)
Thank you Dana,
At the moment I am developing the workbook and I suspect eventually I will need to be able to select any range of dates ... I am just using the month to test the syntax of the vba "Dana DeLouis" wrote in message ... If you are looking at a specific month, would any ideas here help? Sub Demo() Dim s As String Dim Dte As Date Dte = Date s = "Sumproduct(--(Year(A2:A100)=#), --(Month(A2:A100)=#), --(F2:F100=""Cleared""),--(D2:D100 0),D2:D100)" s = Replace(s, "#", Year(Dte), , 1) s = Replace(s, "#", Month(Dte), , 1) MsgBox Evaluate(s) End Sub HTH :) -- Dana DeLouis Win XP & Office 2003 "SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Ping - Bernie (or any other sage)
Thank you Dave,
You are absolutely right ... I think my version was recognising 01/06/2005 as 6th Jan 2005 and of course 30/06/2005 doesn't exist in US format. Using Clng(StartDate ) & Clng(EndDate) has solved the problem Regards & Many thanks Jim Burton "Dave Peterson" wrote in message ... Maybe it's the difference you (from the UK) and VBA treats dates (USA centric). I think I'd stop using strings. Option Explicit Sub WhyNotThis() Dim StartDate As Date Dim EndDate As Date 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 the variable StartDate: " & _ Evaluate("SUMPRODUCT(--(A2:A100=" & StartDate & ")," _ & "--(A2:A100<=--""2005-06-30"")," _ & "--(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 If that doesn't help (untested with my USA settings)... Option Explicit Sub WhyNotThis() Dim StartDate As Date Dim EndDate As Date 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 the variable StartDate: " & _ Evaluate("SUMPRODUCT(--(A2:A100=" & clng(StartDate) & ")," _ & "--(A2:A100<=--""2005-06-30"")," _ & "--(F2:F100=""Cleared""),--(D2:D1000),D2:D100)") MsgBox "Using the both variables: " & _ Evaluate("SUMPRODUCT(--(A2:A100=" & clng(StartDate) & ")," _ & "--(A2:A100<=--" & clng(EndDate) & ")," _ & "--(F2:F100=""Cleared""),--(D2:D1000),D2:D100)") End Sub "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""),--(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 -- Dave Peterson |
| Reply |
| 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 | |||
Linear Mode
