Run-time error '91' on find operation

[riggsd]

I'm getting a Run-time error '91': Object variable or with block variable not set on the Cells.Find code below. It works fine when I create it as a macro and run, but not in the VBA code.

    ' select CSCIs sheet to format
    Set Format_Sheet = Sheets("CSCIs")
    ' activate sheet
    Format_Sheet.Activate
    
    ' find associated documentation cell
    Cells.Find(what:="Associated Documentation", After:=ActiveCell, LookIn:= _
        xlFormulas, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate

    ' set start row to row where title is
    startRow = ActiveCell.Row

Any ideas?

 

[Tazguy37]

You could try this:

Dim startRow As Long

On Error Resume Next
startRow = Cells.Find(what:="Associated Documentation", After:=ActiveCell, LookIn:= _
        xlFormulas, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Row

If Err.Number <> 0 Then MsgBox "Not found"

If you get that error with this, it probably means it can't find the text. I added the Err.Number so you can detect those errors. HTH

 

[riggsd]

Thanks, I'll try that. The text is there though, it's a standard section of the report.

 

[riggsd]

The code works, but it's not finding the text. I can go into the worksheet, do a find and it finds it.

Could it be because the text is in a merged cell?

- Added -

No, that's not the reason since it won't find anything, just says not found even if the text isn't in a merged cell.