VBA Lookup multiple ranges.

[Kris75]

Hi

I'm trying to develop a holiday request card whereby the user can enter a start date and and end date.
This information is entered using a userform and is then recorded in separate columns on the worksheet.

What i need to do is to set up a way whereby the user cannot enter the same dates, or dates within the same period.

Column E will contain all the start Dates ( E10 = 01/01/2012)
Collumn F will contain all the End Dates (F10 = 03/01/2012)

[TABLE="width: 489"]
<TBODY>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][B]0</SPAN>[/B]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][B]Type of</SPAN>[/B]
[/TD]
[TD][B]Date of</SPAN>[/B]
[/TD]
[TD="colspan: 2"][B]Inclusive</SPAN>[/B]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][B]Request</SPAN>[/B]
[/TD]
[TD][B]Request</SPAN>[/B]
[/TD]
[TD][B]From</SPAN>[/B]
[/TD]
[TD][B]To</SPAN>[/B]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1</SPAN>
[/TD]
[TD]Holiday</SPAN>
[/TD]
[TD][/TD]
[TD]01/01/2012</SPAN>
[/TD]
[TD]01/02/2012</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

Can anyone Help

 

[venkat1926]

the sample data is like this in sheet1.

Excel Workbook
	A	B	C	D	E	F
1	*	*	*	*	*	*
2	*	*	*	*	*	*
3	*	*	*	*	*	*
4	*	*	*	*	*	*
5	*	*	*	*	*	*
6	*	*	*	*	*	*
7	*	*	*	*	*	*
8	*	*	*	Type of	Date of	Inclusive
9	*	Request	Request	From	To	*
10	*	1	Holiday	*	1-Jan-12	1-Feb-12
11	*	2	*	*	5-Feb-12	*
12	*	3	*	*	*	*
13	*	4	*	*	*	*
14	*	5	*	*	*	*
15	*	*	*	*	*	*
Sheet1

now right click the tab of shee1 and click view code
in the window that comes up copy paste this event handler

if somebody types any date on E11 two alternative message may come up

1. "the date is not avaliable"

or

2.avaialble. click ok

now keep one point in view
in SOME REGIONS in excel the date is entered as month/date/year (in m y region)
in some other regions it is d/m/yy

find out which is your regional configuration and enter dates (including what you have already entered) correctly otherwise the event handler will give wrong answer

event handler

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r1 As Range, r2 As Range
Dim rbeg As Long, rend As Long
If Target.Column <> 5 Then Exit Sub


Application.EnableEvents = False
Set r1 = Range(Target, Target.End(xlUp).Offset(1, 0))


rbeg = WorksheetFunction.Min(r1)
rend = WorksheetFunction.Max(r1.Offset(0, 1))
If Target >= rbeg And Target <= rend Then
MsgBox "the date is not available"
Else
MsgBox "available. click ok"
End If
Application.EnableEvents = True




End Sub

 

[Kris75]

Hi VenKat


Thank you so much that's great!

Will it work across two worksheets?

Sheet 1 = Request Sheet
Sheet 2 = Log

 

[venkat1926]

one way of doing it is right click each tab and click view code and copy the code .
perhaps this work if the relevant sheet is made active sheet.

of course you can make it a workbook event handler and need slight modification