VBA宏中VLOOKUP公式异常及动态目标表设置求助

Sub InputFormula()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim excludeSheets As Object
    Dim targetSheets As Object ' 存储各工作表对应的VLOOKUP目标表

    ' 创建排除工作表列表
    Set excludeSheets = CreateObject("Scripting.Dictionary")
    excludeSheets.Add "Magic buttons", True
    excludeSheets.Add "Observations", True

    ' 创建工作表-目标表映射（根据你的需求修改）
    Set targetSheets = CreateObject("Scripting.Dictionary")
    targetSheets.Add "表1", "表2"
    targetSheets.Add "表2", "表1"
    targetSheets.Add "表3", "表2"
    ' 可继续添加更多工作表的映射规则

    ' 遍历所有工作表
    For Each ws In ThisWorkbook.Worksheets
        If Not excludeSheets.exists(ws.Name) Then
            ' 检查当前工作表是否有对应的目标表配置
            If targetSheets.exists(ws.Name) Then
                ' 插入A列
                ws.Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
              
                ' A2输入拼接公式（简化CONCATENATE写法）
                ws.Range("A2").FormulaR1C1 = "=RC[1]&RC[2]&RC[3]&RC[4]"
                lastRow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row
                ws.Range("A2:A" & lastRow).FillDown
                ws.Columns("A:A").AutoFit

                ' 获取当前工作表对应的目标表名
                Dim targetSheetName As String
                targetSheetName = targetSheets(ws.Name)

                ' H2输入VLOOKUP公式并填充到最后一行
                ws.Range("H2").Formula = "=VLOOKUP(A2,'" & targetSheetName & "'!A:G,7,FALSE)"
                ws.Range("H2:H" & lastRow).FillDown

                ' I2输入匹配公式并填充到最后一行
                ws.Range("I2").Formula = "=G2=H2"
                ws.Range("I2:I" & lastRow).FillDown
            Else
                ' 无目标表配置时的提示（可选）
                MsgBox "未配置工作表" & ws.Name & "的VLOOKUP目标表，已跳过该表"
            End If
        End If
    Next ws
End Sub

1. 修复公式格式异常 #

你之前使用FormulaR1C1属性但传入A1样式的公式（如A2、G2），导致Excel解析错误。这里改用Formula属性直接写入A1样式公式，更符合日常书写习惯，避免格式混乱。同时将CONCATENATE简化为直接用&拼接，效果一致且更简洁。

2. 实现动态VLOOKUP目标表 #

通过targetSheets字典建立工作表与目标表的映射关系：

• 字典的键是当前工作表名称，值是VLOOKUP需要引用的目标工作表名称
• 遍历工作表时，先检查当前表是否在映射字典中，存在则取出目标表名拼接到公式中
• 后续新增工作表规则，只需在字典中添加新的键值对即可

额外优化 #

• 补充了H列和I列公式的FillDown操作，确保所有数据行都应用公式
• 添加目标表配置检查，避免因未配置规则导致的运行错误

内容的提问来源于stack exchange，提问作者Mightynubnub