如何在VBA生成的Excel Table中预置引用首列的VLOOKUP公式？

以下是修改后的VBA代码，可在创建表格时自动为每行单元格预置VLOOKUP公式，所有单元格均引用该行第一列作为查找值：

Sub GenerateSupplyChain()
    Dim MySheet As String, ws As Worksheet
    MySheet = Sheets("Instructions").Range("T1").Value
    Set ws = Sheets(MySheet)
    
    Const COL_KEY = "O" ' 用于确定表格起始行的参考列
    Const FIRST_ROW = 5
    Const HEADER_RNG = "D2:AS2" ' 表格表头的来源区域
    Const BASE_SHT = "Base Data"
    Dim i As Variant
    i = InputBox("本次供应链包含多少个DFSP？", "输入数量")
    If Not IsNumeric(i) Then
        MsgBox "请输入数字。", vbCritical
        Exit Sub
    End If
    If i < 1 Then Exit Sub
    
    Dim oSht As Worksheet, LastRow As Long
    Set oSht = Sheets(MySheet)
    LastRow = oSht.Cells(oSht.Rows.Count, COL_KEY).End(xlUp).Row
    With oSht.Cells(LastRow, COL_KEY)
        If Len(.Value) > 0 Or (Not .ListObject Is Nothing) Then
            LastRow = LastRow + 2
        End If
        If LastRow < FIRST_ROW Then LastRow = FIRST_ROW
    End With
    Dim tabRng As Range, headerRng As Range, objTable As ListObject
    Set headerRng = Sheets(BASE_SHT).Range(HEADER_RNG)
    Set tabRng = oSht.Cells(LastRow, COL_KEY).Resize(i + 1, headerRng.Columns.Count)
    Set objTable = oSht.ListObjects.Add(xlSrcRange, tabRng, , xlYes)
    objTable.HeaderRowRange.Value = headerRng.Value
    objTable.ShowTotals = True
    objTable.TableStyle = "TableStyleLight1"
    
    ' 核心新增：为表格数据区域批量设置VLOOKUP公式
    Dim dataCol As ListColumn
    Dim firstColName As String
    firstColName = objTable.ListColumns(1).Name ' 获取表格第一列的表头名称
    
    ' 遍历表格每一列（第一列留作手动输入查找键，从第二列开始设置公式）
    For Each dataCol In objTable.ListColumns
        If dataCol.Index > 1 Then
            ' 构建结构化引用的VLOOKUP公式，自动适配每行的第一列值
            dataCol.DataBodyRange.Formula = _
                "=VLOOKUP([@[" & firstColName & "]], '" & BASE_SHT & "'!" & headerRng.EntireColumn.Address & ", " & dataCol.Index & ", FALSE)"
        End If
    Next dataCol
End Sub

关键修改说明 #

• 结构化引用：使用[@[" & firstColName & "]]确保公式始终引用当前行的第一列单元格，无需手动调整单元格地址。
• 批量设置公式：通过遍历表格的每一列，一次性为整列数据区域设置公式，提升效率。
• 动态适配数据源：公式中引用Base Data的表头对应整列范围，确保数据源扩展时公式仍有效。

注：若需要第一列也设置公式（不推荐，易导致循环引用），可删除If dataCol.Index > 1 Then判断语句。

内容的提问来源于stack exchange，提问作者Ryan Data Guy