K
KevHardy
I’m trying to do a spreadsheet to make organising a rota system easier. Our
department has three duty roles each working day: Duty 1, Duty 2, and
Visiting Officer. There are 10 members of staff involved in the rota.
The first problem was having 10 staff with a mixture of full and part time
workers. Worker 1 to 6 working full time, Worker 7 and 8 working 3 days a
week, and worker 9 and 10 working 2 days a week.
I think I have solved this by making a list of 40 names – with workers who
are full time appearing 5 times in the list, and part-timers appearing the
amount of days they work (3, and 2). This gives me a cycle of 40 names with
everyone doing their share of duty relative to the days they work.
So my spreadsheet can now pre-fill column A with the dates of work days
within a 40 day cycle. What I need to do next is populate Column B (Duty 1),
Column C (Duty 2) and Column D (Visiting Officer) with names from my list of
workers (Team).
What I need is some vba code which will populate the three columns (B, C,
and D) with names from ‘Team’ without any name appearing in more than one
column on any particular day (as staff cannot do more than one duty role per
day).
The complications to this is that worker 7 can only do duty on a Tuesday,
Wednesday, or Thursday, worker 8 only Monday, Wednesday and Thursday, worker
9 only Thursday and Friday, and worker 10 only Tuesday and Wednesday. I have
absolutely no idea how to tackle this problem, or if a solution exists! I’m
hoping that there is some clever vba which can check the day against the
worker and check whether they are available to work that particular day or
not. If not then move them to the next appropriate day and then return to the
previous day and look at the next worker to see if they are suitable.
The 40 day rotation may change at some point in the future as workers hours
may change and I’ll need to adapt it for worker’s annual leave etc, but I
think I can do a userform to generate a list for this, it’s just the other
coding would need to allow for a changing ‘Team’ list.
Any help would be much appreciated.
department has three duty roles each working day: Duty 1, Duty 2, and
Visiting Officer. There are 10 members of staff involved in the rota.
The first problem was having 10 staff with a mixture of full and part time
workers. Worker 1 to 6 working full time, Worker 7 and 8 working 3 days a
week, and worker 9 and 10 working 2 days a week.
I think I have solved this by making a list of 40 names – with workers who
are full time appearing 5 times in the list, and part-timers appearing the
amount of days they work (3, and 2). This gives me a cycle of 40 names with
everyone doing their share of duty relative to the days they work.
So my spreadsheet can now pre-fill column A with the dates of work days
within a 40 day cycle. What I need to do next is populate Column B (Duty 1),
Column C (Duty 2) and Column D (Visiting Officer) with names from my list of
workers (Team).
What I need is some vba code which will populate the three columns (B, C,
and D) with names from ‘Team’ without any name appearing in more than one
column on any particular day (as staff cannot do more than one duty role per
day).
The complications to this is that worker 7 can only do duty on a Tuesday,
Wednesday, or Thursday, worker 8 only Monday, Wednesday and Thursday, worker
9 only Thursday and Friday, and worker 10 only Tuesday and Wednesday. I have
absolutely no idea how to tackle this problem, or if a solution exists! I’m
hoping that there is some clever vba which can check the day against the
worker and check whether they are available to work that particular day or
not. If not then move them to the next appropriate day and then return to the
previous day and look at the next worker to see if they are suitable.
The 40 day rotation may change at some point in the future as workers hours
may change and I’ll need to adapt it for worker’s annual leave etc, but I
think I can do a userform to generate a list for this, it’s just the other
coding would need to allow for a changing ‘Team’ list.
Any help would be much appreciated.