Cells.Find in VBA. Run-time error '91': Object variable or With block variable not set

[sueweber]

I have a simple macro which deletes all lines in a sheet containing a particular variable, "Hobbs" in the code below. I created the macro by recording a 'find'. The macro works fine in that it finds and deletes all the lines but at the end, when it can't find another occurrence of the variable, it comes up with the error message: Run-time error '91'. I just end the macro, but I would like to include this in a larger macro but can't with the error.

Sub Hobbs ()

FindAgain:

' Find row with Hobbs

    Cells.Find(What:="hobbs", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate


' Select the row and then delete it
   
    ActiveCell.Rows("1:1").EntireRow.Select
    Selection.Delete Shift:=xlUp
    GoTo FindAgain
    
End Sub

Sue

 

[RCBricker]

I have a simple macro which deletes all lines in a sheet containing a particular variable, "Hobbs" in the code below. I created the macro by recording a 'find'. The macro works fine in that it finds and deletes all the lines but at the end, when it can't find another occurrence of the variable, it comes up with the error message: Run-time error '91'. I just end the macro, but I would like to include this in a larger macro but can't with the error.

Sub Hobbs ()

FindAgain:

' Find row with Hobbs

    Cells.Find(What:="hobbs", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate


' Select the row and then delete it
   
    ActiveCell.Rows("1:1").EntireRow.Select
    Selection.Delete Shift:=xlUp
    GoTo FindAgain
    
End Sub

Sue

I would suggest capturing the error and taking action.

try this

Sub Hobbs()

FindAgain:

' Find row with Hobbs
On Error Resume Next
    Cells.Find(What:="hobbs", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

' Select the row and then delete it
    If Err.Number <> 0 Then
    Else
        ActiveCell.Rows("1:1").EntireRow.Select
        Selection.Delete Shift:=xlUp
        GoTo FindAgain
    End If
'On Error GoTo 0
End Sub

 

[Sektor]

Sub Hobbs()


    Dim cell As Range
    
    With Cells
        Set cell = .Find("hobbs", SearchDirection:=xlPrevious)
        If Not cell Is Nothing Then
            Do
                cell.EntireRow.Delete
                Set cell = .FindPrevious()
            Loop While Not cell Is Nothing
        End If
    End With


End Sub

 

[sueweber]

Hi RCBricker and Sektor
Many thanks for your replies and successful codes! This is the first time I've used the forum and it's wonderful. I had been trying to work out the code for ages using answers to various other similar problems with no success. I'm fairly new to VBA, so now I just have to work out why your two solutions work!
Regards
Sue

 

[RCBricker]

Hi RCBricker and Sektor
Many thanks for your replies and successful codes! This is the first time I've used the forum and it's wonderful. I had been trying to work out the code for ages using answers to various other similar problems with no success. I'm fairly new to VBA, so now I just have to work out why your two solutions work!
Regards
Sue

basically you were getting an error when there were no more sells with "hobbs" as a value. My code used your code, but went on to capture the error and take action when it occurs. I did that because I didn't want to change what you had going on (as I don't know what other code you have or have planned).

So the first change tells VBA that if an error occurs ignore it and continue on with the code.

On Error Resume Next

So when your cells.find can not find a match it just moves on with the code.

Next I use an IF statement to check if there was an error. Any error code number would be higher than 0. So if the error number is higher than 0 do nothing. ELSE do the rest of your code.

What Sektor did is the better way, but it changed your code.

First thing he does is sets a range variable to the location of the cell that contains "hobbs". He then checks to see if the range variable is nothing. if it is not nothing it deletes the row and then looks for the next cell with "hobbs". It loop till the range variable is set to nothing.

IF you have no previous code then use his (I would use his no matter what), otherwise you can use mine as it requires no possible changes to previous code.

 

[sueweber]

Dear RCBricker
Even better help! Thank you so much.
Best wishes
Sue