allocating duties on works rota using VBA

[sherbetdab]

Hi All, new to the forum and been using excel for the past 5 years, only just started learning basic vba coding which helps me speed up some of the everyday chores i have to carry out in my job, i've come unstuck trying to speed up one of the most time consuming tasks, I have a rota for 38 regular drivers and 29 relief drivers. At the moment i have to allocate duties to the relief drivers to cover days off, holidays, sickness etc of the regular drivers, this is based on their knowledge of the 38 routes that operate.I would love to automate the process by writing some code and in doing so save my self many hours work per week.

I'll be totally honest and say i don't know where to start, I'm guessing i have to run sort of loop for each day, when it finds a text value equalling "Rest" "Sick" "Holiday" it would then go and search for a relief driver who is on duty that day, it would then have to search the route knowledge sheet to see if he knows the route, if not it would mover onto the next relief driver on duty and so on, when it finds a suitable relief driver it would put the journey number next to his name meaning it would miss him on the next loop.

Anyway that's the idea in my head, what i need to know is, is it possible to do this with vba? or is it too complicated and maybe needs some other type of programming?

If it is possible could some of you kindly throw some ideas, hints and tips my way so i can carry out some research and put my idea into practice.

I've attached a sample of my rota in case my explanation is a bit hazy.

Thanks in advance.

 

[NoS]

sherbetdab,

Please read this message to forum cross posters and comply.

Have re-arranged the layout of your data to be easier to work with, similar to the way you show it at another forum.

The included macro implements your guess of a loop for each day.
Hopefully the code is commented well enough to follow.

 

[sherbetdab]

Apologies for failing to mention that I have posted the same request on stack overflow but have failed to recieve any replies, a friend suggested excelguru. I am unable to post a link to the question as I havent submitted 5 posts.

mod edit: here's that link: http://stackoverflow.com/questions/41496401/works-rota-allocating-duties-based-on-multiple-criteria

 

[sherbetdab]

NoS, you really are a Magician, apologies regarding the cross posting, I should've taken more care to read the forum rules before posting.

Your code is exactly what I have been looking for, there is no way on this Earth that I could have written that, I never expected someone to do it for me either, my best hope was to be pointed in the right direction and then to research and educate myself.

Looking through the code am i right in saying that i can replace "Route Driver" and "Relief Driver" with actual names of Drivers and it won't affect the running of the macro?

Thank you so much, this will save me so much time.

I know it's not much but i will be making a small donation to the site.

 

[p45cal]

I too have been working on a solution and saw NoS' solution - why didn't I think of copying the Route Knowledge table to the Rota sheet?!
I was working (a) on more likely getting to a solution without a lack of drivers available and (b) not favouring the relief drivers at the top of the list.
I'll continue working on it, but in the meantime I attach a few tweaks to NoS' sleek solution; there's a button on the Rota sheet which you can press repeatedly until you get a run without any non-availability messages.
All it does is sort the whole Relief Drivers table randomly before running the existing code and then returns it to the original order after the code has run.
I get about 1 in 4 presses of the button gives a solution with all routes satisfied, although I can easily envisage a situation where many more clicks would be necessary, or it might be impossible to satisfy all routes.
The attached currently has a fully satisified routes solution.

ps. I've just realised that I only do a random sort once for the whole week, so the same relief drivers will get preference over the whole week. If you want (or care) it can be changed so that the list is resorted randomly each day.

 

[NoS]

Coming from a workplace where there seemed to be a grievance filed over many a shift relief, my first inclination was to ask what the 'ground rules' were regarding seniority, staying on the same route next day, etc., then decided to just answer the question as posted, figuring the OP would return soon enough with the 'ground rules'.

PS: I'm an Electrician not Magician.

 

[sherbetdab]

Guys, I can't thank you enough, both books work a treat.

On Ground rules, we do try and keep a relief driver on a route for as long as we can, if a regular driver is on holiday or sick for a week we try to keep the same relief driver on the route for as much of the week as we can, other than that it's kind of relaxed. I wasn't sure if what you have already done for me was possible without complicating my request even more.

