CODEWITHSUNDEEP
excelvba
Felipe
Felipe

Reputation: 163

Unable the get the findnext property of the Range class

My code is supposed to look for some values in j column and, if they are found, delete the row between columns 1 to 11. However, I´m always getting the error "Unable the get the findnext property of the Range class" associated to "Set c = .FindNext(c)". What´s the problem? Thanks!!!

Sub ExcluirGraosIncompletos()


    Application.ScreenUpdating = False

    Dim c As Range
    Dim ClearAddress As String
    Dim ClearRow As Long


  With Worksheets("ListBCOGJ").Range("J4:J17780")


      Set c = .Find(What:=6, LookIn:=xlValues, LookAt:=xlWhole, _
                                         MatchCase:=True)
      If Not c Is Nothing Then
          ClearAddress = c.Address
          Do
             ClearRow = c.Row
             Range(Cells(ClearRow, 1), Cells(ClearRow, 11)).Delete Shift:=xlUp
             Set c = .FindNext(c)
          Loop While c.Address <> ClearAddress
      End If

      Set c = .Find(What:=5, LookIn:=xlValues, LookAt:=xlWhole, _
                                         MatchCase:=True)
      If Not c Is Nothing Then
          ClearAddress = c.Address
          Do
             ClearRow = c.Row
             Range(Cells(ClearRow, 1), Cells(ClearRow, 11)).Delete Shift:=xlUp
             Set c = .FindNext(c)
          Loop While c.Address <> ClearAddress
      End If

      Set c = .Find(What:=42, LookIn:=xlValues, LookAt:=xlWhole, _
                                         MatchCase:=True)
      If Not c Is Nothing Then
          ClearAddress = c.Address
          Do
             ClearRow = c.Row
             Range(Cells(ClearRow, 1), Cells(ClearRow, 11)).Delete Shift:=xlUp
             Set c = .FindNext(c)
          Loop While c.Address <> ClearAddress
      End If

      Set c = .Find(What:=66, LookIn:=xlValues, LookAt:=xlWhole, _
                                         MatchCase:=True)
      If Not c Is Nothing Then
          ClearAddress = c.Address
          Do
             ClearRow = c.Row
             Range(Cells(ClearRow, 1), Cells(ClearRow, 11)).Delete Shift:=xlUp
             Set c = .FindNext(c)
          Loop While c.Address <> ClearAddress
      End If

      Set c = .Find(What:=36, LookIn:=xlValues, LookAt:=xlWhole, _
                                         MatchCase:=True)
      If Not c Is Nothing Then
          ClearAddress = c.Address
          Do
             ClearRow = c.Row
             Range(Cells(ClearRow, 1), Cells(ClearRow, 11)).Delete Shift:=xlUp
             Set c = .FindNext(c)
          Loop While c.Address <> ClearAddress
      End If

      Set c = .Find(What:=1, LookIn:=xlValues, LookAt:=xlWhole, _
                                         MatchCase:=True)
      If Not c Is Nothing Then
          ClearAddress = c.Address
          Do
             ClearRow = c.Row
             Range(Cells(ClearRow, 1), Cells(ClearRow, 11)).Delete Shift:=xlUp
             Set c = .FindNext(c)
          Loop While c.Address <> ClearAddress
      End If

      Set c = .Find(What:=4, LookIn:=xlValues, LookAt:=xlWhole, _
                                         MatchCase:=True)
      If Not c Is Nothing Then
          ClearAddress = c.Address
          Do
             ClearRow = c.Row
             Range(Cells(ClearRow, 1), Cells(ClearRow, 11)).Delete Shift:=xlUp
             Set c = .FindNext(c)
          Loop While c.Address <> ClearAddress
      End If

      Set c = .Find(What:=2, LookIn:=xlValues, LookAt:=xlWhole, _
                                         MatchCase:=True)
      If Not c Is Nothing Then
          ClearAddress = c.Address
          Do
             ClearRow = c.Row
             Range(Cells(ClearRow, 1), Cells(ClearRow, 11)).Delete Shift:=xlUp
             Set c = .FindNext(c)
          Loop While c.Address <> ClearAddress
      End If

      Set c = .Find(What:=27, LookIn:=xlValues, LookAt:=xlWhole, _
                                         MatchCase:=True)
      If Not c Is Nothing Then
          ClearAddress = c.Address
          Do
             ClearRow = c.Row
             Range(Cells(ClearRow, 1), Cells(ClearRow, 11)).Delete Shift:=xlUp
             Set c = .FindNext(c)
          Loop While c.Address <> ClearAddress
      End If

      Set c = .Find(What:=60, LookIn:=xlValues, LookAt:=xlWhole, _
                                         MatchCase:=True)
      If Not c Is Nothing Then
          ClearAddress = c.Address
          Do
             ClearRow = c.Row
             Range(Cells(ClearRow, 1), Cells(ClearRow, 11)).Delete Shift:=xlUp
             Set c = .FindNext(c)
          Loop While c.Address <> ClearAddress
      End If


  End With

      Application.CutCopyMode = False
      Range("A1").Select

   Application.ScreenUpdating = True

End Sub

Upvotes: 0

Views: 3186

Answers (1)

mattboy
mattboy

Reputation: 2910

It's because you're deleting the range that your Find is referencing.

Moving the FindNext up a line so that it's updated before the row is deleted should probably work, like so: 

ClearRow = c.Row
Set c = .FindNext(c)
Range(Cells(ClearRow, 1), Cells(ClearRow, 11)).Delete Shift:=xlUp

Upvotes: 1

Related Questions