The attached file is a staff scheduling tool for assigning staff to "slots" in 6 cafeterias. Each cafeteria has two shifts every day except Friday where there is no PM shift.I'm having great difficulty handling the coding for a few special staff members. Let's assume one of them is Marty. All assignments should be random. The rules for Marty' are:
Marty must work in all cafeterias by the end of the week. Double shifts where he works in one cafeteria in the AM shift and in the same or different cafeteria in the PM shift are OK.
Marty may not be in more than one cafeteria in a shift on the same day.
Marty's count of shifts at the end of the week should be a minimum of 6 and maximum of 10.
Cafeteria's 2, 5 and 6 have lighter traffic than the others. They are highlighted in yellow on the worksheet. Marty can only work in those cafeterias once a week.
you do not mention how marty is prioritized with other staff when allocating shifts, or how many staff per shift per cafeteria
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
westconn1, what I thought was a solution didn't actually work properly so if your still willing to look into it I'd appreciate it.
To answer your questions, there is no priority among staff members; all are treaded equally.
I'm not sure I understand your second question but besides point #4 in my question the restrictions are that every staff member must work at least 6 times during the week and the staff member can't be in two places at the same time. In other words Marty can work in Cafeteria 1 in a day like Monday in the AM shift and in Cafeteria 2 in the PM shift but not both cafeterias in the AM shift (or PM shift).
The 6 times a week is the problem because if you divide the 90 opportunities to work by the 12 staff members you get 7.5 and so while the minimum assignment requirement is 6, the maximum assignment limit should probably be 7. That would result in 84 assignments, but the remaining 6 still have to be filled and I don't know how to do that given the can't-work-in-two-cafeiterias-in-the-same-shift restriction. I've worked on this for a long time and haven't been able to resolve it.
i am thinking on this, will post when i get something
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete