ExcelBanter - Ping - Bernie (or any other sage)

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Ping - Bernie (or any other sage) (https://www.excelbanter.com/excel-programming/332202-ping-bernie-any-other-sage.html)

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

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

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

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

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