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.

 

[DAD]

Ah, of course, that makes sense. I did think of that last night, but the placement of the NEXT at the bottom threw me off a bit

 

[BrianJN1]

Small errata --"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)"
I didn't refer back. Day 2 and Day 3 should be interchanged but that does not alter what I said.

(I have a distraction - visitors have arrived).

 

[DAD]

Another question Brian, you solution thus far seems to work ok, but if I change a date where previously it said NO due to a clash, and the new date does not have a clash, it remains as a NO. It appears as though the code only runs once. Is there a way to run the code again every time a date cell is changed?

 

[BrianJN1]

My solution only works to a point. What I need to do is assign one positive for a clashing range and then negatives to those others within that range.

I did make mention of such an idea in an earlier comment when referencing how one would have to deal with Pr2 and Pr3 assignments but didn't think about that need at this stage.

 

[DAD]

Yes, I think we are thinking along the same lines. I tried some coding myself last night, with some success, but baby steps at this stage But I did get stuck trying to make the code run whenever I changed a leave date. I had to go back to VBE to run the code again.

 

[BrianJN1]

Ah! I believe that I solved our first step. My date filter was incorrect.
Test this against a set of your own data. I've tried it a few times on two sets of data and even changed seniorities.

Sub lap()
Dim day1, day2, day3, day4 As Long
Dim sen1, sen2, age1, age2 As Long
lastrow = Cells(Rows.Count, "C").End(xlUp).Row

For a = 3 To lastrow
flag = 0
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
Range("E16").Value = flag
day3 = Range("C" & b).Value
day4 = Range("D" & 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

'Set filter for overlap of dates
 [B] If (day3 >= day1 And day3 <= day2) Or (day1 >= day3 And day1 <= day4)[/B] Then
   
    If sen1 > sen2 Then
        flag = 1
        Range("E16").Value = flag
        Exit For
        End If
   If sen1 = sen2 Then
           If age1 > age2 Then
             flag = 1
             Range("E16").Value = flag
                Exit For
            End If
            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

I've made my filter change bold in the above. I'm not sure if the above code differs other than that so I suggest that you copy the above.
I think that I can now see how the final project will develop.

 

[DAD]

Hi Brian,

That is great work, you are right. It is almost there. I did discover one scenario where the algorithm failed. Sat for example Smith has leave from 1-15 Dec, Jones has leave from 2-3 Jan and Chan has leave from 25 Dec -1 Jan. At present, the algorithm will compare the 2, then decide based on seniority. In the above scenario of Jones was allocate leave when compared to Smith due to seniority, then Chan could also have leave as Smith has been denied due to the clash with Jones. Sounds like a tongue twister, so I hope you can get what I am saying.

Pete

 

[DAD]

Hi Brian,

I have been continuing to learn the code, and understanding the code you have written. I have been playing around with it, seeing if I can make some progress to the final solution. I have stumbled on a syntax problem I am hoping you can help with.

I currently have a variable called "PrimaryStartDate", and I use a second variable "PrimaryCount" to assign the value in a cell to the variable PrimaryStartDate as follows:

PrimaryStartDate = Range("C" & PrimaryCount).Value

I would like to use two variables in the above code to assign the cell value for PrimaryStartDate. The second variable for the cell value would be ColumnCount. I tried the following expression:

PrimaryStartDate = Range(ColumnCount, PrimaryCount).Value

However, I keep getting errors. I tried variations on this, including CELLS function, but nothing seemed to work. Can you shed some light on what may be the issue?

Pete

 

[BrianJN1]

Firstly, the cells syntax within the braces is (row, col).
Range basically uses text and so that must be enclosed in quotes, but a variable can be tagged to the text using the "&" to 'concatenate'.

I've taken your statement and written it into a small macro which may help explain in a more practical manner. I've also annotated it as well.

Sub pete()
Dim PrimaryStartDate, ColumnCount, PrimaryCount As Long
'these two variables are set at row 1 and column 1
ColumnCount = 1
PrimaryCount = 1
' The cells syntax is row,column.  Here cell "A1" is copied to PrimaryStartDate
' and then made to appear in "A2"
PrimaryStartDate = Cells(PrimaryCount, ColumnCount).Value
Range("A2").Value = PrimaryStartDate
End Sub

 

[Dim Me as xlNoob]

Missed it... Sorry for the useless post.