VBA Find and Offset

I'm trying to find a cell that I know it's there, but always tells me that there's not. Rng = Nothing

Set ws = Worksheets("Sheet1")
ws.Cells.UnMerge
Range("A1").Select
Set Rng = ws.Cells.find(What:="31/1/2019", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False)
Range(Selection, Selection.Offset(0, 9)).Select
Selection.Copy
Workbooks("Other.xlsm").Activate
ActiveWorkbook.Sheets("Sheet1").Select
Range("A1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial xlValues
Workbooks("Other.xlsx").Close SaveChanges:=False

3 Answers

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.