excel - 如何在同一列中搜索多个值？

Question

我有一个有八个工作表的工作簿。第一个工作表是一个首页，其中包含工作簿中的所有数据，如果您愿意，可以作为主工作表。其余七个选项卡是团队的员工姓名。
我的代码将在 C 列中搜索名称并将包含该名称的整行复制到相应工作人员的个人工作表中。
我现在需要在同一列 (C) 中搜索其余工作人员的姓名，并将相应的行复制到相应的工作表中。
我当前的代码:

Private Sub CommandButton1_Click()
    Dim c As Range
    Dim j As Integer
    Dim Source As Worksheet
    Dim Target As Worksheet
    ' Change worksheet designations as needed
    Set Source = ActiveWorkbook.Worksheets("Front Page")
    Set Target = ActiveWorkbook.Worksheets("Charlotte")
    j = 2
    ' Start copying to row 2 in target sheet
    For Each c In Source.Range("C1:C1000") ' Do 1000 rows
        If c = "Charlotte Richardson" Then
            Source.Rows(c.Row).Copy Target.Rows(j)
            j = j + 1
        End If
    Next c
End Sub

Answer 1

试试这个 - 虽然您必须将工作表名称添加到数组 arr1以及您要查找的数组的全名 arr2 :

Private Sub CommandButton1_Click()

    Dim c As Range
    Dim j As Long, i as Long
    Dim Source As Worksheet
    Dim Target As Worksheet
    Dim arr1 As Variant, arr2 As Variant

    arr1 = Array("Charlotte", "Mikey", "Bob")
    arr2 = Array("Charlotte Richardson", "Mikey Joe", "Bob Vann")

    'Change worksheet designations as needed
    Set Source = ActiveWorkbook.Worksheets("Front Page")

    'Start copying to row 2 in target sheet
    For i = 0 To UBound(arr1)

        j = 2
        Set Target = ActiveWorkbook.Worksheets(arr1(i))

        For Each c In Source.Range("C1:C1000") ' Do 1000 rows
            If c = arr2(i) Then
                Source.Rows(c.Row).Copy Target.Rows(j)
                j = j + 1
            End If
        Next c

    Next i

End Sub