excel - 如何在VBA中的vlookup中使用动态匹配公式？

Question

我需要 VBA 中的代码，该代码相当于 Excel 中 vlookup 内的匹配。有两张表，一张是 MasterSheet，另一张是 required_data_sheet。

MasterSheet 中有 18 列，而 required_data_sheet 中只有 MasterSheet 18 列中的 11 列(并非所有这 11 列都与 MasterSheet 中前 11 列的顺序相同)。两张表中的第一列都是 item_id，它是主键。 MasterSheet 有 45000 条记录，Required_data_sheet 只有几百行，仅填充第一列(即项目 ID)，对于这几百行，需要填充其他 10 条记录(第 11-1 列)。

我可以在 required_data_sheet 中使用以下公式从母版表中获取数据，这会给出正确的结果。我只在 required_data_sheet 的单元格 A2 中写入此公式，然后将相同的公式复制到所有单元格中。

=VLOOKUP($A2,Master,MATCH(B$1,Master[#Headers],0),FALSE)

Master是MasterSheet中数据的表名。

问题是我无法为此编写正确的 VBA 代码。匹配公式部分的代码有问题。

下面是我的代码，匹配公式阻止它给出所需的结果。

Sub Fetch_Specific_Columns()
    Dim lastrow As Long
    Dim lastcolumn As Integer
    Dim c As Integer
    Dim r As Long

    lastrow = Range("A" & Rows.Count).End(xlUp).row
    lastcolumn = Range("A1").End(xlToRight).Column

    For c = 2 To lastcolumn
        For r = 2 To lastrow
            Worksheets("Required_Data_Sheet").Cells(r, c).Formula = "=VLOOKUP(A" & r & ",Master,MATCH(" & Worksheets("Required_Data_Sheet").Cells(1, c) & ",'Master Sheet'!$A$1:$R$1,0),FALSE)"
        Next r        
    Next c
End Sub

预期结果是匹配公式以所需的方式工作。

Answer 1

您的代码的问题在于这部分:

MATCH("& Worksheets("Required_Data_Sheet").Cells(1, c) & ",

它将单元格(1,c)中的值直接插入到公式中，不带引号。这是不正确的 Excel 公式语法。相反，您应该:

1. 插入单元格引用(例如 B3)或
2. 引号中单元格的值。

以下是方法 1 的整行修正:

工作表("Required_Data_Sheet").Cells(r, c).Formula = "=VLOOKUP(A"& r & ",Master,MATCH("& Worksheets("Required_Data_Sheet").Cells(1, c).Address & ",'主表'!$A$1:$R$1,0),FALSE)"

以下是方法 2 的整行修正:

工作表("Required_Data_Sheet").Cells(r, c).Formula = "=VLOOKUP(A"& r & ",Master,MATCH("""& Worksheets("Required_Data_Sheet").Cells( 1, c) & """,'主表'!$A$1:$R$1,0),FALSE)"