When I put the example book together I didn't put the names of the drivers or real route numbers as I was at home and didn't have the info to hand, adding the drivers names doesn't cause any issues, but when I change the numbers in column B to the correct route numbers I get a "Runtime error 1004" "Auto filter method of range class failed" the code stops at the line - filtRng.AutoFilter Field:=9 + route, Criteria1:="Y" 'the route column

The 38 route numbers run from 201 to 245 with some missing numbers. I'm hoping one of you can find a way to sort it.

Thanks again guys.

 

[sherbetdab]

Thanks p45cal, I'm amazed at the talent you guys have, it works a treat, I'm having a play around with both books at work and trying to work out the code and how it works. it's mind boggling how powerful excel can be in the right hands.

 

[p45cal]

when I change the numbers in column B to the correct route numbers I get a "Runtime error 1004" "Auto filter method of range class failed" the code stops at the line - filtRng.AutoFilter Field:=9 + route, Criteria1:="Y"

You need to do two things:
1. ensure the headers of the route knowledge table in the cells J45:AU45 of ther Rota sheet need to be exactly the same as the route names in cells B3:B40. Best to copy/transpose them from one location to the other.
2. change the offending line to:

filtRng.AutoFilter Field:=Application.Match(route, Range("A45:AU45"), 0), Criteria1:="Y"

I've also had to change the Sub RemoveNumbers().
See attached.
When you come to change the route numbers yourself either make sure you have run RemoveNumbers before you do so, otherwise you will have to do a one-time manual clearing of route numbers from the results area of the lower table.
Hopefully you don't have any route numbers prefixed with a zero(es).

 

[p45cal]

While developing my own solution for this I came across a big mistake I made in the attachment to msg#9; it had two duplicate routes in!! 2 181s and 2 56s. This has been corrected in the file attached to this message.

 

[sherbetdab]

Thanks p45cal, i entered the correct route numbers as we use them in work and ran the code, it works a treat, got a few weeks rotas done in a matter of minutes, unbelievable.

Where i may run into a bit of trouble is during peak holiday season when we have less relief drivers to choose from. Is there a way to make the code select the relief driver who has knowledge of the least number of routes and allocate him to a vacant route first? I'm thinking that leaving the more experienced relief drivers to the end would mean less chance of routes being missed. This would also help where we start a new driver and he has to build up knowledge of routes, or at least that's how it sounds in my head. :noidea:

 

[p45cal]

Where i may run into a bit of trouble is during peak holiday season when we have less relief drivers to choose from. Is there a way to make the code select the relief driver who has knowledge of the least number of routes and allocate him to a vacant route first? I'm thinking that leaving the more experienced relief drivers to the end would mean less chance of routes being missed. This would also help where we start a new driver and he has to build up knowledge of routes, or at least that's how it sounds in my head. :noidea:

