Run Time Error 91

[rjplante]

I have an macro and it is failing on the last line in the code block. The worksheet that this is searching on does contain the text system options. When I select column A and perform a find for that text, it finds the exact row just fine. Yet when I run my macro, it fails on this line with a run time error 91 - Object Variable or With block variable not set. I have the range declared up above, so I don't know what the issue is. I have a second macro that executes a similar function and fails in the same manner (macro 2) with the last line failing to find the text. Why is the system unable to execute a find? The file is saved as .xlsm format so all of the macros should be working. I am confused...

Macro 1:

Dim FindRow As Range

With Sheets("Pricing Sheet")
    Set FindRow = .Range("A:A").Find(What:="System Options", LookIn:=xlValues, LookAt:=xlWhole)
End With

Sheets("Pricing Sheet").Range("A" & FindRow.Row + 1).Select

Macro 2:

Dim newLrow As Long
Dim GrandTotal As Range

With Sheets("Component List")
   Set GrandTotal = .Range("A:A").Find(What:="SYSTEM TOTAL", LookIn:=xlValues, LookAt:=xlWhole)
End With
        
newLrow = GrandTotal.Row

 

[Marc L]

​

In both macros VBA procedures you forgot to check if the range is Nothing like you can see in the VBA help example.​

​

The Range.Find method always works when the logic is respected …​

 

[Joe4]

Try changing this part of your code:

LookAt:=xlWhole

to this:

LookAt:=xlPart

and see if that works.

 

[rjplante]

Marc, thanks for the input on that line. I agree it needs to be fixed, however, there seems to be something else going on. I restarted my computer to clean up any other possibilities and opened up a clean template of my workbook. I made some selections in column B for various rows. I then run a macro to filter out all rows greater than zero and any blank rows. When I run this macro it fails on the If then line below.

With ActiveSheet.AutoFilter
    If .FilterMode = True Then
      .ShowAllData

What could be the cause of this failure. Everything else has run just fine before today and now it has started failing. It appears that the last update to Office 365 is on Jan 12. I was working on this last Tuesday and everything worked as expected. Then today, everything is wonky. I don't get it.

 

[rjplante]

Stumbled on the fix using dumb luck. I had a single column which had the filtering turned on for that column. My macros utilize that column for the filtering process. It somehow got turned off. All is better now that I turned the filtering back on for that column.