Vba Find and Offset

Consider:

Dim d As Date
d = DateSerial(2019, 1, 31)
Set Rng = ws.Cells.Find(What:=d, After:=ActiveCell, _
    LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

(You may have other errors)

The code is looking for a string "31/1/2019" and this string is not presented as a date in Excel. To present it as a date, change the name of the month and the day, like this: "1/31/2019":

Sub TestMe()

    Dim rng As Range

    Set rng = Worksheets(1).Cells.Find(What:="1/31/2019", After:=ActiveCell, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)

    If Not rng Is Nothing Then MsgBox rng.Address

End Sub

Of course, there are better ways to do it, for example writing What:=DateSerial(2019, 1, 31) and not caring what comes first and what comes second (as in Gary's solution.)

For the way Excel and VBA format date and search it, take a look here:

• Range.Find not making a difference between January and November (February and December) in VBA Excel

Something to note for the future, when you are trying to use a date as a criteria, you should put the date within #'s and not ""s. Your VBA will automatically change the date to match the format that your system is set up to use.

For example if I type in the following:

If ActiveCell.Value = #31/1/2018# Then
    a = 1
ElseIf ActiveCell.Value = "31/1/2018" Then
    a = 1
End If

The compiler AUTOMATICALLY changes it to this:

If ActiveCell.Value = #1/31/2018# Then
    a = 1
ElseIf ActiveCell.Value = "31/1/2018" Then
    a = 1
End If

Obviously this won't help if you are looking for Feb 1st as 1/2/19...but it does help on the dates that can't exist.