Kann nicht findnext-Eigenschaft des range-Klasse ist Fehler

Ich versuche zu analysieren, einen Bericht in Excel 2007. Es ist im Grunde ein Bericht von der Buchhaltung aufladen Ausnahmen. Der Bericht hat Abschnitte mit einem header für jede Art von Ausnahme. Es gibt Arten von Ausnahmen, die sind aus dem Bericht gelöscht. Ich bin mit einer Do-While-Schleife zu finden, jeden header und, wenn der Abschnitt gelöscht werden soll, habe ich es so zu tun. Wenn nichts gelöscht werden muß der code funktioniert gut, aber nach einem Abschnitt gelöscht wird, bekomme ich eine "Nicht in der Lage, um die FindNext-Eigenschaft des Range-Klasse" - Fehler. Hier ist mein code:

Sub merge_All_Section_Headers()
' Description:
' The next portion macro will find and format the Tranaction Source rows in the file
' by checking each row in column A for the following text: TRANSA.  If a cell
' has this text in it, it is selected and a function called merge_text_cells
' is run, which performs concatenation of each Transaction Source header row and
' deletes the text from the rest of the cells with broken up text.
'
lastRow = ActiveSheet.UsedRange.Rows.Count + 1
Range(lastRow & ":" & lastRow).Delete

ActiveSheet.PageSetup.Orientation = xlLandscape

With ActiveSheet.Range("A:A")
   Dim searchString As String

   searchString = "TRANSA"

   'The following sets stringFound to either true or false based on whether or not
   'the searchString (TRANSA) is found or not):
   Set stringFound = .Find(searchString, LookIn:=xlValues, lookat:=xlPart)

   If Not stringFound Is Nothing Then

      firstLocation = stringFound.Address

      Do
         stringFound.Select

         lastFound = stringFound.Address

         merge_Text_Cells

         If ((InStr(ActiveCell.Text, "CHARGE FILER") = 0) And _
             (InStr(ActiveCell.Text, "CREDIT FILER") = 0) And _
             (InStr(ActiveCell.Text, "PA MIDNIGHT FINAL") = 0) And _
             (InStr(ActiveCell.Text, "BAD DEBT TURNOVER") = 0)) Then

            section_Del 'Function that deletes unwanted sections

         End If

         Range(lastFound).Select

         Set stringFound = .FindNext(stringFound)

       Loop While Not stringFound Is Nothing And stringFound.Address <> firstLocation

     End If

End With
  '-----------------------------------------------------------------------------------
  'BELOW CONTAINS THE CODE THAT WORKS:
   Sub merge_All_Section_Headers()
   ' Description:
   ' The next portion macro will find and format the Tranaction Source rows in the file
   ' by checking each row in column A for the following text: TRANSA.  If a cell
   ' has this text in it, it is selected and a function called merge_text_cells
   ' is run, which performs concatenation of each Transaction Source header row and deletes
   ' the text from the rest of the cells with broken up text.
   '
   lastRow = ActiveSheet.UsedRange.Rows.Count + 1
   Range(lastRow & ":" & lastRow).Delete

   ActiveSheet.PageSetup.Orientation = xlLandscape

   With ActiveSheet.Range("A:A")
       Dim searchString As String
       Dim arrRangesToDelete(0 To 9) As Range

       searchString = "TRANSA"

       'The following sets stringFound to either true or false based on whether or not
       'the searchString (TRANSA) is found or not):
        Set stringFound = .Find(searchString, LookIn:=xlValues, lookat:=xlPart)

        If Not stringFound Is Nothing Then

           firstLocation = stringFound.Address

           counter = 0

           Do

              stringFound.Select

              lastFound = stringFound.Address

              merge_Text_Cells

              If ((InStr(ActiveCell.Text, "CHARGE FILER") = 0) And _
                  (InStr(ActiveCell.Text, "CREDIT FILER") = 0) And _
                  (InStr(ActiveCell.Text, "PA MIDNIGHT FINAL") = 0) And _
                  (InStr(ActiveCell.Text, "BAD DEBT TURNOVER") = 0)) Then

                  firstRowOfSection = ActiveCell.Row

                  lastRowOfSection = (ActiveSheet.Range(ActiveCell.Offset(2, 1).Address).End(xlDown).Row + 2)

                  Set arrRangesToDelete(counter) = Range(firstRowOfSection & ":" & lastRowOfSection)

                  counter = counter + 1

              End If

              Range(lastFound).Select

              Set stringFound = .FindNext(stringFound)

           Loop While Not stringFound Is Nothing And stringFound.Address <> firstLocation

        End If

   End With

   For i = 0 To counter - 1

       arrRangesToDelete(i).Delete

   Next i

   Range(firstLocation).Select

End Sub

So, das array funktioniert und macht den job, ohne Sie zu zerstören Objekte. Ich will noch versuchen, die Union-Methode und sehen, ob ich bekommen kann es arbeiten, das wäre cool, auch!

Schreibe einen Kommentar