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
 
Hmm. I'm not sure what I did. Maybe I'm just a little too close for the moment. I've just adopted your Sen1>Sen2 without further edits and those two instances that I cited above are not apparent.

Let's hope that all the bugs are rid.
 
Last edited:
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Ok Pete. I've gone back and "deskchecked" the data which concurs with the following table.
Do you also concur that is where you arrived? If so we can both say that your project is ready.


Excel 2013/2016
ABCDEFGHIJKLMNOP
1PR1App?PR2App?PR3App?PR4App?SenAge
2NameStEndStEndStEndStEnd
3Jones3-Jan-1812-Jan-18No6-May-1814-May-18No4-Apr-1814-Apr-18No18-Feb-1828-Feb-18Yes3-Jun-1735
4Smith13-May-1825-May-18Yes10-Nov-1820-Nov-18Yes7-Jun-1817-Jun-18Yes17-Jan-1831-Jan-18No2-Jun-1730
5Chan2-Jan-183-Jan-18Yes5-Dec-1812-Dec-18Yes13-Aug-1823-Aug-18Yes20-Jun-1830-Jun-18Yes1-Jun-1740
6Wills12-Jan-1823-Jan-18Yes2-Jul-1818-Jul-18Yes5-Oct-1815-Oct-18No19-Sep-1829-Sep-18Yes4-Jun-1741
7Thom6-Feb-1813-Feb-18Yes1-Mar-1811-Mar-18No4-Jul-1814-Jul-18No27-Dec-186-Jan-19Yes5-Jun-1742
8Ford3-Mar-1817-Mar-18Yes3-Apr-1810-Apr-18No15-Jan-1825-Jan-18No4-Apr-1814-Apr-18No6-Jun-1744
9Simons8-Mar-1815-Mar-18No4-Mar-1814-Mar-18No6-Sep-1816-Sep-18Yes6-Aug-1816-Aug-18No7-Jun-1743
10Dean9-Apr-1816-Apr-18No9-Oct-1819-Oct-18Yes2-Feb-1812-Feb-18No21-Oct-1831-Oct-18Yes9-Jun-1745
11George15-Apr-1828-Apr-18Yes3-May-1811-May-18Yes7-May-1817-May-18No21-Nov-181-Dec-18Yes8-Jun-1746
Sheet1
 
Upvote 0
Hi Brian,

I definitely concur with the data above, and after the small mod I made, I believe it is definitely ready for service. I plan to add a few features with my newly acquired, albeit basic, knowledge of VBA. Thank you again for taking the time to help with the project, and with helping a newbie get hold of the basics.

Pete
 
Upvote 0

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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