Holiday range lookup?

**lian.cragg** · 06-15-2009, 07:02 AM

Hi folks,

Well I need a little help, iv attached a shortened version of what im trying to do with test data, don't laugh! iv only been doing the vb bit for a few months and its rather simple.

What i need to do is when the availability button is clicked on the employee sheet, it will lookup the employee from whats been selected and check against the holiday sheet (which will be hidden later on) and check from the from and tofrom dates from the button and possibly return just a 'yes' in a cell two down from the button, or possibly just a Yes or No in a msgbox.

Iv attached my example, eventually this will include a lot more employees etc. Iv been racking my brain today and must be having a thick day as I can't figure it at all.....

Thank you in advance

**JBeaucaire** · 06-15-2009, 10:17 AM

How about this:

Private Sub CommandButton3_Click()
On Error Resume Next
from = Application.InputBox(Prompt:="Please enter from date", Title:="From Date", Default:="Example: 01/01/2009")
tofrom = Application.InputBox(Prompt:="Please enter to date", Title:="To Date", Default:="Example: 31/01/2009")
    
If from = False Or from Like "Example:*" Or tofrom = False Or tofrom Like "Example:*" Then
    MsgBox "Dates were not entered correctly, please try again."
    Exit Sub
End If

myrow = WorksheetFunction.Match(Range("C9"), Sheets("HOLS").Range("A:A"), 0)
Cols = Sheets("HOLS").Cells(myrow, Columns.Count).End(xlToLeft).Column
For i = 1 To Cols
    If Sheets("HOLS").Cells(myrow, i) = from Or Sheets("HOLS").Cells(myrow, i) = tofrom Then
        MsgBox "Not available"
        Exit Sub
    ElseIf Sheets("HOLS").Cells(myrow, i) > from And Sheets("HOLS").Cells(myrow, i) < tofrom Then
        MsgBox "Not available"
        Exit Sub
    End If
Next i
        
    MsgBox "Available"
    Range("D61").Value = from
    Range("E61").Value = tofrom

End Sub

NOTE: you only need to put the "on error resume next" line in a macro once unless you later change it to something else, it's in effect all the time.

**lian.cragg** · 06-15-2009, 10:51 AM

Worked a treat!!!!

Much love, from a very dizzy blonde!! :o) x

**JBeaucaire** · 06-15-2009, 12:12 PM

You're welcome. If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)