excel - FindNext 返回不正确的值

Question

我目前对以下代码有疑问。我已经设置了Find值，但 FindNext不断返回错误的行:

Set SearchRange = Range("B:B")
Set OpenBal = SearchRange.Find("Opening Balance")
Set EndBal = SearchRange.Find("Ending Balance")

Set CritR = Range("E" & OpenBal.Row, "E" & EndBal.Row)
Set SumR = Range("C" & OpenBal.Row, "C" & EndBal.Row)
Obrow = OpenBal.Row - 1

导致问题的代码:

For i = 4 To lr
If Cells(i, 2).Value = "Ending Balance" Then

    If Cells(Obrow, 1).Value = "USD" Then

        abc = Application.WorksheetFunction.SumIf(CritR, "OPEN", SumR)
        def = Application.WorksheetFunction.SumIf(CritR, "CXCL", SumR)
        Cells(i + 1, 5).Value = abc + def
        Cells(i + 1, 5).NumberFormat = "#,##0.00;(#,##0.00)"
        Cells(i + 1, 4).Value = "OPEN/CXCL"
        Cells(i + 1, 4).HorizontalAlignment = xlRight
        Cells(i + 1, 4).Font.Bold = True
        Cells(i + 1, 4).Interior.Color = 65535
        Cells(i + 1, 5).Interior.Color = 65535
        Cells(i + 1, 2).Value = "Final Balance"
        Cells(i + 1, 2).Interior.Color = 65535
        Cells(i + 1, 2).Font.Bold = True
        Cells(i + 1, 3).Value = Cells(i, 3).Value - Cells(i + 1, 5).Value
        Cells(i + 1, 3).NumberFormat = "#,##0.00;(#,##0.00)"
        Cells(i + 1, 3).Font.Bold = True
        Cells(i + 1, 3).Interior.Color = 65535

    End If

        Set OpenBal = SearchRange.FindNext(after:=OpenBal)
        Set EndBal = SearchRange.FindNext(after:=EndBal)
        Set CritR = Range("E" & OpenBal.Row, "E" & EndBal.Row)
        Set SumR = Range("C" & OpenBal.Row, "C" & EndBal.Row)
        Obrow = OpenBal.Row - 1
        Debug.Print "Open Balance: "; OpenBal.Row; " Ending Balance: " & EndBal.Row; " OB ROW: "; Obrow
End If

Next

在调试屏幕中，在第一个值之后为期初余额行设置的范围是错误的，它只是按如下方式打印期末余额(第 9 行之后所有相应的行实际上都是期末余额行):

Open Balance:  9  Ending Balance: 17 OB ROW:  8 
Open Balance:  17  Ending Balance: 26 OB ROW:  16 
Open Balance:  26  Ending Balance: 33 OB ROW:  25 
Open Balance:  33  Ending Balance: 9 OB ROW:  32 

任何帮助，将不胜感激。

Answer 1

FindNext()使用上一个 Find() 中的标准，无论它在什么范围内运行。但是您可以调用Find()再次指定您的After:=范围。

改变:

Set OpenBal = SearchRange.FindNext(after:=OpenBal)
Set EndBal  = SearchRange.FindNext(after:=EndBal)

至:

Set OpenBal = SearchRange.Find(what:="Opening Balance", after:=OpenBal)
Set EndBal  = SearchRange.Find(what:="Ending Balance",  after:=EndBal)