Finding Weekly Totals

I will try to attach a simplified file to illustrate what I want.
I use macros to generate a database-like table one row at a time for each event. One portion of the table shows a column of weekdays in which an event took place, the next col shows duration for that event in minutes. I want to use vba to get a weekly total of minutes at the end of every Tuesday (except that if there are no entries on a particular Tuesday, then cumulative minutes since the last weekly total.) In other words it needs to keep a running total that will be reset back to zero every Wed morning or at the next event after Tues.


[ATTACH]195913/ATTACH]

Why are you not counting all the minutes for every day? It looks like you are counting only the minutes in the last row for each day.

This could be done with macros, might also be possible with formulas. Do you have a preference?

This would also be a lot easier if instead of the names of the days you had the actual dates.

Thanks 6String

Yes I do actually have the dates of each event. Also I can keep a cumulative or running total of minutes for the events. The part that is puzzling to me is creating a "flag" to start and stop the count. To make this much easier, I have modified the sample file and am uploading it again with additional columns.

Note that each row or event is entered one at a time via a macro. You will note that the second Weekly Total shows up on the last row for a Monday. This is due to the fact that NO events occurred or were entered on Tue of that week. This can only be known when the FOLLOWING event is entered and the Day of that event is beyond Tuesday. Then the weekly total needs to be inserted on the prior row (Event 18) which is the last event of the week.
I would like to include this as part of the code which "Posts" the data to the table - one record at a time. Hence as each record is entered, the entry date must be checked to see if it is at least a "Wednesday". But if there is no event on Wed, then if would be Thursday or later. Each time there is a Weekly Time posted the "counter" needs to reset back to zero.

Thanks for your help.

To start, there is an error in your data. 10/23/2012 shows as Wed but it was a Tue, and the Daily Minutes is also incorrect for that day. I am not sure how the error occurred unless you were just typing in data as a sample rather than using real data, but maybe your macro has a bug.

The only data you need to load is in columns B and C; everything else can be calculated by formulas. See attached. [gocush=WeeklyTotalTest-2.xlsx]

ok this works if we do it with formulas in the table.

Can you help me modify it for vba with relative cell references? Let's say that my macro has selected cell B22 as the Activecell and pasted the event data into cells B22:E22.

The following statement gets me close, except that Activecell shows up as text rather than pointing to a cell. How do I modify this? Thanks.

ActiveCell.Offset(, 5).Formula = "=SUMIFS(C:C,B:B,"" >= ""&(Activecell-MOD(WEEKDAY(Activecell)+3,7)),B:B,"" <= ""&(Activecell+6-MOD(WEEKDAY(Activecell)+3,7)))"

If you want to insert the formula using a macro, you might as well do all the calculations in the macro. It's a matter of personal taste but if I can write a formula once and fill it down, there's not much benefit in composing it in VBA. But the answer to your question is below. However, I notice that you just pulled out the SUMIFS part of the formula I used rather than the entire formula. That will cause every row in column G to have the total minutes for that entire week, instead of just showing it once at the end of the week.

Dim R as Long
' intervening code here....
R = ActiveCell.Row
ActiveCell.Offset(, 5).Formula = "=SUMIFS(C:C,B:B,"">=""&(B" & R & "-MOD(WEEKDAY(B" & R & ")+3,7)),B:B,"<="&(B" & R & "+6-MOD(WEEKDAY(B" & R & ")+3,7)))

I just translated your method exactly to correct it, but I also recommend using a variable of type Range to iterate through the cells, instead of activating each cell. For example:

Dim c As Range
Set c = [B2]
Do While c <> ""
   c.Offset(, 5).Formula = ......... ' as above
   Set c = c.offset (1, 0)
Loop

This has the advantage of not jumping the active cell around, which is visible to the user unless you disable screen updating. It is also faster, though might not be noticeable for small ranges.