Use VBA to identify multiple activities on a Day.

[railguage48]

I need to find a way using VBA to identify multiple activites on a particular day.

Example:
Monday ------------Morning Session with Adam Adam
Monday ------------Lunch with Adam Adam
Monday ------------Afternoon Session with Adam Adam.

These are all the same Monday and the schedules will have 60 different clients all having sessions with staff on different days.

I have a 'lists' sheet where we have lists of clients, Client Range (a3:300), DaysofWeek range (b3:b9), and I have a data entry sheet, 'schedules', where the schedules, (a3:i600) are entered, so I have tried on a test sheet:

a1 = sumproduct(--(a3:a600=Lists!b3)*(a3:a600=Lists!a3) --- a1 = 3 .... There are three sessions on Monday with Adam Adam.

My purpose is to create a seperate sheet for each of the clients, Adam Adam, etc. On this sheet I will enter their completed schedule for the week.

So using VBA, my hope was to identify through the use of sumproduct, days where the clients have a number of sessions so that when a seperate sheet is created for the client, the entries will be as follows ... all of the data will be pulled from the schedules sheet range(a3:a600):
Day Time Where Code Staff hrs

Monday 08:45AM to 11:00AM Community Center zz-0000 Jim Jim 2.25 hrs
11:00Am to 1:00PM Lunch yy-0000 Sally Sally 2.00 hrs
1:00PM to 4:00PM Various Activites xx-0000 Ben Ben 3.00 hrs


Billing Codes zz-0000 2.25 hrs
yy-0000 2.00 hrs
xx-0000 3.00 hrs
Total hrs 7.25 hrs

The client might have a full week of activities so the sheet would show entries for every day that he or she is scheduled.

I am having problems using sumproduct for this since it does not seem to work well with dynamic or named ranges using VBA, so I thought I would create a seperate page called Activites, and use Sumproduct to create a table showing a list of clients in column A and in column B thru H have the days of the week and then run sumproduct with the multiple conditions of client and day of the week to fill the table.

But .. Is there a better way to do this?

Thanks.

 

[railguage48]

Ok ... I got the SumProduct to work with variable ranges ... the solution is already on this site ... I read so many different approaches that I cannot point to the one that I am using but here it is all the same ... thanks to the experts working here on MrExcell:

Range("AF3").Formula = "=SUMPRODUCT((A3:A" & LastRow & "=AE" & jj & ")*(B3:B" & LastRow & "=AA" & jj & "))"

Note: One has to define what LastRow is and declare jj as an integer.

So ... all that is left is to correctly format the page.

Thanks.

 

[railguage48]

I replaced the section, =AE" & jj & ", with a named range, you will also notice that I made the first range dynamic for consistency, and this is how the formula now looks:

Range("AF" & jj).Formula = Evaluate("=SUMPRODUCT((A3:A" & LastRow & "= """ & WeekDay & """)*(B3:B" & LastRow & "=AA" & jj & "))")

This works great and gives you the flexibility that comes with dynamic and named ranges.