Dates are tricky to find with the Range.Find
method. One of the issues is that in VBA, dates are of the Date
data type, but the worksheet does not have that data type. Rather the data type is a number that is formatted to look like a date.
One solution, if you can be certain of the format of the date on the worksheet, is to search for the string equivalent. Given your example, something like this will work:
Option Explicit
Sub GetDates()
Const findDate As Date = #5/11/2017#
Dim findStr As String
Dim R As Range, WS As Worksheet
Set WS = Worksheets("Sheet1")
findStr = Format(findDate, "dd-mmm")
With WS
Set R = .Cells.Find(what:=findStr, LookIn:=xlValues, lookat:=xlWhole)
If Not R Is Nothing Then MsgBox findDate & " found in " & R.Address
End With
End Sub
but it is not very robust since, in many cases, the user can change the format.
Another method that is more robust, would be to loop through the existing cells, looking for the numeric representation of the date (using the Value2
property):
Sub GetDates2()
Const findDate As Date = #5/11/2017#
Dim R As Range, C As Range, WS As Worksheet
Set WS = Worksheets("sheet1")
Set R = WS.UsedRange
For Each C In R
If C.Value2 = CDbl(findDate) Then MsgBox findDate & " found in " & C.Address
Next C
End Sub
If you have a large range to search, this can be sped up by a factor of ten by reading the range into a VBA array and looping through the array.