Use VBA to Identify Multiple Days in a Schedule.

railguage48

New Member
Joined
Dec 8, 2012
Messages
11
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.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

railguage48

New Member
Joined
Dec 8, 2012
Messages
11
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,478
Messages
5,770,321
Members
425,612
Latest member
martinijr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top