cells.find error handling

Rasberry

Board Regular
Joined
Aug 11, 2002
Messages
201
Office Version
  1. 365
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
 
In keeping with your general programming strategy, perhaps something like this.
VBA 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("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!
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Further to what @Alex Blakenburg said, a more standard approach would be a Do-Until loop. An example:
VBA Code:
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!
 
Upvote 0
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.
 
Upvote 0
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.
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,224,596
Messages
6,179,802
Members
452,943
Latest member
Newbie4296

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top