VBA help for newbie

[DAD]

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.

 

[BrianJN1]

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.

 

[DAD]

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

 

[BrianJN1]

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.

 

[BrianJN1]

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.

 

[DAD]

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.

 

[BrianJN1]

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?"

 

[DAD]

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

 

[BrianJN1]

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"?

 

[DAD]

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