Excel VBA如何在第一个匹配后找到下一个值

Question

我下面的代码将显示从电话号码中找到的所有记录，但因为我有重复的电话号码。例如:389905在excel中有两条记录。我想单击搜索按钮以显示下一个匹配的索书号及其记录。

这可能吗 ？

Private Sub CommandButton1_Click()

row_number = 1
Do
    DoEvents
    row_number = row_number + 1
    item_in_review = Sheets("Database").Range("A" & row_number)
    If item_in_review = txtCall.Text Then
        txtLogged.Text = Sheets("Database").Range("B" & row_number)
        txtName.Text = Sheets("Database").Range("C" & row_number)
        txtSite.Text = Sheets("Database").Range("D" & row_number)
        txtType.Text = Sheets("Database").Range("E" & row_number)
        txtTitle.Text = Sheets("Database").Range("F" & row_number)
        cmbOwner.Text = Sheets("Database").Range("G" & row_number)
        cmbResponder.Text = Sheets("Database").Range("H" & row_number)
        txtReference.Text = Sheets("Database").Range("I" & row_number)
    End If
Loop Until item_in_review = ""

End Sub

Answer 1

一种方法是使用一个类来保存每组值:

创建一个类模块并将其命名为clsCallDetails .
将此代码添加到模块中:

Option Explicit

'Class module named 'ClsCallDetails'

Private pLogged As String
Private pName As String
Private pSite As String
Private pCallType As String
Private pOwner As String
Private pResponder As String
Private pReference As String

Public Property Get Logged() As String
    Logged = pLogged
End Property
Public Property Let Logged(Value As String)
    pLogged = Value
End Property

Public Property Get Name() As String
    Name = pName
End Property
Public Property Let Name(Value As String)
    pName = Value
End Property

Public Property Get Site() As String
    Site = pSite
End Property
Public Property Let Site(Value As String)
    pSite = Value
End Property

Public Property Get CallType() As String
    CallType = pCallType
End Property
Public Property Let CallType(Value As String)
    pCallType = Value
End Property

Public Property Get Owner() As String
    Owner = pOwner
End Property
Public Property Let Owner(Value As String)
    pOwner = Value
End Property

Public Property Get Responder() As String
    Responder = pResponder
End Property
Public Property Let Responder(Value As String)
    pResponder = Value
End Property

Public Property Get Reference() As String
    Reference = pReference
End Property
Public Property Let Reference(Value As String)
    pReference = Value
End Property

创建一个普通模块并将此代码添加到其中:
编辑:原来有 Find_CallNumbers作为一个函数；已将其更改为子。

Option Explicit

'Normal Module

Public CallDetails As Collection

Public Sub Find_CallNumbers(NumberToFind As String)

    Dim rng_to_search As Range
    Dim rFound As Range
    Dim FirstAddress As String
    Dim FoundItem As clsCallDetails

    Set CallDetails = New Collection

    With ThisWorkbook.Worksheets("Database")
        Set rng_to_search = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
    End With

    With rng_to_search
        'Look for the first instance.
        Set rFound = .Find(What:=NumberToFind, _
                           After:=.Cells(1, 1), _
                           LookIn:=xlValues, _
                           LookAt:=xlWhole, _
                           SearchDirection:=xlNext)
        If Not rFound Is Nothing Then
            FirstAddress = rFound.Address
            Do
                Set FoundItem = New clsCallDetails 'Create a new instance of the class to hold the details.
                With FoundItem
                    .Logged = rFound.Offset(, 1) 'Offset from column A by 1 column, so column B.
                    .Name = rFound.Offset(, 2)
                    .Site = rFound.Offset(, 3)
                    .CallType = rFound.Offset(, 4)
                    .Owner = rFound.Offset(, 5)
                    .Responder = rFound.Offset(, 6)
                    .Reference = rFound.Offset(, 7)
                End With
                CallDetails.Add FoundItem 'Add the class instance to our collection.
                Set rFound = .FindNext(rFound) 'Look for the next value.

            'Continue searching until we reach the top again.
            Loop While Not rFound Is Nothing And rFound.Address <> FirstAddress
        End If
    End With

End Sub

创建一个包含这些控件的表单(我已将控件名称放在控件上方):


将此代码添加到表单中:

Option Explicit

Private Sub cmdFind_Click()
    Find_CallNumbers Me.txtSearchNumber
    Me.txtCurrentIndex = 1
    PlaceValues (Me.txtCurrentIndex)
End Sub

Private Sub PlaceValues(Index As Long)
    With Me
        .txtLogged.Value = Format(CallDetails(Index).Logged, "HH:MM:SS")
        .txtName.Value = CallDetails(Index).Name
        '... 
        '...
    End With
End Sub

Private Sub cmdNext_Click()
    Me.txtCurrentIndex = Me.txtCurrentIndex + 1
    PlaceValues (Me.txtCurrentIndex)
End Sub

Private Sub cmdPrevious_Click()
    Me.txtCurrentIndex = Me.txtCurrentIndex - 1
    PlaceValues (Me.txtCurrentIndex)
End Sub

当您搜索一个数字时，它会找到并显示第一个找到的项目的详细信息。单击 Next/Previous 将带您进入下一个实例。
您需要添加错误检查 - 例如当它到达集合的末尾或开头时，或者如果未找到搜索的号码。