search for date range - whole year

woon8888

New Member
Joined
May 18, 2022
Messages
13
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
i have a list of dates below that are restricted date & time. i will need to program a VBA that will detected if the entered dates fall between the restricted date/time if it falls between then an alert /msgbox will popup stating it's restricted date & time.

I've written the below code. but it only works on the same sheet. i cannot reference it if it's different sheet.

Any experts to help?

VBA Code:
Sub searchDate()
Dim strDate As String
Dim rCells As Range
Dim dCells As Date
Dim tidetbl As Worksheet

strDate = Range("I1").Value
dCells = Range("I1").Value

Set tidetbl = ThisWorkbook.Worksheets("Restricted Date")

strDate = Format(strDate, "Short Date")

Set rCell = Cells.Find(What:=CDate(strDate), After:=tidetbl.Range("A1"), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

If dCells >= rCell.Offset(0, 1) And dCells <= rCell.Offset(0, 2) Or dCells >= rCell.Offset(0, 3) And dCells <= rCell.Offset(0, 4) Then
    MsgBox ("Restricted Date")
Else
    MsgBox ("OK Date")
End If
End Sub

For e.g. if the dates is 03/Jan 01:23 HRS then it's within the date Restricted date start and end.

below is only 2 months for reference. the actual one has got whole year which is not feasible to paste everything here.

Date StartDate EndDate (2) StartDate (2) End
1-Jan-23
2-Jan-23
3-Jan-2303-Jan 01:0003-Jan 06:00
4-Jan-2304-Jan 00:0004-Jan 07:00
5-Jan-2305-Jan 00:0005-Jan 08:00
6-Jan-2306-Jan 01:0006-Jan 08:00
7-Jan-2307-Jan 02:0007-Jan 09:00
8-Jan-2308-Jan 04:0008-Jan 09:00
9-Jan-2309-Jan 05:0009-Jan 10:00
10-Jan-2310-Jan 05:0010-Jan 11:00
11-Jan-2311-Jan 06:0011-Jan 11:00
12-Jan-2312-Jan 07:0012-Jan 12:00
13-Jan-2313-Jan 10:0013-Jan 11:00
14-Jan-23
15-Jan-23
16-Jan-23
17-Jan-23
18-Jan-2318-Jan 00:0018-Jan 05:0018-Jan 23:0018-Jan 23:59
19-Jan-2319-Jan 00:0019-Jan 06:0019-Jan 23:0019-Jan 23:59
20-Jan-2320-Jan 00:0020-Jan 07:0020-Jan 14:0020-Jan 15:00
21-Jan-2321-Jan 00:0021-Jan 08:0021-Jan 14:0021-Jan 17:00
22-Jan-2322-Jan 02:0022-Jan 09:0022-Jan 15:0022-Jan 18:00
23-Jan-2323-Jan 04:0023-Jan 10:0023-Jan 16:0023-Jan 18:00
24-Jan-2324-Jan 05:0024-Jan 11:00
25-Jan-2325-Jan 07:0025-Jan 12:00
26-Jan-2326-Jan 10:0026-Jan 12:00
27-Jan-23
28-Jan-23
29-Jan-23
30-Jan-2330-Jan 22:0030-Jan 23:5930-Jan 22:0030-Jan 23:59
31-Jan-2331-Jan 00:0031-Jan 04:0031-Jan 22:0031-Jan 23:59
1-Feb-2301-Feb 00:0001-Feb 05:0001-Feb 22:0002-Feb 00:00
2-Feb-2302-Feb 00:0002-Feb 06:0002-Feb 23:0003-Feb 00:00
3-Feb-2303-Feb 00:0003-Feb 07:00
4-Feb-2304-Feb 01:0004-Feb 08:00
5-Feb-2305-Feb 03:0005-Feb 09:00
6-Feb-2306-Feb 04:0006-Feb 10:00
7-Feb-2307-Feb 06:0007-Feb 10:00
8-Feb-2308-Feb 07:0008-Feb 11:00
9-Feb-2309-Feb 08:0009-Feb 11:00
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
VBA Code:
Sub searchDate()
Dim strDate As String
Dim rCells As Range
Dim dCells As Date
Dim tidetbl As Worksheet

' Qualify the sheet that has the input dates.
strDate = Sheets("Input").Range("I1").Value
dCells = Sheets("Input").Range("I1").Value

Set tidetbl = ThisWorkbook.Worksheets("Restricted Date")

strDate = Format(strDate, "Short Date")

' Qualify the "Restricted Date sheet
Set rCell = tidetbl.Columns("A").Find(What:=CDate(strDate), After:=tidetbl.Range("A1"), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

' Add parenthisis to group the two AND criteria
If (dCells >= rCell.Offset(0, 1) And dCells <= rCell.Offset(0, 2)) Or (dCells >= rCell.Offset(0, 3) And dCells <= rCell.Offset(0, 4)) Then
    MsgBox ("Restricted Date")
Else
    MsgBox ("OK Date")
End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top