excel - .FindNext() 与 xlPrevious

Question

我试图从下到上查找所有值“O”。我不能使用“正常”查找，因为我要去 offset一些值下降和.Find()会再次找到他们。

问题是当我使用 .FindNext在 .Find() 之后与 SearchDirection:=xlPrevious它回到顶部而不是跟随方向。

出于测试目的，我正在使用此代码:

Dim f As Range

Set ws = ThisWorkbook.ActiveSheet

ws.Cells(7, 5).Value = "O"
ws.Cells(6, 5).Value = "O"
ws.Cells(5, 5).Value = "O"
ws.Cells(5, 6).Value = "O"
ws.Cells(5, 7).Value = "O"

Set f = ws.Range("A1:AX50").Find(what:="O", SearchDirection:=xlPrevious)
Debug.Print f.Address
Set f = ws.Range("A1:AX50").FindNext(f)
Debug.Print f.Address

它打印 $E$7和 $E$5 .我期待 $E$7和 $E$6 .

我不确定这是从下到上查找所有值的最佳方法。任何帮助将不胜感激。

Answer 1

Range.FindNext method显然没有继承 SearchDirection:=xlPrevious 参数。您需要底座 Range.Find method用 After:=f 参数代替。

Dim f As Range, addr As String, ws As Worksheet

Set ws = ThisWorkbook.ActiveSheet

ws.Cells(7, 5).Value = "O"
ws.Cells(6, 5).Value = "O"
ws.Cells(5, 5).Value = "O"
ws.Cells(5, 5).Value = "O"
ws.Cells(5, 6).Value = "O"
ws.Cells(5, 7).Value = "O"

With ws.UsedRange.Cells  'Range("A1:AX50")
    Set f = .Find(What:="O", after:=.Cells(1), SearchDirection:=xlPrevious, SearchOrder:=xlByRows)
    If Not f Is Nothing Then
        addr = f.Address(0, 0)
        Do
            Debug.Print f.Address
            Set f = .Find(What:="O", after:=f, SearchDirection:=xlPrevious, SearchOrder:=xlByRows)
        Loop Until addr = f.Address(0, 0)
    End If
End With