In the attached there's a quite different solution.
It looks at the routes and finds those with the fewest eligible drivers. In some cases there may only be one (or even none! (that's reported)).
When a driver is assigned to a given route for the day, he clearly can't be allocated to a different route on the same day, so that driver is removed from all other routes he might have been eligible to do for that day.
The code takes these routes with the fewest drivers and then assesses the impact on other routes of removing each of those drivers from all the other routes he might have been eligible for, and chooses the driver that would leave other routes with the largest number of drivers to choose from (it actually only aims to keep routes with a lowest number of eligible drivers from falling lower).
Whenever there are equal consequences of choosing one route or driver over another, an element of randomness is used.
With your data as it stands, I've never had to run the process more than once.

There's a button on the Rota sheet; press it and it the first thing it does is to make a copy of that sheet and works on that, so your original sheet is always left untouched. The button is copied too, but it always copies the sheet named Rota. Repeated presses result in different arrangements.
Both upper and lower tables are updated.

It might cause consternation if the more experienced drivers were left to the end… it might mean that less experienced drivers were more likely to get work!!
What I can say about this solution is that if there's only one relief driver eligible and available to drive a given route he'll always get that route.
This is quite different from saying that if a relief driver can only do one route he'll always get it, he won't; he'll be thrown in with the rest of the drivers who can do it, with equal chance.

This has been quite an interesting problem, and if I get the time and inclination I might address

if a regular driver is on holiday or sick for a week we try to keep the same relief driver on the route for as much of the week as we can

by using an interactive interface where you might select a driver and immediately see the effect on possibilities elsewhere, and double-clicking a driver would assign him to a route for the day and update the remaining possibilities, that way you would be more able to keep a driver on a specific route.
Again, if I get the time and inclination.

 

[sherbetdab]

Hi p45cal, i ran the new solution today, wow, i ran it multiple times and not once did it miss allocating a relief driver to a route, absolutely fantastic. I've still to add holiday dates for the summer months but hopefully it still runs as good. The speed at which the drivers are allocated is astounding. Many Thanks for your help.

We are about to start 5 or 6 new drivers meaning there could be a couple of new routes and a few more relief drivers, if i go to each part of the code with cell references and use "lr as long" as my row references would the code still run ok? Would this also work if in future we had to let a few drivers go which would shorten each range? Rather this than me having to change the cell reference each time the number of drivers changed.

 

[p45cal]

We are about to start 5 or 6 new drivers meaning there could be a couple of new routes and a few more relief drivers, if i go to each part of the code with cell references and use "lr as long" as my row references would the code still run ok? Would this also work if in future we had to let a few drivers go which would shorten each range? Rather this than me having to change the cell reference each time the number of drivers changed.

Using 'lr as long' is a good idea, but the important question is how to determine the value of lr?
When I was writing this my first priority was getting the ligic working (and the logic too), and so yes I used hard coded values and now I need to make it a bit more flexible.
I'll do this, but for it to remain working there are a few conditions you need to adhere to when making changes to the worksheets.
This is the principal one:

• On the Rota sheet you have a row coloured black which is COMPLETELY BLANK (row 41). Keep this as a single blank row between the two tables! It doesn't, of course, have to be row 41.

Likewise below the second table,

• maintain a completely blank row below the last driver's name (currently row 73).

Others:

• A completely blank column in column J. (If you want to include more days/weeks on the Rota sheet, that's OK, just maintain at least 1 completely blank column after the last day.
• At the top of each table on the Rota sheet you have 2 header rows, keep them both as exactly 2 rows.

Why all this business about blank rows and columns? We'll be using the vba equivalent of selecting a single cell (try it yourself, select A1 on the Rota sheet) , then pressing F5 on the keyboard, choosing Special…, then choosing Current Region, OK. You'll see the selection change to include the whole top table. If there aren't completely blank rows and columns around the table this will fail and choose more cells. This will tell us:

• Where the bottom of that table is (could be your lr)
• Where the right of the table is
• That the top of the table below is 2 rows below that (impoortant, because we'll be doing the F5/Current Region thing from there to obtain the extents of the second table).

Assumptions made about the tables on the Rota sheet to allow smooth operation:

• That they both start in column A
• That the first day is in column D of both tables
• That the drivers nmames are in column A
• That the route numbers are in column B

Moving on to the Route Knowledge sheet.
Ensure:

• Drivers names are in column A, starting at row 2
• Route numbers are in row 1
• There is only one header row
• That you maintain a row of something below the last driver names (it doesn't have to be formulae as you have at the moment), and like wise you maintain an extra column of something at the right.
• Beyond those columns/rows you have at least 1 completely blank row/column (this is because we'll be doing the F5/Current Region thing and assuming the body of the table (the Ys) to be that result less one row at the top, less one row at the bottom and less one column at the right).

You can have more routes across the top, and more drivers names in column A in this Route Knowledge table than you have on the Rota sheet, but every route number and driver name on the Rota sheet has to be included (exactly the same) on the Route Knowledge sheet.

I'm going to take a break from this now until tomorow. FYI below is a list of lines that need changing and in red what needs changing (I just deleted lines that didn't need changing), also a few lines will need to be added.

Sub blah()
For Each colm In .Range([COLOR=#FF0000]"D3:I40[/COLOR]").Columns
ReportDriverShortage RouteDemandAndDriverAvailability, .Cells([COLOR=#FF0000]42[/COLOR], colm.Column).Value, colm, MinRouteDriversAvailable, True, ""
With colm.Cells(Application.Match(RouteDemandAndDriverAvailability(k, 1), .Range("[COLOR=#FF0000]B3:B40[/COLOR]"), 0))
With .Cells([COLOR=#FF0000]44[/COLOR], colm.Column).Resize([COLOR=#FF0000]29[/COLOR]).Cells(Application.Match(DriverToUse, .Range("[COLOR=#FF0000]A44:A72[/COLOR]"), 0))
If Len(msg) > 0 Then MsgBox "For " & .Cells([COLOR=#FF0000]42[/COLOR],  colm.Column).Value & ", the following routes have no driver because  all available and qualified drivers have been allocated elsewhere:"  & vbLf & vbLf & Mid(msg, 3) & vbLf & vbLf &  "These will be marked 'No drivers left'", , "We've run out of drivers  for the day!"
End Sub

Sub ReportDriverShortage(RDA, myday, colm, mrda, AtStart, msg)
With colm.Cells(Application.Match(RDA(ii, 1), colm.Parent.Range("[COLOR=#FF0000]B3:B40[/COLOR]"), 0))
End Sub

Sub CreateNewRDDA(RDADA, SceRng, mrda)
Set myRng2 = .Cells([COLOR=#FF0000]44[/COLOR], cll.Column).Resize([COLOR=#FF0000]29[/COLOR]).SpecialCells(xlCellTypeBlanks)
RouteColm = Sheets("Route Knowledge").Range("[COLOR=#FF0000]A1:AM1[/COLOR]").Find(what:=RDADA(i, 1), LookIn:=xlFormulas, lookat:=xlWhole, MatchCase:=False, searchformat:=False).Column
If Application.VLookup(DriverName, Sheets("Route Knowledge").Range("[COLOR=#FF0000]A2:AM30[/COLOR]"), RouteColm, False) = "Y" Then
End Sub

 

[p45cal]

See attached for testing. You should just be able to copy the code over (or copy the modules over) to you own files.
If you want attach a file with different numbers of route, drivers etc., I'll test myself.

 

[sherbetdab]

Many Thanks p45cal, you are a star.

Does the way you have this set up mean that each table is now dynamic?

I'm thinking about having a Master Route Knowledge Tab, I'll then use cell references in the names column of the "Route Knowledge" Tab "A2" = "=Rota!A44" and then use Vlookup to the Master Tab to populate the "Y". My Thinking here is that if a driver is on long term sick or 2 weeks holiday I can switch the Route Driver name in the top table with the Relief Driver from the bottom table. I think that should solve one of my earlier problems regarding keeping the same relief driver on a route for a number of weeks. Upon the Route Drivers return i can switch the names back. Before i go ahead and do this, Do You think this will work?

 

[sherbetdab]

My Supervisor has insisted that i add sunday to the rota, I don't need to allocate drivers, all that the cells will say is either Rest or Working.

When i do this and run the code i get

Run Time Error 1004 No Cells Were Found.

The Debugger highlights the following Line


Set myRng = SceRng.SpecialCells(xlCellTypeConstants, 22) Line 173

Sunday is inserted before Monday.

Could you please help me out by getting the code to somehow ignore the Sunday?

Thanks in advance, I actually hate asking as you've done so much already.

 

[p45cal]

Does the way you have this set up mean that each table is now dynamic?

The 3 tables are themselves not dynamic at all; there are no formulae, if you want to change them you have to go in and edit them by changing values, adding rows, columns etc.
What is dynamic is how the code finds where and how big these tables are. That's all as far as dynamism is concerned.

I'm thinking about having a Master Route Knowledge Tab, I'll then use cell references in the names column of the "Route Knowledge" Tab "A2" = "=Rota!A44" and then use Vlookup to the Master Tab to populate the "Y". My Thinking here is that if a driver is on long term sick or 2 weeks holiday I can switch the Route Driver name in the top table with the Relief Driver from the bottom table. I think that should solve one of my earlier problems regarding keeping the same relief driver on a route for a number of weeks. Upon the Route Drivers return i can switch the names back. Before i go ahead and do this, Do You think this will work?

The way I'd go about this in the first instance is as follows. Let's say you had a Route Driver 4, as in the file I last attached, away on long term leave. He does route 271H and there are several relief drivers who can do this route. Let's say you choose to put Relief Driver 11 in his place, this is what you do:
1. In the top table on the Rota sheet, replace Route Driver 4's name in column A with Relief Driver 11's name.
2. Put a plain number (no text - because text is what the code looks for to determine if a replacement driver is needed for that route) in the body of the top table on the Rota sheet on the same row as that route/driver. This way he gets treated as a regular driver of that route. (By the way, you can still put text in if Relief Driver 11 is sick, so that yet another relief diriver can be sought to replace him).
3. You need to make that relief driver (Relief Driver 11) unavailable for anything else - which is simplest done by putting something (anything (say Permanent Duty?)) in the cells against his name for all the days he's doing that in the bottom table of the Rota sheet.

You don't need to make any changes to the Route Knowledge sheet.

edit post posting: Changes suggested above have been included in the file attached to my next message.

 

[p45cal]

When i do this and run the code i get

Run Time Error 1004 No Cells Were Found.

The Debugger highlights the following Line


Set myRng = SceRng.SpecialCells(xlCellTypeConstants, 22) Line 173

Here, you've highlighted my lack of debugging. I haven't catered for a day when all the regular drivers are doing their own routes, so no relief drivers are required.
I have adjusted the code to cater for this eventuality in the attached as well as all the other changes below.
It makes me realise that there is also another potential situation I haven't catered for; all the relief drivers are off for whatever reasons. I haven't time right now to adjust the code for this eventuality, but if it raises its ugly head then come back.

Sunday is inserted before Monday.

Could you please help me out by getting the code to somehow ignore the Sunday?

Thanks in advance, I actually hate asking as you've done so much already.

In the attached I've added a Sunday and 3 lines of code needed changing:
Set colms = Intersect(colms, colms.Offset(2, 3))
beomes:
Set colms = Intersect(colms, colms.Offset(2, 4))
and:
Set RouteNumbersRng = colms.Columns(1).Offset(, -2)
becomes:
Set RouteNumbersRng = colms.Columns(1).Offset(, -3)
although this last could be changed altogether to:
Set RouteNumbersRng = Intersect(.Columns(2), colms.EntireRow)
which will work regardless of which column you want to start processing and will therefore work in both versions.
also:
Set DriverColms = Intersect(DriverColms, DriverColms.Offset(2, 3))
becomes:
Set DriverColms = Intersect(Colms.EntireColumn, DriverColms, DriverColms.Offset(2, 3))

See attached where I've added Sunday, and arranged for Relief Driver 11 to semi-permanently replace Route Driver 4. See cells A6,D6:J6,F54:J54.

Because the attached has a significant number of changes you need to check for yourself that it's doing its job properly.

 

[sherbetdab]

Thanks p45cal, that works great.

I'm probably starting to tempt your patience here, I'm trying to develop what you have done for me and turn it into exactly what I want.

I'd have been better off asking at the start but i wanted to see if i could develop the workbook myself.

You've probably noticed that there is a "Rest Day Plan" in Column C

The original Rota had a Rest Days Tab, all of the cells from monday to saturday have a formula

=IF(VLOOKUP($C3,'Rest Days'!$A:$H,K$201,FALSE)=0,$B3,"Rest") This puts the Route Number into empty cells and "Rest" into the cells where the Drivers are on their day off according to the rest plan. I've added a "Rest Days Tab" and written some code to put this formula into all of the relevant cells and the pasted the values so as to leave cells blank for the relief drivers as i've removed "SPARE" from the cell next to the relief driver. Now when i press the button i get runtime error 91 Object Variable or with block variable not set It sticks on line 192 Set myRng2 = DrvrColm.SpecialCells(xlCellTypeBlanks) If i put "SPARE" back in the cell next to the relief driver can the code be changed so that it knows they are available for a route if "SPARE" is in the cell, or is it something else altogether? Sorry again.