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]

Ok. That is complicating matters.
That's going to test me.

 

[DAD]

Hi Brian,

Yes, unfortunately what started out as something I thought would be relatively simple, turns out it is not. But thanks you for putting your effort and brain power into it.

Pete

 

[BrianJN1]

I quickly found that my initial model was incorrect so I did actually have to "flowchart" Priority 1.
To go further I had to create some data of my own. In Col N I've set the outcome which Col E should be telling me.
Those columns are in agreement but I'm still not totally confident. I also know that my algorithm is not highly efficient.

Anyway, this is what happens:
I get two items of data (dates). I retain the first and test it against others in the list. When found to be invalid against your criteria it is so flagged and then the second item becomes the focus. It (when further down the list) does have to begin at the start of the list as something earlier might have created an exclusion and this needs to take that into account.

Now, if my process so far is correct then any personnel who have a "Yes" need to have an "N/AP" registered for Pr2 and Pr3.
In addressing Pr2 dates both the "N/AP" and Pr1 dates do need to be addressed as someone like Smith for instance, may have nominated an earlier date (eg, 15 Jan. Wills having that period reserved would create a rejection).

Then Pr3 also has to backtrack on Pr1 and Pr2 whilst looking at its own dates.

This is my code at this point:

Sub overlap()
Dim day1, day2, day3, day4 As Long
Dim sen1, sen2, age1, age2 As Long
Dim lastrow, a, b, flag As Long
lastrow = Cells(Rows.Count, "C").End(xlUp).Row
For a = 3 To lastrow
day1 = Range("C" & a).Value
day2 = Range("D" & a).Value
sen1 = Range("L" & a).Value
age1 = Range("M" & a).Value

Range("B16").Value = Range("B" & a).Value
Range("C16").Value = day1
Range("L16").Value = sen1
Range("M16").Value = age1
For b = 3 To lastrow
If a <> b Then
flag = 0
day3 = Range("C" & b).Value
sen2 = Range("L" & b).Value
age2 = Range("M" & b).Value
Range("B17").Value = Range("B" & b).Value
Range("C17").Value = day3
Range("L17").Value = sen2
Range("M17").Value = age2
If day1 < day3 And day3 < day2 Then

        
   If sen1 = sen2 Then
           If age1 < age2 Then
             flag = 1
                Exit For
            End If
           
           ElseIf sen1 > sen2 Then
                flag = 1
                Exit For
        End If
   
End If
End If

Next
If flag = 1 Then
Range("E" & a).Value = "No"
Else
Range("E" & a).Value = "Yes"
End If
Next

End Sub

This is my sheet after the run. Oh, I didn't mention that I added some code to show what data was being processed as I stepped through the code (Debug F8). That appears below the table.

Excel 2013/2016

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			PR1		App?	PR2		App?	PR3		App?	Sen	Age
2		Name	St	End		St	End		St	End				Req'd
3		Jones	1-Jan-18	10-Jan-18	No							3-Jun-17	35	N
4		Smith	13-May-18	25-May-18	Yes							2-Jun-17	30	Y
5		Chan	2-Jan-18	3-Jan-18	Yes							1-Jun-17	40	Y
6		Wills	12-Jan-18	23-Jan-18	Yes							4-Jun-17	41	Y
7		Thom	6-Feb-18	13-Feb-18	Yes							5-Jun-17	42	Y
8		Ford	3-Mar-18	17-Mar-18	No							7-Jun-17	44	N
9		Simons	8-Mar-18	15-Mar-18	Yes							6-Jun-17	43	Y
10		Dean	9-Apr-18	18-Apr-18	Yes							8-Jun-17	45	Y
11		George	15-Apr-18	28-Apr-18	Yes							9-Jun-17	46	Y
12
13
14
15
16		George	15/04/2018									9/06/2017	46
17		Dean	9/04/2018									8/06/2017	45
18

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

 

[DAD]

Hi Brian,

Wow, thanks so much for that. I am going to have to go through this slowly to figure out and understand it. I'll get back to you once I think I have a good grasp on how you have done it. Once again, thanks again, you have no idea of how helpful this is.

Pete

 

[BrianJN1]

There is an error. I submitted a post but for some reason it didn't work.

If I reverse the Seniority of Ford and Simons they both get "Yes" but that should be "Yes" and then "No"!!!!
I thought that I had that error beaten but I cannot understand where I went wrong.

There is something in this block:

If day1 < day3 And day3 < day2 Then
  If sen1 = sen2 Then            
      If age1 < age2 Then
             flag = 1
                Exit For
            End If
           
           ElseIf sen1 > sen2 Then
                flag = 1
                Exit For
        End If   
End If
End If

which I failed to address.

 

[DAD]

Once I get a handle on the coding, I may be able to help with the logic

 

[DAD]

Hi Brian,

I have tried my best to understand the code. I think I do, but fear my understanding is wrong as I struggle to make sense of the logic. I think your code for variable lastrow looks up the last row of data (dates) in column c. You then create a loop from 3 (first row of dates) to lastrow (last row of dates). But what I am struggling to understand is the variables a and b (counters I assume). It appears to me that Day 1 should be the first day of leave for Priority one, day 2 the last day of leave for priority one. But, by my understanding, it appears as though day 3 is also the first day of leave for priority one. Obviously I am incorrect, as the logic "If day1 < day3 And day3 < day2 Then" would never work. But I can't seem to grasp why there is a difference between day 1 and day 3 (given that counter variables a and b seem the same).

Pete

 

[BrianJN1]

I realised later that I should not have located those values at C16 and C17 as when the code is run a second time, and those rows are not cleared, then lastrow counts to row 17 which I don't want.

The variables day1 and day3 represent the range of the leave for the first employee being tested. Day2 and day4 (which I haven't used) are the start and end leave dates for the second employee being compared. If day2 is between day1 and day3 then we have an obvious clash which has to be resolved. As to the variables a and b, when a is 1 then all values associated with b= 2 to lastrow are compared with a=1, then when a=2, all values for b=1, 3 to lastrow are compared with a=2.

As to priority, and we are only talking about Pr1 at the moment, day1 is always the first day of leave. Later when we consider Pr2 and Pr3 those same variables will be used again.

I should have annotated the code before I published it.

 

[DAD]

Ah ok, that is where my mistake is. I will go back and figure out why b=2 when a=1. That would make sense

 

[BrianJN1]

In a for ... next loop a would usually begins at 1 (but our data begins at row 3). The next time through the loop a increases by 1.

Now that code has a a pair of "nested" For ...Next loops
For a= 3 to lastrow
for b= 3 to lastrow
next
next

When a=3, b loops until it gets to lastrow and then a jumps to 4 and b begins fro 3 to lastrow again; it's a bit like the odometer in your car, the units keeps repeating while the tens increases by 1.