Many thanks for your help in previous thread,

Now I would like to replace some of the criteria with variables but I am
stumped with the dates ... e.g. in the code below I would like to replace
"2005-06-01" with StartDate a variable which holds the value of the first
day of the current month

StartDate = Format(DateAdd("d", -Day(Now() - 1), Now()), "yyyy-mm-dd")

However many permutations of Cdate, Cstr, Format etc I cannot get it to work
correctly would you mind showing me how it can be done

............ original code ............

evaluate("SUMPRODUCT(--(A2:A100=--""2005-06-01""),--(A2:A100<=--""2005-06-30""),
--(F2:F100=""Cleared""),--(D2:D1000),D2:D100)")


Thanks again

=Date - Day(Date) + 1


--
HTH
Roger
Shaftesbury (UK)
(A few miles further north than Bob P, and a few billion less brain cells)



"Jimbo" wrote in message
...
Many thanks for your help in previous thread,

Now I would like to replace some of the criteria with variables but I am
stumped with the dates ... e.g. in the code below I would like to replace
"2005-06-01" with StartDate a variable which holds the value of the first
day of the current month

StartDate = Format(DateAdd("d", -Day(Now() - 1), Now()), "yyyy-mm-dd")

However many permutations of Cdate, Cstr, Format etc I cannot get it to
work correctly would you mind showing me how it can be done

........... original code ............

evaluate("SUMPRODUCT(--(A2:A100=--""2005-06-01""),--(A2:A100<=--""2005-06-30""),
--(F2:F100=""Cleared""),--(D2:D1000),D2:D100)")


Thanks again

Roger,
I'm not sure what you are suggesting ... Did you intend that your code was
to be used to establish the value of the variable StartDate or as a direct
substitute in the line of vbaCode.

In either case I would be grateful if you would reproduce the code and also
the equivalent for a variable EndDate (the end of current month)

My apologies if I am appearring negative or 'thick' its because I'm becoming
more confused by the hour

Regards



"Roger Whitehead" wrote in message
...
=Date - Day(Date) + 1


--
HTH
Roger
Shaftesbury (UK)
(A few miles further north than Bob P, and a few billion less brain cells)



"Jimbo" wrote in message
...
Many thanks for your help in previous thread,

Now I would like to replace some of the criteria with variables but I am
stumped with the dates ... e.g. in the code below I would like to replace
"2005-06-01" with StartDate a variable which holds the value of the first
day of the current month

StartDate = Format(DateAdd("d", -Day(Now() - 1), Now()), "yyyy-mm-dd")

However many permutations of Cdate, Cstr, Format etc I cannot get it to
work correctly would you mind showing me how it can be done

........... original code ............

evaluate("SUMPRODUCT(--(A2:A100=--""2005-06-01""),--(A2:A100<=--""2005-06-30""),
--(F2:F100=""Cleared""),--(D2:D1000),D2:D100)")


Thanks again

Jimbo,

The correct way to do that is:

MsgBox Evaluate("SUMPRODUCT(--(A2:A100=--" & startdate &
"),--(A2:A100<=--""2005-06-30""),
--(F2:F100=""Cleared""),--(D2:D1000),D2:D100)")

HTH,
Bernie
MS Excel MVP


"Jimbo" wrote in message
...
Many thanks for your help in previous thread,

Now I would like to replace some of the criteria with variables but I am
stumped with the dates ... e.g. in the code below I would like to replace
"2005-06-01" with StartDate a variable which holds the value of the first
day of the current month

StartDate = Format(DateAdd("d", -Day(Now() - 1), Now()), "yyyy-mm-dd")

However many permutations of Cdate, Cstr, Format etc I cannot get it to
work
correctly would you mind showing me how it can be done

........... original code ............


evaluate("SUMPRODUCT(--(A2:A100=--""2005-06-01""),--(A2:A100<=--""2005-06-3
0""),
--(F2:F100=""Cleared""),--(D2:D1000),D2:D100)")


Thanks again

Jimbo,

I think he was suggesting a way to calculate the first day of the month. Not
very useful, since you are already doing that.

HTH,
Bernie
MS Excel MVP


"Jimbo" wrote in message
...
Roger,
I'm not sure what you are suggesting ... Did you intend that your code was
to be used to establish the value of the variable StartDate or as a direct
substitute in the line of vbaCode.

