cells.find error handling

[Rasberry]

I can't seem to get the correct error code handling for ending the sub when there are no more #N/A's to find. It just stops the code:

Sub RunMacro_Remove_NA()
Dim End_of_Monthly_Dates As Integer
Range("D9").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.End(xlDown).Select
End_of_Monthly_Dates = ActiveCell.Row
ActiveCell.Offset(0, 2).Select
EndRange = Selection
Range("E10").Select
For i = 1 To End_of_Monthly_Dates * 2
Cells.Find(What:="#N/A N/A", After:=ActiveCell, LookIn:= _
xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Delete Shift:=xlUp
Next i
End Sub

 

[Rasberry]

In keeping with your general programming strategy, perhaps something like this.

Sub RunMacro_Remove_NA()
    Dim End_of_Monthly_Dates As Long
    Dim i As Long
    Dim rngFind As Range
   
    With ActiveSheet
        End_of_Monthly_Dates = .Range("D" & .Rows.Count).End(xlUp).Row
        .Range("E10").Select
        For i = 1 To End_of_Monthly_Dates * 2
            Set rngFind = Nothing
            On Error Resume Next
            Set rngFind = .Cells.Find(What:="#N/A N/A", After:=ActiveCell, _
            LookIn:=xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            On Error GoTo 0
            If Not rngFind Is Nothing Then
                rngFind.Delete Shift:=xlUp
            End If
        Next i
    End With
End Sub

This works! Thank you so much!

 

[Rasberry]

Further to what @Alex Blakenburg said, a more standard approach would be a Do-Until loop. An example:

Sub RunMacro_Remove_NA2()
    Dim rngFind As Range, SearchRange As Range
   
    Set SearchRange = ActiveSheet.UsedRange
    With SearchRange
        Do
            Set rngFind = .Cells.Find(What:="#N/A N/A", LookIn:=xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            If Not rngFind Is Nothing Then
                rngFind.Delete Shift:=xlUp
            End If
        Loop Until rngFind Is Nothing
    End With
End Sub

For the example, I am defining the search range as the used range of the ActiveSheet, but it can be defined in other ways depending on what cells you want to limit the search to.

This works! Thank you so much!

 

[Alex Blakenburg]

Thanks for testing all the suggestions. The Do Loop method provided by @rlv01 is the way to go.
Your case where you are deleting the Found Cell is the simplist use case for it. Doing a replace will work too as long as you are replacing it in such a way that Find will not find it anymore (espcially if you are using xlPart).
If there is a risk of it looping back onto itself either because you didn't make any changes or the changed cell still makes the find criteria, the Loop will need some additional code.

 

[jolivanes]

Because you initially mentioned you were not interested in an alternative code, see Post #5, I was waiting for people to answer your original request.
It now looks like everybody had the same idea I originally had. A rewrite of the code.
So, for whatever it's worth, here would have been my suggestion as Post #6. And no looping I might add.
Change references where required.

Sub Delete_Double_NAs()
With Range("E10:E" & Cells(Rows.Count, 4).End(xlUp).Row)
    .AutoFilter Field:=1, Criteria1:="#N/A N/A"
    With .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
        .Parent.AutoFilterMode = False
        .Delete (xlShiftUp)
    End With
End With
End Sub

You can, or maybe should, include "Application.ScreeUpdating" in it.