Excel数组动态值公式工作表有效，VBA中出现类型不匹配错误

核心问题 #

你遇到的类型不匹配错误主要来自两个点：

1. 变量类型不匹配：Mtch被定义为Long类型，但ADDRESS函数返回的是单元格地址字符串（如$M$6），赋值时必然触发类型不匹配。
2. 公式字符串拼接错误：原Evaluate的公式字符串存在括号不完整、引号转义错误，且代码中未将Evaluate的结果赋值给Mtch，导致后续Range(Mtch)调用无有效值。

另外，原代码中直接使用Cells、Range未指定工作表对象，容易引发跨表错误，建议加上工作表限定。

修正步骤 #

1. 修正变量类型与公式赋值 #

将Mtch的类型改为String，并正确拼接公式字符串完成赋值：

' 修正变量定义（把Mtch从Long改为String）
Dim Mtch As String
' ...其他代码...
' 正确拼接公式并赋值
Mtch = Application.Evaluate("ADDRESS(INDEX(M:M,MATCH(TRUE,INDEX((M:M+" & row1.Value & ">0),),0)),13)")

注意：公式字符串的括号要完全匹配，原代码末尾少了一个闭合括号。

2. 优化公式逻辑（可选，避免字符串拼接） #

直接用VBA调用工作表函数替代Evaluate字符串，更易维护且减少语法错误：

Dim matchRow As Long
' 找到第一个满足M列值 + row1.Value > 0的行号
matchRow = Application.Match(True, Application.Index(M:M + row1.Value > 0, 0), 0)
' 获取对应单元格地址
Mtch = Application.Address(matchRow, 13) ' 13代表M列

3. 完整修正后的关键代码片段 #

Sub SpreadMacro()
    Dim ws As Worksheet
    Set ws = ActiveSheet ' 建议改为具体工作表，如ThisWorkbook.Worksheets("Sheet1")
    
    Dim Lr As Long
    Dim rng As Range
    Dim row1 As Range
    Dim Sp1 As Long
    Dim Sp2 As Long
    Dim Sp3 As Long
    Dim Mtch As String ' 修正为String类型
    Dim MtchGL As String
    Dim rng2 As Range

    'Find table length
    Lr = ws.Cells(ws.Rows.Count, "H").End(xlUp).Row
    Set rng = ws.Range("H4:H" & Lr)

    'Find negative values and set spread values
    For Each row1 In rng.Rows
        If row1.Value >= 0 Then
            GoTo Skip
        Else
            Set rng2 = ws.Range("M4:M" & Lr)
            If Application.WorksheetFunction.Max(rng2) + row1.Value < 0 Then
                row1.Interior.ColorIndex = 8
                GoTo Skip
            Else
                Sp1 = row1.Offset(0, 6).Value ' 加上.Value避免隐式转换问题
                Sp2 = row1.Offset(0, 7).Value
                Sp3 = row1.Offset(0, 8).Value
                
                ' 修正公式赋值逻辑
                Mtch = Application.Evaluate("ADDRESS(INDEX(M:M,MATCH(TRUE,INDEX((M:M+" & row1.Value & ">0),),0)),13)")
                ' 或者用VBA函数调用方式：
                ' Dim matchRow As Long
                ' matchRow = Application.Match(True, Application.Index(ws.Range("M:M") + row1.Value > 0, 0), 0)
                ' Mtch = ws.Cells(matchRow, 13).Address
                
                MtchGL = ws.Range(Mtch).Offset(0, -9).Value
                
                'Insert spread values into the matched account
                ws.Range(Mtch).Offset(0, 1).Formula = ws.Range(Mtch).Offset(0, 1).Formula & "+" & Sp1
                ws.Range(Mtch).Offset(0, 2).Formula = ws.Range(Mtch).Offset(0, 2).Formula & "+" & Sp2
                ws.Range(Mtch).Offset(0, 3).Formula = ws.Range(Mtch).Offset(0, 3).Formula & "+" & Sp3
                
                'Clear the negative variance from over budget account and indicate where the variance was spread
                row1.Offset(0, 5).Value = "Spread in " & MtchGL
                row1.Offset(0, 6).Value = 0
                row1.Offset(0, 7).Value = 0
                row1.Offset(0, 8).Value = 0
            End If
        End If
Skip:
    Next row1
End Sub

额外注意事项 #

• 当row1.Value为负数时，拼接后的公式M:M+负数>0等价于M:M>绝对值，逻辑和原Excel公式一致，无需额外处理符号。
• 建议添加错误处理（如On Error Resume Next或On Error GoTo），避免因无匹配项导致代码崩溃。
• 尽量避免整列引用（M:M），改用实际数据范围（如M4:M & Lr），提升运行效率。

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