In either case I would be grateful if you would reproduce the code and
also
the equivalent for a variable EndDate (the end of current month)

My apologies if I am appearring negative or 'thick' its because I'm
becoming
more confused by the hour

Regards

Jimbo,

Of course, you don't need the "Msgbox" part, and the line wrapping occurred
at a bad place.

Basically, replace

--(A2:A100=--""2005-06-01"")

with

--(A2:A100=--" & startdate & ")

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Jimbo,

The correct way to do that is:

MsgBox Evaluate("SUMPRODUCT(--(A2:A100=--" & startdate &
"),--(A2:A100<=--""2005-06-30""),
--(F2:F100=""Cleared""),--(D2:D1000),D2:D100)")

HTH,
Bernie
MS Excel MVP


"Jimbo" wrote in message
...
Many thanks for your help in previous thread,

Now I would like to replace some of the criteria with variables but I am
stumped with the dates ... e.g. in the code below I would like to
replace
"2005-06-01" with StartDate a variable which holds the value of the
first
day of the current month

StartDate = Format(DateAdd("d", -Day(Now() - 1), Now()), "yyyy-mm-dd")

However many permutations of Cdate, Cstr, Format etc I cannot get it to
work
correctly would you mind showing me how it can be done

........... original code ............



evaluate("SUMPRODUCT(--(A2:A100=--""2005-06-01""),--(A2:A100<=--""2005-06-3
0""),
--(F2:F100=""Cleared""),--(D2:D1000),D2:D100)")


Thanks again

Thanks for your input Bernie ... I had tried that approach prior to starting
this thread but it doesn't appear to work ...
... hence my fiddling about with Format, Cdate, Cstr etc since the original
code (which works) contains a string representing the first of June 2005
albeit in yyyy-mm-dd format.
Where am I goijng wrong ?

Regards
Jimbo

I always seem to have trouble with dates ...



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Jimbo,

The correct way to do that is:

MsgBox Evaluate("SUMPRODUCT(--(A2:A100=--" & startdate &
"),--(A2:A100<=--""2005-06-30""),
--(F2:F100=""Cleared""),--(D2:D1000),D2:D100)")

HTH,
Bernie
MS Excel MVP


"Jimbo" wrote in message
...
Many thanks for your help in previous thread,

Now I would like to replace some of the criteria with variables but I am
stumped with the dates ... e.g. in the code below I would like to replace
"2005-06-01" with StartDate a variable which holds the value of the first
day of the current month

StartDate = Format(DateAdd("d", -Day(Now() - 1), Now()), "yyyy-mm-dd")

However many permutations of Cdate, Cstr, Format etc I cannot get it to
work
correctly would you mind showing me how it can be done

........... original code ............


evaluate("SUMPRODUCT(--(A2:A100=--""2005-06-01""),--(A2:A100<=--""2005-06-3
0""),
--(F2:F100=""Cleared""),--(D2:D1000),D2:D100)")


Thanks again

Jimbo, my mistake (and an under-estimate by a few billion brain cells - as
Bernie has kindly confirmed.....!)

If I understand correctly, replace your ""2005-06-30"" with

" & StartDate & "

Then copy the following into a standard module.....

Function StartDate() As String
StartDate = Format(Date - Day(Date) + 1, "yyyy-mm-dd")
End Function

Then, for EndDate:

Function EndDate() As String
If Month(Date) = 12 Then
EndDate = Year(Date) + 1 & "-01-" & Format(Day(Date - Day(Date)
+ 1), "00")
Else
EndDate = Year(Date) & "-" & Format(Month(Date) + 1, "00") & "-"
& Format(Day(Date - Day(Date) + 1), "00")
End If
EndDate = CDate(EndDate) - 1
MsgBox EndDate
End Function


HTH
Roger




"Jimbo" wrote in message
...
Roger,
I'm not sure what you are suggesting ... Did you intend that your code was
to be used to establish the value of the variable StartDate or as a direct
substitute in the line of vbaCode.

In either case I would be grateful if you would reproduce the code and
also the equivalent for a variable EndDate (the end of current month)

My apologies if I am appearring negative or 'thick' its because I'm
becoming more confused by the hour

Regards



"Roger Whitehead" wrote in message
...
=Date - Day(Date) + 1


