Excel VBA - Using Find method on a range of dates

Question

I am trying to find if a certain date is in a range of dates. This is the range of dates:

01/01/2013
11/02/2013
29/03/2013
20/05/2013
01/07/2013
05/08/2013
02/09/2013
14/10/2013
11/11/2013
25/12/2013
26/12/2013

Here is the VBA code:

  ' Format Holiday Rows '
        With ConfigData.Range("B8:B18")
            Set holidays = .Find(s1.Cells(row_count, 1))

            If Not holidays Is Nothing Then
                MsgBox s1.Cells(row_count, 1)
            End If
        End With

In the above code, the first MsgBox that pops up reads "11/01/2013". This makes absolutely no sense, as that value is not in the range.

Note: ConfigData.Range("B8:B18") refers to the range of dates shown above.

ALSO: This code is within a for loop that increments the value of s1.Cells(row_count, 1). Starting at 01/01/2013 until 31/12/2013

Answer 1

If you just want to confirm a calendar day in your series is within the holiday list, then you could even use vlookup:

Dim strFound As String

On Error Resume Next
strFound = Application.Vlookup(s1.Cells(row_count, 1), .Range("B8:B18"), 1, 0)
If IsError(strFound) Then
   MsgBox "Not Found"
Else
'-- Found
End If
On Error GoTo 0

Answer 2

It is important to note that excel uses american date formatting. ie mm/dd/yyyy and it can therefore be a little tricky to get the .Find() function to work properly. Make sure your variables are formated properly in order for excel to hopefully give you what you're looking for:

Dim strdate As String
Dim aCell As Range

strdate = ActiveSheet.Cells(1,1)
strdate = Format(strdate, "Short Date")
On Error Resume Next
    Set aCell = Cells.Find(What:=CDate(strdate), After:=Range("A1"), LookIn:=xlFormulas , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

If rCell Is Nothing Then
    MsgBox("Date cannot be found. Try Again")
End If
End Sub

Of course there are a lot of annoying things that can happen with the date formatting, but this is assuming the dates you're looking for ar in the "Short Date" format.

Answer 3

'To find a cell elsewhere in a worksheet with the same specific date as a reference cell: 'First copy all dates to cells immediately to their left. 'Format the copied cells as "General" 'Run this code - then use the dateRow and DateCol variables (eg in vlookup) 'Works in Excel 2013 (The "General" column must not be hidden - Hide by formatting in background colour)

Dim dateVal
Dim DateRow
Dim DateCol

dateVal = Range("j8").Value 'must be in general format

Cells.Find(What:=dateVal, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate

DateRow = ActiveCell.Row
DateCol = ActiveCell.Column

MsgBox (DateRow & "   " & DateCol)

End Sub

Answer 4

The following code works for me:

Sub thing()

    Dim cell As Range, _
        holidays As Range

    For Each cell In Range("D1:D365")
        With Range("A1:A11")
            Set holidays = .Find(cell.Value, LookIn:=xlValues, lookat:=xlWhole)

            If Not holidays Is Nothing Then
                Debug.Print cell.Value
            End If
        End With
    Next cell

End Sub

If this doesn't work, I'd suggest it's likely you have a cell formatting issue. Select one of your date cells. Go to the immediate window (Alt+F11, then Ctrl+G from Excel), type ? Selection.Value2 and press enter. Does that return a numeric value (~41000)?

Alternatively, you could reenter the dates in a completely new sheet (enter the first couple manually and drag down, do not copy and paste as formatting will be copied also) and try again. This should at least remove odd formatting as a potential issue.