VBA help for newbie

DAD

Board Regular
Joined
Jan 8, 2010
Messages
54
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.

1596v9.jpg
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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?"
 
Upvote 0
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
 
Upvote 0
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"?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top