excel - 如何根据单元格的值搜索列并将相邻列的值添加到列表中？

Question

我想根据单元格搜索列并将相邻列值添加到列表中。

示例：工作表上的数据布局如下：

当我转到另一张表并从下拉列表中选择Henry或Ben时，相邻的单元格会下拉与 A 列中的名称相关的所有 IDS。

示例 2：

标签： excelvbaexcel-formula

Answer 1

让我们假设对于这个答案，我们使用工作表 1。工作表 1 结构：

Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)

        Dim Lastrow As Long
        Dim cell As Range, rng As Range, rngResults As Range
        Dim strSearch As String, strResults As String

        With ThisWorkbook.Worksheets("Sheet1")

            If Not Intersect(Target, .Range("D2")) Is Nothing Then

                strSearch = Target.Value

                Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

                Set rng = .Range(.Cells(2, 1), .Cells(Lastrow, 1))
                Set rngResults = .Range("E2")

                For Each cell In rng

                    If strSearch = cell.Value Then

                        If IsEmpty(strResults) Then
                            strResults = cell.Offset(0, 1).Value
                            Debug.Print strResults
                        Else
                            strResults = strResults & "," & cell.Offset(0, 1).Value
                            Debug.Print strResults

                        End If

                    End If

                Next

                    With rngResults.Validation
                        .Delete
                        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                            Formula1:=strResults
                    End With

            End If

        End With

    End Sub