excel - Fastest / most elegant method for looping through a range to find values in excel VBA

Question

Good day,

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.

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.

Example code:

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

标签： excelvbaloopsfind

Answer 1

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

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.

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.

Have a look at this piece of code:

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