Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Joe Blow
 
Posts: n/a
Default Macro to sum array by date


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!D3:D7,Bookings!H3:H7)

Bookings being a worksheet. Bookings!D3:D7 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
  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

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

No VBA!

--

HTH

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


"Joe Blow" wrote in message
...

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!D3:D7,Bookings!H3:H7)

Bookings being a worksheet. Bookings!D3:D7 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



  #3   Report Post  
Joe Blow
 
Posts: n/a
Default

On Mon, 17 Jan 2005 21:30:44 -0000, "Bob Phillips"
wrote:

=SUMPRODUCT(--(Bookings!D3:D7=--"2005-01-01"),--(Bookings!D3:D7<=--"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
  #4   Report Post  
Bob Phillips
 
Posts: n/a
Default

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" wrote in message
...
On Mon, 17 Jan 2005 21:30:44 -0000, "Bob Phillips"
wrote:


=SUMPRODUCT(--(Bookings!D3:D7=--"2005-01-01"),--(Bookings!D3:D7<=--"2005-0

1
-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



  #5   Report Post  
Joe Blow
 
Posts: n/a
Default

On Wed, 19 Jan 2005 20:21:04 -0000, "Bob Phillips"
wrote:

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


  #6   Report Post  
Bob Phillips
 
Posts: n/a
Default

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


"Joe Blow" wrote in message
...
On Wed, 19 Jan 2005 20:21:04 -0000, "Bob Phillips"
wrote:

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert date in macro George Gee New Users to Excel 12 April 17th 06 05:44 AM
Date Overdue function, Macro, or VBS Galsaba Excel Discussion (Misc queries) 5 January 14th 05 01:26 AM
how to alter the date within a macro LWhite Excel Discussion (Misc queries) 2 January 4th 05 02:54 PM
Need help troubleshooting an array formula XLXP on Win2K KR Excel Worksheet Functions 1 December 13th 04 08:41 PM
Date and Time Macro m.j.anderson Excel Discussion (Misc queries) 1 December 1st 04 01:35 AM


All times are GMT +1. The time now is 01:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"