Macro to sum array by date

[Joe Blow]

Hi,

I am having some trouble with this marco that I'm attempting. Hoping
someone could give me a hand. I am trying to sum an array based on a
date range.

Here is how I am passing info to the macro:

=accmgrsum(1/1/05,1/31/05,Bookings!D37,Bookings!H3:H7)

Bookings being a worksheet. Bookings!D37 are dates and
Bookings!H3:H7 are dollars.

Here is my marco:

Function accmgrsum(start_date, end_date, date_array, bookings_array)
accmgrsum = Application.Sum(IIf(date_array >=
Application.DateValue("start_date"), IIf(date_array <=
Application.DateValue("end_date"), bookings_array, 0), 0))
End Function

Could someone let me know where I have gone wrong? I am not too well
versed on VBA as you can probably tell. Am I on the right track or is
there an easier way?

Thanks,
Joe

 

[Bob Phillips]

=SUMPRODUCT(--(Bookings!D37>=--"2005-01-01"),--(Bookings!D37<=--"2005-01
-31"),Bookings!H3:H7)

No VBA!

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Joe Blow]

=SUMPRODUCT(--(Bookings!D37>=--"2005-01-01"),--(Bookings!D37<=--"2005-01
-31"),Bookings!H3:H7)

No VBA!

Thanks Bob, the reason I am using vba is that I need to call this
macro repeatedly with many differing dates. This is just my test case.
Any ideas?

Joe

 

[Bob Phillips]

Joe,

Saw you in the torrents group today and remembered I hadn't answered your
post. Here is a solution

Function accmgrsum(start_date, end_date, date_array, bookings_array)
Dim sFormula As String

sFormula = "SUMPRODUCT(--(" & date_array.Address(external:=True) & _
">=--""" & Format(CDate(start_date), "yyyy-mm-dd")
& """)," & _
"--(" & date_array.Address(external:=True) & _
"<=--""" & Format(CDate(end_date), "yyyy-mm-dd") &
""")," & _
bookings_array.Address(external:=True) & ")"
accmgrsum = Evaluate(sFormula)
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Joe Blow]

Joe,

Saw you in the torrents group today and remembered I hadn't answered your
post. Here is a solution

Function accmgrsum(start_date, end_date, date_array, bookings_array)
Dim sFormula As String

sFormula = "SUMPRODUCT(--(" & date_array.Address(external:=True) & _
">=--""" & Format(CDate(start_date), "yyyy-mm-dd")
& """)," & _
"--(" & date_array.Address(external:=True) & _
"<=--""" & Format(CDate(end_date), "yyyy-mm-dd") &
""")," & _
bookings_array.Address(external:=True) & ")"
accmgrsum = Evaluate(sFormula)
End Function

Bob,

Many thanks! Looks quite different from my first pass. I'll give it a
try later today.

Regards,
Joe

 

[Bob Phillips]

Yeah, whereas you tried to do it all in VB, I just used builtin Excel
functionality. I had the biggest problem with the dates, the formula worked
in Excel, but not when I transposed to VBA. That is why I cast the string
to a date, and formatted it backwards.

Bob