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

Solution

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

Comments

[chuff]

Could you show us the code that assigns s1 and row_count? (Also, you need to use .Value to access the value in a cell, as in Cells(row_count,1).Value).

[mkingston]

Funny, that's the first thing that occurred to me, but I thought I'd try the code and see what was wrong. I obviously forgot vlookup in the process :). +1, formulae before vba.

[bonCodigo]

@mkingston thanks :) In a usual day, when we just work with sheets (without any VBA), to look for something in a range, we would use vlookup, so thought it would be a better fit given holidays are unique so is the calendar ;)

[bonCodigo]

@user985779 is this answer fitting to your need? Please comment.

[mkingston]

@user985779 I've revised my answer. Let me know if it helps at all. Additionally, please respond to the comment thread on the other answer to your question. Thanks.