VBA宏执行COUNTIF公式时崩溃，请求问题排查

核心问题解析 #

• COUNTIF参数顺序完全错误：COUNTIF的标准语法是COUNTIF(统计范围, 匹配条件)，你写反了参数位置，这是公式直接崩溃的核心原因。
• VBA变量未正确嵌入公式字符串：公式里的OldPath是VBA变量，直接写在双引号里Excel无法识别，必须把变量值拼接成Excel能识别的外部工作簿引用格式（路径用方括号包裹，工作表名加单引号）。
• 依赖Active对象导致引用混乱：连续打开两个工作簿后，ActiveSheet指向最后打开的文件，公式未明确区分两个工作簿的范围，容易引发引用歧义。
• AutoFill写法冗余易出错：可以直接给整列批量写入公式，没必要先用AutoFill绕弯子。

修复后的完整代码 #

Sub RRQP()
    'RRQP Macro
    '统计旧工作簿数据在新工作簿的出现次数
    
    Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
    
    Dim FullPath As String
    Dim OldPath As String
    Dim wbOld As Workbook, wbNew As Workbook
    Dim wsNew As Worksheet
    Dim last_row As Long
    
    '读取单元格中的路径
    FullPath = Range("G6").Value
    OldPath = Range("G4").Value
    
    '用对象变量绑定工作簿，避免依赖Active对象
    Set wbOld = Workbooks.Open(OldPath)
    Set wbNew = Workbooks.Open(FullPath)
    Set wsNew = wbNew.ActiveSheet
    wsNew.Name = "Transaction Report"
    
    Application.DisplayAlerts = True
    Application.AskToUpdateLinks = True
    
    '获取B列最后一行的行号
    last_row = wsNew.Cells(wsNew.Rows.Count, 2).End(xlUp).Row
    
    '构建符合Excel规范的COUNTIF公式，拼接外部工作簿引用
    wsNew.Range("D2:D" & last_row).Formula = _
        "=COUNTIF('[" & wbOld.Name & "]" & wbOld.ActiveSheet.Name & "'!$A$1:$A$10000,A2)"
    
    '可选：如果要固定统计结果，可将公式转为值（避免后续文件路径变动出错）
    'wsNew.Range("D2:D" & last_row).Value = wsNew.Range("D2:D" & last_row).Value
    
    '清理对象变量
    Set wsNew = Nothing
    Set wbNew = Nothing
    Set wbOld = Nothing
End Sub

关键修复点说明 #

• 修正COUNTIF参数顺序：将旧工作簿的统计范围放在第一个参数，当前表的A2匹配条件放在第二个参数。
• 规范外部工作簿引用格式：通过'[" & wbOld.Name & "]" & wbOld.ActiveSheet.Name & "'!$A$1:$A$10000拼接出Excel能识别的外部范围，打开后的工作簿引用用文件名而非完整路径。
• 用对象变量替代Active对象：明确绑定wbOld（旧工作簿）、wbNew（新工作簿）、wsNew（新工作簿的目标工作表），杜绝Active对象切换导致的引用错误。
• 批量写入公式：直接给D2到最后一行的区域赋值公式，比AutoFill更高效稳定。

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