Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 11 to 20 of 27

Thread: allocating duties on works rota using VBA

  1. #11
    Seeker sherbetdab's Avatar
    Join Date
    Jan 2017
    Location
    Glasgow, Scotland, UK
    Posts
    17
    Articles
    0


    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #12
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,032
    Articles
    0
    Quote Originally Posted by sherbetdab View Post
    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.
    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
    Quote Originally Posted by sherbetdab View Post
    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.
    Attached Files Attached Files
    Last edited by p45cal; 2017-01-09 at 09:09 PM.

  3. #13
    Seeker sherbetdab's Avatar
    Join Date
    Jan 2017
    Location
    Glasgow, Scotland, UK
    Posts
    17
    Articles
    0
    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.

  4. #14
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,032
    Articles
    0
    Quote Originally Posted by sherbetdab View Post
    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.

    Code:
    Sub blah()
    For Each colm In .Range("D3:I40").Columns
    ReportDriverShortage RouteDemandAndDriverAvailability, .Cells(42, colm.Column).Value, colm, MinRouteDriversAvailable, True, ""
    With colm.Cells(Application.Match(RouteDemandAndDriverAvailability(k, 1), .Range("B3:B40"), 0))
    With .Cells(44, colm.Column).Resize(29).Cells(Application.Match(DriverToUse, .Range("A44:A72"), 0))
    If Len(msg) > 0 Then MsgBox "For " & .Cells(42,  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("B3:B40"), 0))
    End Sub
    
    Sub CreateNewRDDA(RDADA, SceRng, mrda)
    Set myRng2 = .Cells(44, cll.Column).Resize(29).SpecialCells(xlCellTypeBlanks)
    RouteColm = Sheets("Route Knowledge").Range("A1:AM1").Find(what:=RDADA(i, 1), LookIn:=xlFormulas, lookat:=xlWhole, MatchCase:=False, searchformat:=False).Column
    If Application.VLookup(DriverName, Sheets("Route Knowledge").Range("A2:AM30"), RouteColm, False) = "Y" Then
    End Sub
    Last edited by p45cal; 2017-01-10 at 11:48 PM.

  5. #15
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,032
    Articles
    0
    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.
    Attached Files Attached Files

  6. #16
    Seeker sherbetdab's Avatar
    Join Date
    Jan 2017
    Location
    Glasgow, Scotland, UK
    Posts
    17
    Articles
    0
    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?

  7. #17
    Seeker sherbetdab's Avatar
    Join Date
    Jan 2017
    Location
    Glasgow, Scotland, UK
    Posts
    17
    Articles
    0
    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.

  8. #18
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,032
    Articles
    0
    Quote Originally Posted by sherbetdab View Post
    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.
    Quote Originally Posted by sherbetdab View Post
    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.
    Last edited by p45cal; 2017-01-12 at 05:00 PM.

  9. #19
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,032
    Articles
    0
    Quote Originally Posted by sherbetdab View Post
    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.


    Quote Originally Posted by sherbetdab View Post
    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.
    Attached Files Attached Files

  10. #20
    Seeker sherbetdab's Avatar
    Join Date
    Jan 2017
    Location
    Glasgow, Scotland, UK
    Posts
    17
    Articles
    0
    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.

Page 2 of 3 FirstFirst 1 2 3 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •