Range.Find和With冻结Excel [英] Range.Find and With freezes Excel

问题描述

Microsoft在VBA的Range.Find函数中的条目以以下代码为例( https://docs.microsoft.com/zh-cn/office/vba/api/excel.range.find ):

Microsoft's entry in Range.Find function of VBA has the following code as an example (https://docs.microsoft.com/en-us/office/vba/api/excel.range.find):

   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
    End If 
End With

我试图使用它，包括以下With语句，但是Excel冻结了.

I tried to use it including the following With statement, but Excel froze.

   With Range("a1:f50")
     Set c = .Find("F-Value", LookIn:=xlValues)
     If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            With c.Borders(xlEdgeLeft)
                     .LineStyle = xlContinuous
                     .ColorIndex = 0
                     .TintAndShade = 0
                     .Weight = xlThin
            End With
            Set c = .FindNext(c)
         Loop While Not c Is Nothing
     End If
 End With

我想知道是否同时使用Range.Find和With会使代码过于繁琐，因为我今年购买了这台计算机，并且总体运行良好.

I wonder if using both Range.Find and With make the code too heavy, because I bought the computer this year, and it runs fine overall.

推荐答案

您删除了c.Address <> firstAddress检查，这是退出用例的唯一方法.

You removed the c.Address <> firstAddress check, which is the only way to exit the loop in your use case.

Find()/FindNext() 总是在您的范围内循环，直到没有匹配的值为止-当到达末尾时，它只是从头开始重新开始.如何计算该值取决于在找到单元格之后对单元格值进行的操作

Find()/FindNext() always loops around within your range until there's no matching value - when it gets to the end it just starts back at the beginning again. How you account for that depends on what you do with the cell value after the cell has been located

• 如果要替换的值不再与Find匹配，则可以在Find()匹配失败并退出并返回Nothing后立即退出循环.下面的第二个代码示例)

• If you're replacing the value such that it will no longer be a match for the Find, you can exit the loop as soon as Find() fails to make a match and so returns Nothing (see second code example below)

如果您要不在循环中更改单元格值，则查找"将一直循环执行，一遍又一遍地返回相同的单元格，除非您有某种方法可以退出找到所有单个单元格后循环.通常，您可以通过保存第一个单元格的地址来执行此操作，然后在第二次返回该单元格后立即退出循环.

If you're not changing the cell value in the loop, then Find will just keep looping around, returning the same cells over and over, unless you have some way to exit the loop once all individual cells have been found. Typically you do this by saving the address of the first cell, and then exiting the loop as soon as that cell is returned a second time.

因此，对于您的不可替代"产品，用例可以正常工作:

So for your "no-replace" use case this works fine:

    Dim firstAddress As String, c As Range

    With Range("a1:f50")
         Set c = .Find("F-Value", LookIn:=xlValues, LookAt:=xlWhole) '<<be specific
         If Not c Is Nothing Then
            firstAddress = c.Address '<< remember the first cell found
            Do
                With c.Borders(xlEdgeLeft)
                         .LineStyle = xlContinuous
                         .ColorIndex = 0
                         .TintAndShade = 0
                         .Weight = xlThin
                End With
                Set c = .FindNext(c)
             Loop While c.Address <> firstAddress '<< exit once we've hit the first cell again
         End If
     End With

为了比较，这里是查找替换".版本:

For comparison here's the "find with replace" version:

    Dim  c As Range

    With Range("a1:f50")
         Set c = .Find("F-Value", LookIn:=xlValues, LookAt:=xlWhole)
         Do While Not c Is Nothing
             c.Value = "SomeOtherValue"
             Set c = .FindNext(c)
         Loop 
    End With

这篇关于Range.Find和With冻结Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！