VBA中INDEX/MATCH公式逻辑正确却返回空值，报对象定义错误

核心错误原因及修复步骤 #

1. 公式参数分隔符不匹配 #

Excel界面中公式参数用分号（;）是区域设置适配的结果，但VBA默认使用**逗号（,）**作为公式参数分隔符（对应Formula属性）。直接用分号拼接公式赋值会触发语法错误，这是报错的主要原因。

2. 未定义的变量ws_comp #

代码中ws_comp.Range("A1").Offset(0,25).Value里的ws_comp从未声明和赋值，即使删除公式行，这行也会触发运行时错误，必须先完成声明与赋值。

3. 变量声明不规范 #

k变量未声明，属于隐式变体类型，建议显式声明为整数类型，同时优化单元格引用的生成方式，避免手动拼接工作表名的出错风险。

修正后的代码 #

Sub fetching_acq_data_compustat()
    Dim ws_input As Worksheet
    Dim rngTkr As Range
    Dim c As Range
    Dim ws_output As Worksheet
    Dim ws_comp As Worksheet ' 新增工作表变量声明
    Dim strTkrCell As String
    Dim strDatedCell As String
    Dim k As Integer ' 显式声明k的类型

    Set ws_input = ThisWorkbook.Sheets("MA_ExportFiltered_RawData")
    Set ws_output = ThisWorkbook.Sheets("Acquirer_ETR")
    Set ws_comp = ThisWorkbook.Sheets("US acq_CUSIP data") ' 赋值ws_comp，根据实际表名调整

    With ws_input
        Set rngTkr = .Range("R43:R3223")
        For Each c In rngTkr
            ' 自动生成带工作表名的绝对引用，无需手动拼接
            strTkrCell = c.Address(True, True, xlA1, True)
            k = 14
            If k < 16 Then
                strDatedCell = c.Offset(0, k).Address(True, True, xlA1, True)

                ws_output.Range("A1").Offset(2, 0).Value = c.Offset(0, 14).Value
                
                ' 用FormulaLocal保留分号分隔符，适配本地区域设置
                ws_output.Range("A1").Offset(2, 1).FormulaLocal = _
                    "=INDEX('US acq_CUSIP data'!$A$3:$AJ$77388;" & _
                    "MATCH(1;('US acq_CUSIP data'!$I$3:$I$77388=" & strTkrCell & _
                    ")*('US acq_CUSIP data'!$C$3:$C$77388=" & strDatedCell & ");0);26)"

                ws_output.Range("A1").Offset(1, 1).Value = ws_comp.Range("A1").Offset(0, 25).Value
                ws_output.Range("A1").Offset(0, 1).Value = c.Value

                k = k + 1
            End If
        Next
    End With
End Sub

额外优化说明 #

• 移除了不必要的ws_input.Activate，VBA操作工作表无需激活，直接通过对象引用更高效稳定。
• 使用Address(True, True, xlA1, True)自动生成带工作表名的绝对引用，避免手动拼接时的拼写错误。
• 启用Option Explicit（代码顶部添加）可以强制变量声明，进一步减少隐式错误。

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