VBA .find Method on Date Values

[dhancy]

Is there a "trick" to using the .find method in VBA to find a Date value?

I have always used something like this to find string values:

Set x = ws.Rows("1:1").Find(varString, LookIn:=xlValues)

where varString is a character string.


It works great. However, when searching for a date value, it's not working the same way.

I've tried many things:

- declaring varString as a date value
- search for CDate(varString)
- using various format strings on the date

Any other suggestions are greatly appreciated. Thanks!


Dennis

 

[Joe4]

Check out the "Dates in the FIND Method" section at the bottom of this link here:
VBA Procedures For Dates And Times

 

[dhancy]

Thank you !!!!

I've been looking at this all morning, and getting nowhere.

I used both the DateValue function to convert to a date, and also changes xlValues to xlFormulas.

It works great! THANK YOU !!!!

 

[Joe4]

You are welcome! Glad I was able to help.

 

[Paul_C]

Check out the "Dates in the FIND Method" section at the bottom of this link here:
VBA Procedures For Dates And Times

Thank you for the post. I am new to Excel VBA. After struggling so many times to search a date in a range, I came across your post.

The code works smoothly, if the date I want to search is in the range. If the date I am trying to search is not in the range I get the Run-time error 91 message - Object variable or With block variable not set. Would you please advise how to fix it? Thank You.

My code:
Sub FindCell()


Dim FoundCell As Range


Set FoundCell = Range("A1:A15").Find(what:="10/2/2018") 'date in US format


Debug.Print FoundCell.Address
Debug.Print FoundCell.Value


End Sub

 

[MARK858]

Try

Sub FindCell()


    Dim FoundCell As Range


    Set FoundCell = Range("A1:A15").Find(what:="10/2/2018")    'date in US format

    If Not FoundCell Is Nothing Then
Debug.Print FoundCell.Address
Debug.Print FoundCell.Value
    Else
        Exit Sub
    End If
End Sub

 

[steve the fish]

Id use this to replace your Set line:

myFind = DateSerial(2018, 10, 2)
Set FoundCell = Range("A1:A15").Find(What:=myFind, LookAt:=xlWhole)

 

[Paul_C]

Try

Sub FindCell()


    Dim FoundCell As Range


    Set FoundCell = Range("A1:A15").Find(what:="10/2/2018")    'date in US format

    If Not FoundCell Is Nothing Then
Debug.Print FoundCell.Address
Debug.Print FoundCell.Value
    Else
        Exit Sub
    End If
End Sub

Thanks Mark. Works beautifully.

 

[Ejazif]

Please check the function

 

[Joe4]

Please check the function

What exactly is your question?
Note that you have posted to a thread that is over 2 years old!