Run-time error '91' on Cells.Find()

  • Thread starter Thread starter Vera
  • Start date Start date
V

Vera

Hi,

I recorded a macro to find a date in a worksheet. The
generated code looks like this:

Range("A2").Select
Application.FindFormat.Clear
Application.FindFormat.NumberFormat = "dd/mm/yyyy"
Application.FindFormat.....
....
Cells.Find(What:="20-01-2004", After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=True).Activate

However, when I try to run this macro, I get an error
message: Run-time error '91': Object variable or With
block variable not set. When I click debug, the error
appears to be caused by the Cells.Find() statement.

My intension is to create a ThisWorkbook.Open method that
finds the current date in the worksheet.

What can I do to make this macro work?
Any help will be greatly appreciated.
 
This is a little bit tricky. If you do a search you will find several
suggested methods that do not work. Here is one that does.

'-----------------------------------------
Sub FindToday()
Dim MyDateFormat As String
Dim MyDate As String
Dim FoundCell As Object
'-----------------------------------------
' NB. Date format must be same as worksheet.
'MyDateFormat = "dd/mm/yyyy" '...alternative version
MyDateFormat = ActiveSheet.Range("A2").NumberFormat
MyDate = Format(Now, MyDateFormat)
'----------------------------------------
Set FoundCell = ActiveSheet.Cells.Find(MyDate, LookIn:=xlValues)
If FoundCell Is Nothing Then
MsgBox (MyDate & " not found in " & ActiveSheet.Name)
Else
Application.Goto reference:= _
ActiveSheet.Range(FoundCell.Address)
End If
End Sub
'---------------------------------------------
 
Back
Top