# search for date range - whole year



## woon8888 (Dec 31, 2022)

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?


```
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) End1-Jan-232-Jan-233-Jan-2303-Jan 01:0003-Jan 06:004-Jan-2304-Jan 00:0004-Jan 07:005-Jan-2305-Jan 00:0005-Jan 08:006-Jan-2306-Jan 01:0006-Jan 08:007-Jan-2307-Jan 02:0007-Jan 09:008-Jan-2308-Jan 04:0008-Jan 09:009-Jan-2309-Jan 05:0009-Jan 10:0010-Jan-2310-Jan 05:0010-Jan 11:0011-Jan-2311-Jan 06:0011-Jan 11:0012-Jan-2312-Jan 07:0012-Jan 12:0013-Jan-2313-Jan 10:0013-Jan 11:0014-Jan-2315-Jan-2316-Jan-2317-Jan-2318-Jan-2318-Jan 00:0018-Jan 05:0018-Jan 23:0018-Jan 23:5919-Jan-2319-Jan 00:0019-Jan 06:0019-Jan 23:0019-Jan 23:5920-Jan-2320-Jan 00:0020-Jan 07:0020-Jan 14:0020-Jan 15:0021-Jan-2321-Jan 00:0021-Jan 08:0021-Jan 14:0021-Jan 17:0022-Jan-2322-Jan 02:0022-Jan 09:0022-Jan 15:0022-Jan 18:0023-Jan-2323-Jan 04:0023-Jan 10:0023-Jan 16:0023-Jan 18:0024-Jan-2324-Jan 05:0024-Jan 11:0025-Jan-2325-Jan 07:0025-Jan 12:0026-Jan-2326-Jan 10:0026-Jan 12:0027-Jan-2328-Jan-2329-Jan-2330-Jan-2330-Jan 22:0030-Jan 23:5930-Jan 22:0030-Jan 23:5931-Jan-2331-Jan 00:0031-Jan 04:0031-Jan 22:0031-Jan 23:591-Feb-2301-Feb 00:0001-Feb 05:0001-Feb 22:0002-Feb 00:002-Feb-2302-Feb 00:0002-Feb 06:0002-Feb 23:0003-Feb 00:003-Feb-2303-Feb 00:0003-Feb 07:004-Feb-2304-Feb 01:0004-Feb 08:005-Feb-2305-Feb 03:0005-Feb 09:006-Feb-2306-Feb 04:0006-Feb 10:007-Feb-2307-Feb 06:0007-Feb 10:008-Feb-2308-Feb 07:0008-Feb 11:009-Feb-2309-Feb 08:0009-Feb 11:00


----------



## AlphaFrog (Dec 31, 2022)

```
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
```


----------



## woon8888 (Dec 31, 2022)

This worked like a charm! Kudos! @AlphaFrog


----------

