VBA运行时错误1004：含工作表名变量的Index Match公式问题

我正在开发一个带动态Index Match的VBA宏，因为工作表名称会动态变化，所以用变量指定工作表名和数据范围。运行时触发1004运行时错误，报错行是写入Index Match公式的那一行，但我觉得语法没问题——如果去掉Index前的等号执行宏，把RC[-10]换成单元格AA2再手动加等号，公式能正常工作。

原代码如下：

Sub Compare_Results()

    'Create variables
    Dim BOCurrWeek As String
    Dim BOPrevWeek As String
    Dim lastRow As Long
    Dim myRange1 As String
    Dim myRange2 As String
    
    Worksheets("Update Me").Activate
    'Activate Back office sheet
    Worksheets(BOCurrWeek).Activate
    
    'Name new columns
    Range("AK1").Select
    ActiveCell.FormulaR1C1 = "Prev OS"
    Range("AL1").Select
    
    'Get sheet names
    BOPrevWeekN = Range("B4").Value
    BOCurrWeek = Range("B8").Value
 
    'Get number of rows for previous week of Back office data
    Worksheets(BOPrevWeek).Activate
    lastRow = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
    'Debug.Print lastRow
    
    'Set Prev Os formula
    myRange1 = "$T$1:$T$" & lastRow
    myRange2 = "$AA$1:$AA$" & lastRow
    
    Worksheets(BOCurrWeek).Activate

    Range("AK2").Select
    'Gives error
    ActiveCell.FormulaR1C1 = _
        "=Index('" & BOPrevWeek & "'!" & myRange1 & ",Match(AA2,'" & BOPrevWeek & "'!" & myRange2 & ",0),0)"
              
End Sub

1. 引用格式混用：你用了FormulaR1C1属性，但公式里写的是A1格式的AA2，Excel无法解析这种混合格式，直接触发1004错误。
2. 变量未初始化就使用：代码开头先激活Worksheets(BOCurrWeek)，但此时BOCurrWeek还没赋值（赋值操作在后面），这会导致第一次激活工作表时出错。
3. 冗余的Select/Activate操作：这类操作不仅降低代码效率，还容易因焦点变化引发意外错误。

Sub Compare_Results()
    '声明变量
    Dim BOCurrWeek As String
    Dim BOPrevWeek As String
    Dim lastRow As Long
    Dim myRange1 As String
    Dim myRange2 As String
    Dim wsUpdateMe As Worksheet
    
    '直接引用工作表对象，避免Select/Activate
    Set wsUpdateMe = ThisWorkbook.Worksheets("Update Me")
    
    '先获取工作表名称，再进行后续操作
    BOPrevWeek = wsUpdateMe.Range("B4").Value
    BOCurrWeek = wsUpdateMe.Range("B8").Value
    
    '给新列命名
    ThisWorkbook.Worksheets(BOCurrWeek).Range("AK1").Value = "Prev OS"
    
    '获取上周数据的最后一行
    With ThisWorkbook.Worksheets(BOPrevWeek)
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With
    
    '设置数据范围
    myRange1 = "$T$1:$T$" & lastRow
    myRange2 = "$AA$1:$AA$" & lastRow
    
    '用Formula属性写入A1格式公式，和手动测试逻辑一致
    With ThisWorkbook.Worksheets(BOCurrWeek).Range("AK2")
        .Formula = "=INDEX('" & BOPrevWeek & "'!" & myRange1 & ",MATCH(AA2,'" & BOPrevWeek & "'!" & myRange2 & ",0))"
    End With
End Sub

• 将FormulaR1C1替换为Formula，直接使用A1格式引用，和你手动验证的逻辑匹配。
• 调整变量赋值顺序，先从"Update Me"工作表获取名称，再操作对应工作表，避免未初始化变量的问题。
• 移除所有Select和Activate操作，改用工作表对象直接操作单元格，代码更稳定高效。
• 保留了工作表名称的单引号包裹，自动兼容带空格或特殊字符的工作表名。

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