最快/最优雅的方法来遍历范围以在excel VBA中查找值 [英] Fastest / most elegant method for looping through a range to find values in excel VBA

问题描述

美好的一天，

在我的项目中，遇到很多情况，我需要我的代码在给定范围内多次查找某个字符串，并在找到该字符串时执行许多操作.

In my project, I encounter a number of situations in which I need my code to find a certain string multiple times in a given range, and do a number of actions when this string is found.

我写了适合自己需要的Do..Loop，但是由于我不是最优雅的程序员(我是自学成才的)，所以我想知道其他更快或更优雅的方法.

I have written a Do..Loop which suits my needs, but since I am not the most elegant programmer (I am self-taught) I am wondering about other methods that may be faster or perhaps more elegant.

示例代码:

Set FindStr = .Sheets(c).Range("C2:C" & LRow).Find("MANUALLY", lookat:=xlWhole)
If Not FindStr is Nothing Then
    FRow = FindStr.Row
End If

Do While Not FindStr Is Nothing
    If FindStr.Offset(0,2) = "SA" Then
        FindStr.Offset(0,5) = "Confirmed"
    End If
    Set FindStr = .Sheets(c).Range("C2:C" & LRow).Find("MANUALLY", after:=FindStr, lookat:=xlWhole)

    If Not FindStr Is Nothing Then
        If FindStr.Row = FRow then Exit Do
    End If
Loop

推荐答案

如果要继续搜索，可以使用FindNext而不是重新开始新搜索.

If you want to continue an search, you can use FindNext rather than restart a new search.

您唯一需要了解的是FindNext完成后将在顶部继续搜索.为了告诉您搜索再次开始，请保存第一个匹配项的地址，如果FindNext返回您的第一个匹配项的地址，您就知道您已经完成了.

The only thing you need to be aware of is that FindNext will continue a search at the top once it is done. To be able to tell that the search starts again, you save the address of the first match and if FindNext returns you the address of your first match you know that you are done.

对变量命名的一句话:Find/FindNext返回Ranges，您应该给变量起一个反映该名称的名称. FindStr表示它包含一个字符串，这是不正确的. Range的 default属性是它的值，因此它看起来好像是一个字符串-但不是.

One remark to your variable naming: Find/FindNext return Ranges, and you should gibe your variable a name that reflects that. FindStr impplies that it holds a string, which is not true. The default property of a Range is the value of it, so it may look as if it is a string - but it isn't.

看看这段代码:

Const searchStr = "MANUALLY"
With .Sheets(c).Range("C2:C" & LRow)
    Dim findRange As Range, firstHit As Range
    Set findRange = .Find(searchStr, lookat:=xlWhole)
    If Not findRange Is Nothing Then
        Set firstHit = findRange     ' Remember first hit.
        Do
            ' ... (Do your stuff here)
            Set findRange = .FindNext(findRange)
        Loop While Not findRange Is Nothing And findRange.Address <> firstHit.Address
    Else
        ' in case you didn't find anything
    End If
End With

这篇关于最快/最优雅的方法来遍历范围以在excel VBA中查找值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！