Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 27

Thread: allocating duties on works rota using VBA

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

    allocating duties on works rota using VBA



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

    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.
    Attached Files Attached Files

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    579
    Articles
    0
    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.
    Attached Files Attached Files

  3. #3
    Seeker sherbetdab's Avatar
    Join Date
    Jan 2017
    Location
    Glasgow, Scotland, UK
    Posts
    15
    Articles
    0
    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/4...tiple-criteria
    Last edited by p45cal; 2017-01-08 at 09:52 AM.

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

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,019
    Articles
    0
    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.
    Attached Files Attached Files
    Last edited by p45cal; 2017-01-08 at 10:03 AM.

  6. #6
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    579
    Articles
    0
    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.

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

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

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

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

Page 1 of 3 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
  •