Keep getting Error 91 with Excel Find function

Question

I've tried the suggestions on this site and none seem to work.

In cells C6:Z6, I have dates 01/01/2011 through to 01/12/2012 (in UK date format). I run the following macro:

Sub FindDate()

    Range("C6:Z6").Select

    Selection.Find(What:="01/08/2012", After:=ActiveCell, LookIn:=xlFormulas _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate

End Sub

and always seem to get Run-time error 91. I've tried using 'Set' to the set the range and that doesn't do it either.

For context, I'm trying to obtain the column number for a preset date (using ActiveCell.Column).

Answer 1

When you simply search for "01/08/2012", you are actually searching for a string. You have to convert it to a date using CDate.

Also it is better to check if anything is found using If Not aCell Is Nothing Then to avoid any errors. See my post in this link.

Try this

Sub FindDate()
    Dim aCell As Range

    Set aCell = Range("C6:Z6").Find(What:=CDate("01/08/2012"), After:=ActiveCell, _
    LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

    If Not aCell Is Nothing Then
        MsgBox aCell.Column
    Else
        MsgBox "Not Found"
    End If
End Sub