Unable to find date using cells.find method

[Niraj Baraili]

Hi All,

I am trying look up for a date in a range as below:

Sub Test()
Dim x As Date

x = Workbooks("Test.xlsm").Worksheets("sheet1").Range("D2")

Cells.Find(What:=x, After:=ActiveCell, LookIn:=xlFormulas, _
                    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False).Activate
End sub

While running this code it's showing run time error 9. Even i tried to find out the date using excel find method for the date "2/4/2016" and same is not found.

Any input on this appreciated.

 

[Marc L]

Hi !

Start with this thread …
​

 

[YasserKhalil]

Try this

Sub Test()
    Dim C As Range
    Dim Str As String
    Str = Format(ThisWorkbook.Worksheets("Sheet1").Range("D2").Value2, "m/d/yyyy")
    Set C = ActiveSheet.Columns(1).Find(What:=Str, LookIn:=xlValues)
    If Not C Is Nothing Then C.Select
End Sub

 

[Niraj Baraili]

Hi Khalil,
Thank you very much.
It works like charm.

 

[Marc L]

Or​

Sub Demo1()
    Dim Rg As Range
    Set Rg = Sheet1.UsedRange.Columns(1).Find(CDate(Sheet1.[D2].Value2), , xlValues, xlWhole)
    If Not Rg Is Nothing Then Rg.Select: Set Rg = Nothing
End Sub

Do you like it ? So thanks to click on bottom right Like !

Here it's the international Excel format "m/d/yyyy" exception
that can't work with Text property …
​

 

[YasserKhalil]

Thanks Mr. MarcL for this great code
Or

    Dim C As Range, StrDate As Date
    StrDate = CLng(ThisWorkbook.Worksheets("Sheet1").Range("D2").Value2)
   
    Set C = ActiveSheet.Columns(1).Find(What:=StrDate, LookIn:=xlValues)
    If Not C Is Nothing Then C.Select

 

[Marc L]

Yasser, as Value2 is aready a Long value so no need CLng …

So your codeline StrDate = is same as CDate I used …

And do not forget to flush at end variables object !
​