Error 424 Object not found

[silentwolf]

Hi got following code to delet entire row if an word is chosen in an input box.

Sub a()
Dim rngReporter As Range, strErsteAdresse As String, Suchbegriff

Suchbegriff = InputBox("Suche nach", Title:="Löschen")
If Len(Suchbegriff) > 0 Then
    With ActiveSheet.UsedRange
         Do
              Set rngReporter = .Find(Suchbegriff, lookat:=xlPart, LookIn:=xlValues, MatchCase:=False)
        If rngReporter Is Nothing Then Exit Do
              rngReporter.EntireRow.Delete
        Loop Until rngReporter.Address = strErsteAdresse
    End With
End If

However I do get an runtime error of 424 object not found in this line of code

        Loop Until rngReporter.Address = strErsteAdresse

does anyone know about this problem or knows how I could or need to change it so it works??
At the moment it findes me only one line and delete it

Thanks for any help!!
Albert

 

[VoG]

Hi. You need to use a loop with FindNext. This is the example from the Help file:

With Worksheets(1).Range("a1:a500")
    Set c = .Find(2, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Interior.Pattern = xlPatternGray50
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

 

[silentwolf]

Hi Vogt,
thanks for your reply! But as I am not that fit in VBA could you be so kind to let me know how my code would need to be changed??
I am sorry but just not sure.
Thanks

Albert

 

[VoG]

Try

Sub a()
Dim rngReporter As Range, strErsteAdresse As String, Suchbegriff
On Error Resume Next
Suchbegriff = InputBox("Suche nach", Title:="Löschen")
If Len(Suchbegriff) > 0 Then
    With ActiveSheet.UsedRange
        Set rngReporter = .Find(Suchbegriff, lookat:=xlPart, LookIn:=xlValues, MatchCase:=False)
        If Not rngReporter Is Nothing Then
                strErsteAdresse = rngReporter.Address
            Do
                rngReporter.EntireRow.Delete
                Set rngReporter = .FindNext
            Loop While Not rngReporter Is Nothing And rngReporter.Address <> strErsteAdresse
        End If
    End With
End If
End Sub

 

[silentwolf]

Hi VoG,
sorry I miss spelled your name first!!
It worked fantastic!!
Really appreciate your help alot!

Cheers
Albert