excel - VBA:匹配来自不同工作簿的单元格

Question

我有一个主工作表正在被其他工作簿更新。 Master 中的“A 列”有一个 ID 号，其他电子表格也有一个列，比如说“B”列包含该 ID 号。
我想将另一个工作簿中的 ID 号与主工作簿相匹配，并在 A 列中匹配的位置将其他列拉到主工作簿中，以填写正确标题下的空白。
我得到了这个宏，但它与任何数据都不匹配。

Sub Macro1()

Dim mastersheet As Worksheet
Set mastersheet = ActiveSheet
For a = 2 To 1000
    ValueToCheck = Cells(a, 1).Value
    fname = "File location"
    Dim Workbooktocheck As Workbook
    Set Workbooktocheck = Workbooks.Open("filename")
    For b = 2 To 1000
        valueInNew = Workbooktocheck.Sheets("Sheet1").Cells(b, 12)
        If ValueToCheck = valueInNew Then
            mastersheet.Cells(a, 3).Value = Workbooktocheck.Sheets("sheet1").Cells(b, 7)
        End If
    Next b
Next a

End Sub

更新
这是大师:

这是更新的电子表格:

Answer 1

Match()只要您只期望一个匹配的行，通常比嵌套循环快。
尝试这个:

Sub Macro1()

    Dim wsMaster As Worksheet, wbToCheck As Workbook, wsCheck As Worksheet
    Dim ValueToCheck, a As Long, m
    
    Set wsMaster = ActiveSheet
    Set wbToCheck = Workbooks.Open("filepathgoeshere")
    Set wsCheck = wbToCheck.Worksheets("Sheet1") '<<< was missing this
    
    For a = 2 To wsMaster.Cells(Rows.Count, 1).End(xlUp).Row 
        ValueToCheck = wsMaster.Cells(a, 1).Value
        If Len(ValueToCheck) > 0 Then
            'match is faster than using a nested loop
            m = Application.Match(ValueToCheck, wsCheck.Range("L:L"), 0)
            If Not IsError(m) Then
                'got a match
                With wsMaster.Rows(a)
                    .Columns("C").Value = wsCheck.Cells(m, "G").Value
                    'etc for other cells...
                End With
            End If
        End If
    Next a

End Sub