excel - 根据vba中单元格(查找)中可用的值执行while循环

Question

嗨，我正在用 vb 编写代码来检查工作表中的特定值，如果该值不可用，那么它应该返回到另一张工作表以查找新值，如果找到该值我必须这样做该表上的一些操作我有以下代码来查找表中的值，但如果我在 DO WHILE 循环中传递相同的值作为条件，则会出现编译错误

查找数值代码

Selection.Find(What:=last_received, After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Activate

有人可以帮我写一段 DO WHILE 代码，并在循环条件中查找上述内容，这样如果条件给出 false(即，如果在工作表中找不到该值)，那么我应该使用一些寻找其他值(value)

这是我要放入循环的代码

Sheets("Extract -prev").Select
    Application.CutCopyMode = False

    ActiveWorkbook.Worksheets("Extract -prev").Sort.SortFields.Clear   'sorting with tickets
    ActiveWorkbook.Worksheets("Extract -prev").Sort.SortFields.Add Key:=Range( _
        "C2:C2147"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Extract -prev").Sort
        .SetRange Range("A1:AB2147")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Application.Goto Reference:="R1C3"        'taking last received ticket
    Selection.End(xlDown).Select
    Selection.Copy
    Sheets("Calc").Select
    Application.Goto Reference:="Yesterday_last_received"
    ActiveSheet.Paste

此代码获取最后一张票，但根据它在下一张“摘录”中的可用性，我必须在最后一张票之前(以及之后)获取一张票。

Answer 1

尝试下面的代码:

Sub test()

    Dim lastRow As Long
    Dim rng As Range
    Dim firstCell As String

    lastRow = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row

    For i = 1 To lastRow
        Set rng = Sheets("sheet2").Range("A:A").Find(What:=Cells(i, 1), LookIn:=xlValues, lookAt:=xlPart, SearchOrder:=xlByRows)

        If Not rng Is Nothing Then firstCell = rng.Address

        Do While Not rng Is Nothing

            rng.Offset(0, 1) = "found"

            Set rng = Sheets("sheet2").Range("A:A").FindNext(rng)


            If Not rng Is Nothing Then
                If rng.Address = firstCell Then Exit Do
            End If

        Loop
    Next

End Sub