Run-time error 91 - object variable or with block variable not set

[bicke]

Hi everyone,

I'm trying to troubleshoot a run-time error 91 on a macro in my workbook. Essentially this macro looks through active worksheets to find any issues (special formatted cells in active worksheets) and returns the comments in these formatted cells in a 'issue worksheet' (I hope that makes sense!).

Anyway, i get a 'run-time error 91 - object variable or with block variable not set' issue on the following code line:

iLastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

It looks like this VBA line is searching for something that doesn't exist (e.g. a previous worksheet which has been deleted) but I've tried to troubleshoot every possible reason/solution and can't remove this error.

Does anyone have any suggestions about what could be causing this?

Thanks in advance for your help.
Cheers,
Bicke.

 

[Trebor76]

Re: Run-time error 91 - object variable or with block variable not set - troubleshooting help!

Hi bicke,

There's no data on the active tab to set the variable. Have a look at the following where the code first checks if there's any data on the active tab before trying to set the iLastRow variable:

Option Explicit
Sub Macro2()

    Dim iLastRow As Long
    
    'If there's some data on the current tab, then...
    If WorksheetFunction.CountA(Cells) > 0 Then
        '...set the 'iLastRow' varaible
        iLastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End If

End Sub

HTH

Robert

 

[bicke]

Re: Run-time error 91 - object variable or with block variable not set - troubleshooting help!

Hi Robert,

You are a legend! Thanks so much for your help. Yes, I found the error so it all works now. I can't thank you enough and I owe you a beer if you ever visit Perth!

Thanks again.
Cheers,
Bicke.

 

[Trebor76]

Re: Run-time error 91 - object variable or with block variable not set - troubleshooting help!

NP - glad we got it sorted. I haven't been to Perth for years but if I do it's a date (so to speak)