Holiday range lookup?
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
How about this:
Code: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.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Worked a treat!!!!
Much love, from a very dizzy blonde!! :o) x
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)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks