时间戳

首页 > 解决方案 > 仅当在用户窗体中单击按钮时才运行“FindNext”

excel - 仅当在用户窗体中单击按钮时才运行“FindNext”

问题描述

我正在设置一个用户表单来查找公司名称。每个公司有 10 行数据。我需要按钮来选择第一行，然后停止并仅在再次单击按钮时才运行 findnext。

目前我的代码正确地找到了公司的名称，但是由于循环，它直接选择了公司名称的最后一行。我需要它在第一次出现名称时选择它，然后只有当我再次单击该按钮时才找到下一个

Private Sub CommandButton1_Click()
Dim lastrow As Long, i As Long, c As String
Dim mycell As Range

lastrow = Sheets("Rent Roll").Range("A" & Rows.Count).End(xlUp).Row
c = TextBox1.Text

With Sheets("Rent Roll").Range("C5:C" & lastrow)
Set mycell = .Find(what:=c, LookIn:=xlValues)

If Not mycell Is Nothing Then
firstAddress = mycell.Address

   Do
   mycell.Select

   Set mycell = .FindNext(mycell)

   Loop While Not mycell Is Nothing And mycell.Address <> firstAddress

Else
MsgBox ("Not Found")

End If

End With

Exit Sub


End Sub

如果我输入“Google”，它应该选择第一行然后停止。然后，如果我再次单击该按钮，请使用 findnext 并使用“Google”选择下一个单元格

标签： excelvba

解决方案

你应该能够做到这样的事情：

Private Sub CommandButton1_Click()
    Static lastCell As Range  '<< static variables preserve values between calls
    Static lastTerm As String

    Dim ws As Worksheet, rngSrch As Range
    Dim lastrow As Long, i As Long, c As String
    Dim f As Range, afterCell As Range

    Set ws = ThisWorkbook.Worksheets("Rent Roll")
    Set rngSrch = ws.Range(ws.Range("C5"), ws.Cells(Rows.Count, "C").End(xlUp))

    c = TextBox1.Text

    'new search term?
    If c <> lastTerm Then
        Set lastCell = Nothing
        lastTerm = c
    End If

    If Len(c) = 0 Then Exit Sub '<< nothing to search for

    If lastCell Is Nothing Then
        Set afterCell = Rng.Cells(Rng.Cells.Count)
    Else
        Set afterCell = lastCell
    End If

    Set f = rngSrch.Find(what:=c, after:=afterCell, LookIn:=xlValues)
    If f Is Nothing Then
        MsgBox ("'" & c & "' not found")
    Else
        If f.Row < lastCell.Row Then
            MsgBox "Already at the last row" '<< do this, or just keep wrapping round?
        Else
            f.Select
            Set lastCell = f
        End If
    End If

End Sub

推荐阅读