vba 无法获取范围类错误的 findnext 属性

Can't get findnext property of range class error

提问by Lawrence Knowlton

I am trying to parse a report in Excel 2007. It is basically a report of accounting charge exceptions. The report has sections with a header for each type of exception. There are types of exceptions that are deleted from the report. I'm using a Do While loop to find each header and if the section needs to be deleted I have it do so. If nothing needs to be deleted the code works fine, but right after a section is deleted I get an "Unable to get the FindNext property of the Range Class" error. Here is my code:

我正在尝试在 Excel 2007 中解析报告。它基本上是会计费用异常的报告。该报告包含带有每种异常类型标题的部分。有从报告中删除的例外类型。我正在使用 Do While 循环来查找每个标题，如果需要删除该部分，我会这样做。如果不需要删除任何内容，代码工作正常，但在删除部分后，我收到“无法获取范围类的 FindNext 属性”错误。这是我的代码：

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, the array works and does the job, without destroying any objects. I still want to try the Union method and see if I can get it to work, which would be cool as well!

因此，数组可以工作并完成工作，而不会破坏任何对象。我还是想试试Union方法，看看能不能用，也很酷！

回答by brettdj

You code falls over as the range object in StrFoundhas been destroyed - so it Is Nothingwhen you go to apply

由于范围对象StrFound已被破坏，您的代码会失败 - 所以Is Nothing当您申请时

There are a couple of alternatives to the error handling proposed bu Juri (which if you do use you should reset immediately)

对错误处理建议的 bu Juri 有几种替代方案（如果你确实使用了它，你应该立即重置）

• Add all the sections to be deleted to a new range with Unionthen delete this range in a single shot outside the loop. I have an example in my article here in my article Using Find and FindNext to efficiently delete any rows that contain specific text
• Move your delete code after the .FindNextrather than before it, and add a simple test to see if stringfoundexists before running your Section_Delcode

• 将所有要删除的部分添加到新范围，Union然后在循环外的单个镜头中删除该范围。我在我的文章中的这篇文章中有一个示例使用 Find 和 FindNext 有效删除包含特定文本的任何行
• 将删除代码移动到之后.FindNext而不是之前，并stringfound在运行Section_Del代码之前添加一个简单的测试以查看是否存在

Union approach

联合方式

Sub UnionAPp()
Dim c As Range
Dim rng1 As Range
With Worksheets(1).Range("a1:a500")
    Set c = .Find(2, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstaddress = c.Address
        Set rng1 = c
        Do
            Set c = .FindNext(c)
            Set rng1 = Union(rng1, c)
        Loop While c.Address <> firstaddress
    End If
    MsgBox "Your working range is " & rng1.Address
End With
End Sub

So you could modify the standard Excel help for FindNextfrom

所以你可以修改标准的 Excel 帮助FindNext从

standard

标准

Sub TestInit()
With Worksheets(1).Range("a1:a500")
    Set c = .Find(2, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Value = 5
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With
End Sub

new

新的

Sub TestA()
With Worksheets(1).Range("a1:a500")
    Set c = .Find(2, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Value = 5
            Set c = .FindNext(c)
            If Not c Is Nothing Then c.Clear
            'your code: If Not StrFound Is Nothing Then Call Section_Del
        Loop While Not c Is Nothing
    End If
End With
End Sub

回答by Jüri Ruut

If all occurencies are deleted, Findnextshould give an error.

如果所有事件都被删除，Findnext应该给出一个错误。

A line

一条线

On Error Goto ExitLoop

should be added before Set stringFound = .FindNext(stringFound).

之前应该加Set stringFound = .FindNext(stringFound)。

A line

一条线

ExitLoop:

should be added after Loop While...

应该在后面加上 Loop While...