excel - VBA:如何在单元格范围内搜索值，并返回该位置旁边的单元格？

Question

大家好，这是我的第一个问题，所以我会尽我所能来格式化这个。

在 下方没有特定单元格名称的快速说明

我正在尝试编写一个宏，其中用户输入一个值(X)，一个宏在一系列单元格中搜索一个值(X)，然后宏返回值位置旁边的 3 个空格中的单元格值(X) 是。

使这无法解决的几件事是用户在 Sheet1 上输入值并且该值通过公式移动到 Sheet2，我似乎无法弄清楚如何使用查找我正在搜索的值for 尚未在宏中定义。

使这变得困难的另一件事是范围也不是严格定义的，因为列表可能比现在更长或更短，而且我不知道它什么时候会改变。因此，搜索范围必须根据用户输入的列表开始，并且需要一直持续到找到空白点。

例如: Range.("C7:D10") 将不起作用，因为用户可以输入新信息来更改工作范围，如下所述。

以下是进一步解释的截图

/image/wlnhg.jpg

因此，在此屏幕截图中，单元格 C3 和 D3 是从 Sheet1 导入的值。

C3 是 (=Sheet1!B2)

D3 是 (=Sheet1!B3)

这个想法是宏运行并向下搜索列 A，直到它与 C3 匹配。

然后搜索函数在两个单元格上移动并向下搜索直到它与 D3 匹配或直到它碰到一个空白空间。

我不知道如何要求宏根据导入的值进行搜索，也不知道如何要求它搜索我需要的这个奇怪的特定范围。我的想法是，我工作中的某个人可能会出现并在 C10 下方添加一行并添加必要的信息，宏仍然可以工作并搜索到 C11，并且在告诉宏停止之后会有一个空格。

在搜索找到 D3 的匹配项后，它将与匹配项相邻的值返回到顶部的相应单元格 E3、F3 和 G3。

我希望以人们可以理解的方式提出这个问题，我很累所以不知道我是否写了一些有意义的东西。感谢您阅读我的帖子，你们都是最棒的!!

Answer 1

搜索两次

Workbook Download (投递箱)

Sub SearchTwice()

    Const cSheet As String = "Sheet2"   ' Source Worksheet Name
    Const cList As String = "C3"        ' List Cell Range Address
    Const cName As String = "D3"        ' Name Cell Range Address
    Const cListCol As String = "A"      ' List Column Letter
    Const cNameCol As String = "C"      ' Name Column Letter
    Const cFirst As Long = 6            ' First Row
    Const cCol As Long = 3              ' Number of Columns

    Dim rng1 As Range       ' Find List Cell Range
                            ' Found Name Cell Range
    Dim rng2 As Range       ' Next List Cell Range
                            ' Name Search Range
    Dim strList As String   ' List
    Dim strName As String   ' Name

    ' In Source Worksheet
    With ThisWorkbook.Worksheets(cSheet)
        ' Write from List Cell Range to List.
        strList = .Range(cList)
        ' Write from Name Cell Range to Name.
        strName = .Range(cName)
        ' Check if Cell Ranges do NOT contain data.
        If strList = "" Or strName = "" Then  ' Inform user.
            MsgBox "Missing List or Name.", vbCritical, "Missing data"
            Exit Sub
        End If
         ' In List Column
        With .Columns(cListCol)
            ' Create a reference to Find List Cell Range (rng1) containing
            ' List (strList).
            Set rng1 = .Find(strList, .Cells(cFirst - 1), xlValues, xlWhole)
            ' Check if List has not been found.
            If rng1 Is Nothing Then   ' Inform user and exit.
                MsgBox "The list '" & strList & "' has not been found", _
                        vbCritical, "List not found"
                Exit Sub
            End If
            ' Create a reference to Next List Cell Range (rng2).
            Set rng2 = .Find("*", .Cells(rng1.Row), xlValues, xlWhole)
        End With
        ' In Name Column
        With .Columns(cNameCol)
            ' Check if the row of Next List Cell Range (rng2) is greater than
            ' the row of List Cell Range (rng1) i.e. if a cell with a value
            ' has been found below List Cell Range (rng1) in List Column.
            If rng2.Row > rng1.Row Then   ' Next List Cell Range FOUND.
                ' Create a reference to Name Search Range (rng2) which spans
                ' from the cell below Find List Cell Range (rng1) to the cell
                ' above the Next List Cell Range (rng2), but in Name Column.
                Set rng2 = .Cells(rng1.Row + 1).Resize(rng2.Row - rng1.Row - 1)
              Else                        ' Next List Cell Range NOT found.
                ' Create a reference to Name Search Range (rng2) which spans
                ' from the cell below Find List Cell Range (rng1) to the bottom
                ' cell, but in Name column.
                Set rng2 = .Cells(rng1.Row + 1).Resize(.Rows.Count - rng1.Row)
            End If
        End With
        ' In Name Search Range (rng2)
        With rng2
            ' Create a reference to Found Name Cell Range (rng1).
            Set rng1 = .Find(strName, .Cells(.Rows.Count), xlValues, xlWhole)
        End With

        ' Check if Name has not been found.
        If rng1 Is Nothing Then   ' Inform user and exit.
            MsgBox "The name '" & strName & "' has not been found", _
                    vbCritical, "Name not found"
            Exit Sub
        End If

        ' Remarks:
        ' Source Range is calculated by moving the Found Name Cell Range (rng1)
        ' one cell to the right and by resizing it by Number of Columns (cCol).
        ' Target Range is calculated by moving the Name Cell Range one cell
        ' to the right and by resizing it by Number of Columns (cCol).

        ' Copy values of Source Range to Target Range.
        .Range(cName).Offset(, 1).Resize(, cCol) _
                = rng1.Offset(, 1).Resize(, cCol).Value

    End With

    ' Inform user of succes of the operation.
    MsgBox "The name '" & strName & "' was successfully found in list '" & _
            strList & "'. The corresponding data has been written to the " _
            & "worksheet.", vbInformation, "Success"

End Sub