--
HTH
Roger
Shaftesbury (UK)
(A few miles further north than Bob P, and a few billion less brain
cells)



"Jimbo" wrote in message
...
Many thanks for your help in previous thread,

Now I would like to replace some of the criteria with variables but I am
stumped with the dates ... e.g. in the code below I would like to
replace "2005-06-01" with StartDate a variable which holds the value of
the first day of the current month

StartDate = Format(DateAdd("d", -Day(Now() - 1), Now()), "yyyy-mm-dd")

However many permutations of Cdate, Cstr, Format etc I cannot get it to
work correctly would you mind showing me how it can be done

........... original code ............

evaluate("SUMPRODUCT(--(A2:A100=--""2005-06-01""),--(A2:A100<=--""2005-06-30""),
--(F2:F100=""Cleared""),--(D2:D1000),D2:D100)")


Thanks again

Sorry, that's:

Sorry, that's:

Function EndDate() As String
If Month(Date) = 12 Then
EndDate = Year(Date) + 1 & "-01-" & Format(Day(Date - Day(Date)
+ 1), "00")
Else
EndDate = Year(Date) & "-" & Format(Month(Date) + 1, "00") & "-"
& Format(Day(Date - Day(Date) + 1), "00")
End If
EndDate = CDate(EndDate) - 1
EndDate = Format(EndDate, "yyyy-mm-dd")
End Function

Watch out for the wrap

Roger

"Jimbo" wrote in message
...
Roger,
I'm not sure what you are suggesting ... Did you intend that your code was
to be used to establish the value of the variable StartDate or as a direct
substitute in the line of vbaCode.

In either case I would be grateful if you would reproduce the code and
also the equivalent for a variable EndDate (the end of current month)

My apologies if I am appearring negative or 'thick' its because I'm
becoming more confused by the hour

Regards



"Roger Whitehead" wrote in message
...
=Date - Day(Date) + 1


--
HTH
Roger
Shaftesbury (UK)
(A few miles further north than Bob P, and a few billion less brain
cells)



"Jimbo" wrote in message
...
Many thanks for your help in previous thread,

Now I would like to replace some of the criteria with variables but I am
stumped with the dates ... e.g. in the code below I would like to
replace "2005-06-01" with StartDate a variable which holds the value of
the first day of the current month

StartDate = Format(DateAdd("d", -Day(Now() - 1), Now()), "yyyy-mm-dd")

However many permutations of Cdate, Cstr, Format etc I cannot get it to
work correctly would you mind showing me how it can be done

........... original code ............

evaluate("SUMPRODUCT(--(A2:A100=--""2005-06-01""),--(A2:A100<=--""2005-06-30""),
--(F2:F100=""Cleared""),--(D2:D1000),D2:D100)")


Thanks again

Jimbo,

The code worked for me in both its forms. I will send you a working version
privately.

HTH,
Bernie
MS Excel MVP


"Jimbo" wrote in message
...
Thanks for your input Bernie ... I had tried that approach prior to
starting
this thread but it doesn't appear to work ...
... hence my fiddling about with Format, Cdate, Cstr etc since the
original
code (which works) contains a string representing the first of June 2005
albeit in yyyy-mm-dd format.
Where am I goijng wrong ?

Regards
Jimbo

I always seem to have trouble with dates ...



"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Jimbo,

The correct way to do that is:

MsgBox Evaluate("SUMPRODUCT(--(A2:A100=--" & startdate &
"),--(A2:A100<=--""2005-06-30""),
--(F2:F100=""Cleared""),--(D2:D1000),D2:D100)")

HTH,
Bernie
MS Excel MVP


"Jimbo" wrote in message
...
Many thanks for your help in previous thread,

Now I would like to replace some of the criteria with variables but I
am
stumped with the dates ... e.g. in the code below I would like to
replace
"2005-06-01" with StartDate a variable which holds the value of the
first
day of the current month

StartDate = Format(DateAdd("d", -Day(Now() - 1), Now()), "yyyy-mm-dd")

However many permutations of Cdate, Cstr, Format etc I cannot get it to
work
correctly would you mind showing me how it can be done

........... original code ............



evaluate("SUMPRODUCT(--(A2:A100=--""2005-06-01""),--(A2:A100<=--""2005-06-3
0""),
--(F2:F100=""Cleared""),--(D2:D1000),D2:D100)")


Thanks again