Use VBA to Identify Multiple Days in a Schedule.

[railguage48]

I have managed to write some vba using sumproduct to identify multiple instances of a day in a persons schedule ... following is the code:

Sheets("Schedules").Activate
Range("A" & jj).Select
LastRow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
Range("AF3").Formula = Evaluate("=SUMPRODUCT((A3:A" & LastRow & "= """ & WeekDay & """)*(B3:B" & LastRow & "="""
& ClName & """))")

kkk = Range("AF3")
If kkk > 1 Then
cntr = cntr + 1
Else
cntr = 0
End If

The sumproduct essentially checks to see for example that in the A3:AA range whether we have a match .... say a Monday that appears more than once for the client ... say John G. In the example I am using we have John G with three time slots on Monday that he is scheduled thus kkk = 3.

I would like to loop through the ranges in the Schedules sheet and copy those rows where the above statement about John G is true. For the rest of the example we just assume that John only has one scheduled time for each of the rest of the days of the week so in those instances kkk = 1.

I have the following code to loop thru the days of the week:

For j = 3 to 11
WeekDay = Range("AE" & j)
...
if kkk > 1 then
WeekDay = Range("AE" & (j-cntr))
else
WeekDay = Range("AE" & j)
Next j

There is a whole bunch of code, including he sumproduct above that then loops thru the Schedules sheet in the appropriate rows and columns copying the instances where John G has a schedule for a particular day of the week.

First I create a schedule sheet for John G and the vba code enters the first row of data ... Monday etc etc in Row A3 thru H3 of this new sheet.
On the second loop through the data on the Schedules sheet it finds another Monday for John G and this is where I would like to hold the WeekDay variable such that it is still pointing to Monday for a total of three loops since kkk = 3 for John G and Monday.

The problem that I am having is that if I hold WeekDay to point at Monday by something like WeekDay = Range("AE" & (j -1)), it seems to work for two instances ... that is the first instance, WeekDay = Range("AE" & j), j here is 3 for example, then the next pass we have WeekDay = ("AE" & j) and here j = 4 but since I reset it back one we are still at 3 ... this unfortunately only works once then I get and empty WeekDay = "". The loop has to loop through the Schedules sheet and I use jj as a variable here which increases by one each pass through ... next j, however ... increases j but then I try to reset it back by one so that it is still pointing to Monday in the Range("AE" & j) section of the Schedules sheet, it is a temporary range.

Can anyone please help me work through this.

Thanks.

 

[railguage48]

I finally got the code to work. First I used:

For each lll in Range(DaysoftheWeek)
WeekDay = lll.value

This to control the day that is being worked on so that if there are multiple appointments for John G on Monday the logic and capture all those and post them to the John G sheet.

I used the Sumproduct to control the flow of the logic so that I could cycle through Monday picking up those different appointments ... here is the code:

If kkk > 0 then
For j = 1 to kkk
...
...
Next j
End If.

And that did it ... I now have to focus on formating the new sheets such that if there are multiple Monday appointments for example, then in col A, that is were the day is shown, I will show Monday in the first row and all subsequent rows that have a Monday below the first row will be offset by one column.

Thanks.