使用循环的用户表单锁定不起作用

Question

我正在尝试设置我的用户表单以执行循环或查找以引用我的表，该表位于工作表上并且是一个大型数据库。

我希望我的用户表单查找我键入的内容，然后自动填充其他文本框，以便我可以限制重复项的数量并使其更加流畅。

我的代码如下所示，嵌入到Textbox1中，并设置为在更改后运行代码。它仍然不起作用，我已经工作了很多天和几周，试图解决这个问题。

Option Explicit
Dim id As String, i As String, j As Integer, flag As Boolean
Sub GetDataA()
If Not IsNumeric(UserForm1.TextBox1.Value) Then
    flag = False
    i = 0
    id = UserForm1.TextBox1.Value
    Do While Cells(i + 1, 1).Value <> ""
        If Cells(i + 1, 1).Value = id Then
            flag = True
            For j = 2 To 7
                UserForm1.Controls("TextBox" & j).Value = Cells(i + 1, j).Value
            Next j
        End If
        i = i + 1
    Loop
    If flag = False Then
        For j = 5 To 10
            UserForm1.Controls("TextBox" & j).Value = ""
        Next j
    End If
Else
End If
End Sub

Answer 1

你可能希望采用这种代码重构

Option Explicit
Sub GetDataA()
    Dim j As Integer
    Dim f As Range
    With UserForm1 '<--| reference your userform
        If Not IsNumeric(.TextBox1.Value) Then Exit Sub '<--| exit sub if its TextBox1 value is not a "numeric" one
        Set f = Range("A1", Cells(Rows.Count, 1).End(xlUp)).Find(what:=.TextBox1.Value, LookIn:=xlValues, lookat:=xlWhole) '<--| try and find its TextBox1 value along column "A" cells from row 1 down to last not empty one
        If f Is Nothing Then '<--| if not found
            For j = 5 To 10
                .Controls("TextBox" & j).Value = ""
            Next j
        Else '<--| if found
            For j = 2 To 7
                .Controls("TextBox" & j).Value = f.Offset(, j - 1).Value
            Next j
        End If
    End With
End Sub

注意：如果此子实际上位于UserForm1代码窗格中，则可以将With UserForm1更改为With Me