用AutoFilter缩短代码

Question

我的代码可以很好地将过滤后的数据返回到表单上的TextBoxes的三列。当我在调试中执行它时，我可以看到，执行我的新手(但正在工作)代码需要很长的时间。我记录了宏，试图了解如何使用AutoFilter脚本做同样的事情&因此，有一个更快的脚本，但恐怕我失败了。有人能告诉我怎么做吗，或者我是否该费心去做？

Private Sub CommandButton1_Click()
Dim i As Integer, c As Integer, d As Integer
Dim e As Integer, f As Integer, g As Integer
Dim lstrw As Integer, ws As Worksheet
Dim MySerNum As String, MyLocation As String
Dim MySearchValue As String, MyStatus As String

    MySearchValue = "x" 'search column for cells that contain only the letter "x" 
    Set ws = Sheets(2)
                       'The user will type an "x" in column N at the rows...
                       'he wishes to return data from to the form TextBoxes
    lstrw = ws.Range("N" & Rows.Count).End(xlUp).Row
                       'find how many cells contain string "x"
    For f = 1 To lstrw
        If ws.Cells(f, 14).Value = MySearchValue Then
            e = e + 1
        End If
    Next f
                       ' loop to find occurrence of "x" and load variables
    For i = 1 To lstrw
                      'the adjacent textbox on the form to TextBox1...
                      'is TextBox16 hence the +15 below   
        If ws.Cells(i, 14).Value = MySearchValue Then
            MySerNum = ws.Cells(i, 2).Value
        End If
        If ws.Cells(i, 14).Value = MySearchValue Then
            MyLocation = ws.Cells(i, 4).Value
        End If
        If ws.Cells(i, 14).Value = MySearchValue Then
            MyStatus = ws.Cells(i, 5).Value
        End If   
        d = c + 15
        g = d + 15
        Me.Controls("TextBox" & c).Value = MySerNum
        Me.Controls("TextBox" & d).Value = MyLocation
        Me.Controls("TextBox" & g).Value = MyStatus
        If ws.Cells(i, 14).Value = MySearchValue Then
            c = c + 1
        End If
    Next i
End Sub

Answer 1

话虽如此，以下是几条建议：

更改：

For i = 1 To lstrw
                  'the adjacent textbox on the form to TextBox1...
                  'is TextBox16 hence the +15 below   
    If ws.Cells(i, 14).Value = MySearchValue Then
        MySerNum = ws.Cells(i, 2).Value
    End If
    If ws.Cells(i, 14).Value = MySearchValue Then
        MyLocation = ws.Cells(i, 4).Value
    End If
    If ws.Cells(i, 14).Value = MySearchValue Then
        MyStatus = ws.Cells(i, 5).Value
    End If   
    d = c + 15
    g = d + 15
    Me.Controls("TextBox" & c).Value = MySerNum
    Me.Controls("TextBox" & d).Value = MyLocation
    Me.Controls("TextBox" & g).Value = MyStatus
    If ws.Cells(i, 14).Value = MySearchValue Then
        c = c + 1
    End If
Next i

至：

For i = 1 To lstrw
                  'the adjacent textbox on the form to TextBox1...
                  'is TextBox16 hence the +15 below   
    If ws.Cells(i, 14).Value = MySearchValue Then
        Me.Controls("TextBox" & c).Value = ws.Cells(i, 2).Value
        d = c + 15
        Me.Controls("TextBox" & d).Value = ws.Cells(i, 4).Value
        g = d + 15
        Me.Controls("TextBox" & g).Value = ws.Cells(i, 5).Value
        c = c + 1
    End If   
Next i

这样做可以删除所有中间My...变量，从而可以删除它们的Dim语句。如果稍后需要它们，可以在循环结束时设置它们一次。这会让事情加速一些。