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

 

[jolivanes]

Just explain what you want to achieve.
The code from Post #1 needs cleaning anyway. Too many "Selects"

 

[Rasberry]

Just explain what you want to achieve.
The code from Post #1 needs cleaning anyway. Too many "Selects"

I want to find "#N/A N/A" in a certain range of cells, and then delete the cell, and shift all cells below it up. Once it has cycled through the range, I want to exit the sub. What it does right now is once it has cleaned up all the "#N/A N/A" cells, and can't find anymore, it basically stops on the cells.find code line and the macro stops working rather than just exiting the sub.

 

[jolivanes]

Re: "For i = 1 To End_of_Monthly_Dates * 2"
Why the *2?
Re: "certain range of cells" Be more specific.

Have you tried with AutoFilter?

 

[Rasberry]

I think you are getting lost in the peripheral items. There are multiple ways I can define the range. I just need code to exit the loop once it cannot find the #N/A.

 

[jolivanes]

Nothing serious. Just try to give you a better alternative.

 

[rlv01]

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

 

[Alex Blakenburg]

I agree with Jolivanes that this is a rather odd way of doing this. Typically you would use a Do Until Loop.
Since @rlv01's code doesn't actually exit the For loop when there is nothing more to find, here is a variation of that code.

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("g" & .Rows.Count).End(xlUp).Row
        .Range("E10").Select
        For i = 1 To End_of_Monthly_Dates * 2
            Set rngFind = .Cells.Find(What:="#N/A N/A", After:=ActiveCell, _
                            LookIn:=xlFormulas2, LookAt:=xlPart, SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            If rngFind Is Nothing Then
                Exit For
            End If
            rngFind.Delete Shift:=xlUp

        Next i
    End With
End Sub

PS: I split out "If rngFind Is Nothing Then Exit For" into multiple lines so that I could put a break point on the Exit For to see where it exited.

 

[rlv01]

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.

 

[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!!! As the other two models do as well. Thank you so much!