VBA help for newbie
Eliminate Pivot Table Annoyances
Page 1 of 6 123 ... LastLast
Results 1 to 10 of 54

Thread: VBA help for newbie

  1. #1
    Board Regular
    Join Date
    Jan 2010
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA help for newbie

     
    Hi Guys and Gals,

    I have a project I have been working on for quite some time, but unable to find a workable solution. Up until now, I have been trying to find a solution without the use of VBA, as I am a relative newbie to VBA, but doing lots of reading and "trial and error" coding to fix that. Unfortunately the non-VBA solution is too big and too unworkable, so I am now trying it using VBA solution. I was hoping some of you may generously help get me on the right track?

    So I work for a small company, and I am trying to make our system of allocation holiday/vacation dates a little more streamlined. To this end, I have come up with an algorithm to allocate the leave, but just now need to be able to code that into excel so employees can see if their desired leave dates will be approved.

    If you observe the pic below, I have drastically simplified the final solution so that it may make it easier for you to help me down the right path. You will see that the employees are Smith, Jones and Chang. They can request up to 3 periods of Holidays/vacation per year, and they allocate each of the periods a priority, 1 being the most desired, 3 being the least. If there is no clash with other requested dates, it is approved, and the column titled "Approved" beside the requested period will say YES. If there is a clash of dates (with any of the dates entered in Priority 1-3), then the person that has the higher priority dates entered (1-3) will have their leave approved (YES) and the lower priority will have NO in the Approved? column. Should the dates clash, and the priority be the same, then the person with the higher seniority (that is the earlier date in column L will be approved (YES), the lower seniority with NO. Should The dates clash, the priority the same, the seniority the same, then the final step will be to allocate the leave to the oldest person (column N).

    So to summarise:

    1. No clash of dates = Approved
    2. Clash of dates = Approved to higher Priority
    3. Same dates + same Priority = Approved to higher Seniority
    4. Same dates + same Priority + same Seniority = Approved to older person.

    I realise this is more complicated than most questions asked here, but I would really appreciate your help. While the final solution is a lot more complicated, if I can gain an understanding of how you experts solve a smaller problem like this, I am sure I will be able to figure out the larger solution.

    Many thanks for your time.


  2. #2
    Board Regular
    Join Date
    Jun 2017
    Location
    Nth Vic, Australia
    Posts
    593
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA help for newbie

    I am a little confused here. You state
    "If you observe the pic below, I have drastically simplified the final solution"
    but if your pic is the final solution, then Jones and Chang appear to have a clash of dates?

    If I understand your concept correctly no two employees will ever share a period of time with another, even though that may be one day, ie Smith appears to start on Jan 9 while Jones finishes one day later. Is that correct?

    BTW, I'm no expert.
    You may see and know but until you tell me exactly I am blind; I cannot read your mind.
    Attachments? No, but check for on site preferences.
    Cloud sharing or other off-site links? Yes, but off-site links are fragile into the future.

  3. #3
    Board Regular
    Join Date
    Jan 2010
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA help for newbie

    Hi Brian,

    Thanks for your reply. Yes you are correct, only one employee should be on leave at any one time, even if one day overlap. I should have stated that in my original post, my bad. And when I said I have simplified the final solution, I probably was grammatically incorrect. The pic simplifies the process of what the final solution will be. The simplified version in the pic, does show what the problem can be with clashing dates. If I can find a VBA solution to the simplified version shown in the pic, I should be able to translate the VBA process to solve the much larger, final version. I hope that explains it better.

    Thanks again
    Last edited by DAD; Nov 15th, 2017 at 12:03 AM.

  4. #4
    Board Regular
    Join Date
    Jun 2017
    Location
    Nth Vic, Australia
    Posts
    593
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA help for newbie

    Thank you. You're not wrong about complication. I'm trying to build a flow chart around this premise:
    Date>Priority>Seniority>Age

    This is where I was at when your reply arrived:

    Priority 1
    Get dates - St & End
    Get 2nd date Any overlaps
    Yes
    - Priority - Yes


    I can usually get away without flowcharting events but with something as complicated as this I'm going to have to build a logical structure around your criteria before I translate that into code.

    I'm unsure when I can give you a result but I'll continue unless someone accepts and completes this challenge before me.
    You may see and know but until you tell me exactly I am blind; I cannot read your mind.
    Attachments? No, but check for on site preferences.
    Cloud sharing or other off-site links? Yes, but off-site links are fragile into the future.

  5. #5
    Board Regular
    Join Date
    Jun 2017
    Location
    Nth Vic, Australia
    Posts
    593
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA help for newbie

    I erred above when I said "Flowcharting". Yes I've done that before but what I really meant was "pseudocoding" which in your scenario, hopefully goes like this:

    Date>Priority>Seniority>Age
    Priority 1
    Get dates - St & End
    Get 2nd date Any overlaps
    **Yes
    - Priority
    -Yes --- "Approved" Priority 2 & 3 - "Disapproved"
    - No ---
    Seniority
    - Yes - "Approved"
    -No ---
    Age
    - Yes -"Approved"
    -No - "Disapproved"
    **No - "Approved"
    End of Priority1?
    No - Get next date

    Yes, test for Priority 2 & 3.
    Last edited by BrianJN1; Nov 15th, 2017 at 03:11 AM.
    You may see and know but until you tell me exactly I am blind; I cannot read your mind.
    Attachments? No, but check for on site preferences.
    Cloud sharing or other off-site links? Yes, but off-site links are fragile into the future.

  6. #6
    Board Regular
    Join Date
    Jan 2010
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA help for newbie

    Hi Brian,

    Thank you so much for putting time into this. I really do appreciate it. Any code you could provide would be enough to "guide" me on how to solve the larger puzzle. I am keen to learn VBA, and perhaps I have jumped in the deep end, but I find I learn best with trial and error. And having experts like yourself guide me is immensely helpful.

  7. #7
    Board Regular
    Join Date
    Jun 2017
    Location
    Nth Vic, Australia
    Posts
    593
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA help for newbie

    There are two questions I should have asked, "What two persons submitted requests but one is a day earlier than the other? Will the first date always take precedence?"
    You may see and know but until you tell me exactly I am blind; I cannot read your mind.
    Attachments? No, but check for on site preferences.
    Cloud sharing or other off-site links? Yes, but off-site links are fragile into the future.

  8. #8
    Board Regular
    Join Date
    Jan 2010
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA help for newbie

    Hi Brian,

    Good question mate. It is proposed that the date of submission will not be a determining factor. All employees will be told when submissions need to be in by a nominated "closing date". The Algorithm will sort out who is allocated the leave.

    But your question certainly has been a topic of discussion in this process.

    Pete

  9. #9
    Board Regular
    Join Date
    Jun 2017
    Location
    Nth Vic, Australia
    Posts
    593
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA help for newbie

    No, that is not what I meant. "A" nominates 1 Jan, "B" nominates 2 Jan as start dates. Would the earlier date take precedence even if "B" had Seniority and/or Age over "A"?
    You may see and know but until you tell me exactly I am blind; I cannot read your mind.
    Attachments? No, but check for on site preferences.
    Cloud sharing or other off-site links? Yes, but off-site links are fragile into the future.

  10. #10
    Board Regular
    Join Date
    Jan 2010
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA help for newbie

      
    Ah, now I see what you mean. The start date is irrelevant, Even if there is only one day overlap at the end of a 3 week holiday period, the Priority/Seniority/Age system will allocate the leave. The one who misses out will have to adjust their leave accordingly.

    I hope that answers your question.

    Pete

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com