excel - VBA 中是否有类似索引的函数可以在完美匹配时返回多列数据？

Question

我有 2 个包含以下数据的工作簿：

如图所示，A 列是我映射这两个工作簿的唯一键。我要填充的值是原始文件（image1）中的“待定”。所以我在主文件中寻找密钥 11x 并获取该密钥的 B、C 和 D 列（组件、子和零件）的值。

我“尝试”match了 func 但无法确定复制搜索值的目的地，而且我的代码一次只返回一个值……

for R = 2 To lastrow
    y=application.match(worksheet2.cells(R,1), worksheet2.range("A:A"),0)
If not application.isnumber(y) Then
    worksheet2.cells(x,1).copy destination:=worksheet1.cells(**?????????**)

那么，我可以使用一个索引匹配公式返回所有三列值吗？如果不是，我如何在 VB 中编写搜索功能？请帮忙。

标签： excelvba

Answer 1

此代码在启动搜索的工作簿中执行。

Private Sub CmdBtn_Click()
On Error GoTo workbookErr
Dim WbMaster As Worksheet: Set WbMaster = Workbooks("MasterWorkBook.xlsm").Worksheets("Sheet1")
Dim WbCopyTo As Worksheet: Set WbCopyTo = ThisWorkbook.Worksheets("Sheet1")
Dim rangeWbCopyTo As Range, rangeWbMaster As Range
Dim cellWbCopyTo As Range, cellWbMaster As Range
Dim i As Integer

With WbMaster
    Set rangeWbMaster = .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
End With
With WbCopyTo
    Set rangeWbCopyTo = .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
End With

For Each cellWbCopyTo In rangeWbCopyTo
    For Each cellWbMaster In rangeWbMaster
        If cellWbCopyTo.Value = cellWbMaster.Value Then
            For i = 0 To 2
                cellWbCopyTo.Offset(0, 1 + i).Value = cellWbMaster.Offset(0, 1 + i).Value
            Next i
            Exit For
        End If
    Next cellWbMaster
Next cellWbCopyTo
Exit Sub
workbookErr:
MsgBox "Open MasterWorkBook before executing the search.", vbCritical
End Sub