CODEWITHSUNDEEP
excelexcel-2007runtime-errorvba
Sputnik
Sputnik

Reputation: 123

Keep getting Error 91 with Excel Find function

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).

Upvotes: 0

Views: 6815

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

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

Upvotes: 1

Related Questions