excel - 如何在超过 60000 行的文本框中快速显示结果

Question

我有 Excel 数据库，我只想输入标签号，我会自动在文本框中显示结果...但是需要 30 秒才能显示结果

Private Sub cmdSearch_Click()
Dim x As Long
Dim y As Long
x = Sheets("Clients").Range("A" & Rows.Count).End(xlUp).Row
For y = 1 To x
If Sheets("Clients").Cells(y, 1).Text = TextBox1.Value Then

TextBox1.Text = Sheets("Clients").Cells(y, 1)
TextBox4.Text = Sheets("Clients").Cells(y, 3)
TextBox5.Text = Sheets("Clients").Cells(y, 4)
TextBox10.Text = Sheets("Clients").Cells(y, 5)
TextBox11.Text = Sheets("Clients").Cells(y, 6)
TextBox12.Text = Sheets("Clients").Cells(y, 7)
TextBox13.Text = Sheets("Clients").Cells(y, 8)
End If
Next y
End Sub

Answer 1

Match相当快

Private Sub cmdSearch_Click()
    Dim m As VARIANT


    With Sheets("Clients")
        m = Application.Match(TextBox1.Value, .Columns(1), 0)
        If not iserror(m) then
            TextBox4.Text = .Cells(m, 3)
            TextBox4.Text = .Cells(m, 4)
            'etc
        end if
    end with

End Sub