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.
