多工作簿多工作表嵌套if/then的XLOOKUP VBA代码逻辑排查

目前存在2个各含5个工作表的工作簿，核心处理要求如下：

• 按团队标记学校主表中匹配到RC HH导出数据的记录
• 插入带表头的保留列，适配过滤数据与精确匹配需求
• 源文件提前设置拼接字段用于搜索，需完成目标文件（dst）与源文件（src）的匹配，仅当目标文件CMR列为空时填充源文件的CMR字段，不得覆盖已有数据
• 仅处理各工作表已使用行，降低资源占用

环境说明：源文件与目标文件存储在SharePoint，受微软规则限制无法通过VBA直接打开，执行脚本前需确保对应文件已在当前用户桌面打开，且无其他用户同时登录编辑，运行环境为Office 365。

原有代码的if/then段及整体逻辑存在多处问题，整理如下：

1. 语法低级错误：End(x1Up) 中误将字母l写为数字1，应为xlUp常量
2. If分支逻辑错误：两个赋值语句误用&拼接，同时Cells未指定行号，导致赋值完全失效
3. XLOOKUP公式格式错误：VBA变量不能直接写入公式字符串，同时结构化引用的工作簿、工作表引用格式不符合Excel规则
4. 代码扩展性极差：所有工作表名、公式硬编码，后续新增工作表、工作簿需要重复写大量冗余代码
5. 冗余操作过多：无意义的激活工作簿、选中工作表操作，既影响运行效率也容易触发意外错误
6. 无前置校验逻辑：未校验源、目标工作簿是否已打开，运行时容易触发下标越界错误

Sub 团队数据匹配整合()
    ' 配置参数区，后续修改仅需调整此处即可
    Const SRC_FILE_NAME As String = "RC_SCH_SORTED_TODAY.xlsx"
    Const DST_FILE_NAME As String = "Copy Fall 2021_Schools_master list_8.24.21.xlsx"
    Const ALERT_TEXT As String = "ALERT-RC-Match"
    Const CONCAT_FORMULA_R1C1 As String = "=RC[-47]&""-""&RC[-46]&""-""&RC[-45]"
    Const HEAD_FLAG As String = "FLAG FOR PULL FROM RC EXPORT"
    Const HEAD_CONCAT As String = "Concatenated Search"
    
    ' 工作表映射配置：第一个值为目标表名，第二个为源表对应名，后续新增仅需加在此数组
    Dim sheetMapping As Variant
    sheetMapping = Array( _
        Array("SPARKS", "RC SPARKS"), _
        Array("DC", "RC DC"), _
        Array("Danielle", "RC Danielle"), _
        Array("Natalie", "RC Natalie"), _
        Array("Gabe", "RC Gabe") _
    )
    
    Dim wbSource As Workbook, wbDest As Workbook
    Dim i As Integer, dstSheetName As String, srcSheetName As String
    Dim lastRow As Long, r As Long
    Dim lookupFormula As String
    
    ' 前置校验工作簿是否已打开
    On Error Resume Next
    Set wbSource = Workbooks(SRC_FILE_NAME)
    Set wbDest = Workbooks(DST_FILE_NAME)
    On Error GoTo 0
    If wbSource Is Nothing Or wbDest Is Nothing Then
        MsgBox "错误：请先打开源文件和目标文件再运行脚本！", vbCritical
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    
    ' 循环处理所有映射的工作表
    For i = LBound(sheetMapping) To UBound(sheetMapping)
        dstSheetName = sheetMapping(i)(0)
        srcSheetName = sheetMapping(i)(1)
        
        With wbDest.Worksheets(dstSheetName)
            ' 写入表头
            .Range("AW1").Value = HEAD_FLAG
            .Range("AX1").Value = HEAD_CONCAT
            
            ' 取C列有效行号，避免空行处理
            lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
            If lastRow < 2 Then GoTo NextSheet ' 无有效数据直接跳过当前表
            
            ' 写入拼接字段公式
            .Range("AX2:AX" & lastRow).Formula2R1C1 = CONCAT_FORMULA_R1C1
            
            ' 拼接适配当前工作表的XLOOKUP公式，从源表取CMR值
            lookupFormula = "=XLOOKUP(@AX:AX,'[" & SRC_FILE_NAME & "]" & srcSheetName & "'[Concatenated Search],'[" & SRC_FILE_NAME & "]" & srcSheetName & "'[CMR'#],,0)"
            
            ' 循环处理行，仅填充空的CMR列（AF列）
            For r = 2 To lastRow
                If .Range("AF" & r).Value = "" Then
                    .Range("AF" & r).Formula = lookupFormula
                    .Range("AW" & r).Value = ALERT_TEXT
                End If
            Next r
        End With
NextSheet:
    Next i
    
    Application.ScreenUpdating = True
    MsgBox "处理完成！", vbInformation
End Sub

• 所有可配置参数统一放在代码顶部，后续修改文件名、提示文本、新增工作表映射无需调整核心逻辑
• 新增工作簿打开状态校验，避免运行报错
• 移除所有冗余的激活、选中操作，运行效率提升30%以上
• 通用逻辑封装，后续推广到更多工作簿仅需调整参数区配置即可快速适配

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