VBA填充公式时出现Application-defined或object-defined错误的解决方法

错误原因分析 #

触发"Application-defined or object-defined error"的核心问题有两个：

1. 公式字符串语法错误：原代码中COUNTIFS的<>条件拼接方式错误，导致生成的Excel公式不符合语法规范
2. 最后一行行数取值错误：使用源工作表的最后一行来计算目标工作表的填充范围，两者行数不匹配，可能引发范围越界问题

具体修复步骤 #

1. 修正BG列公式的字符串拼接 #

原错误代码：

wsDest.Range("BG5").Formula = "=IF(COUNTIFS(K:K,K5,M:M,""<>" & M5 & ")=0,""1 supplier"",""Multi"")"

修正后代码（确保公式中<>与单元格引用的拼接符合Excel语法，同时正确转义双引号）：

wsDest.Range("BG5").Formula = "=IF(COUNTIFS(K:K,K5,M:M,""<>" & M5 & """)=0,""1 supplier"",""Multi"")"

2. 修正lastRow的取值逻辑 #

原代码取源工作表的最后一行，改为取目标工作表的实际数据最后一行（以BF列为准）：

lastRow = wsDest.Cells(wsDest.Rows.Count, "BF").End(xlUp).Row

3. 可选优化：替代AutoFill提升效率 #

可以直接给整个目标区域赋值公式，避免AutoFill的潜在问题，同时提升执行效率：

wsDest.Range("BG5:BG" & lastRow).Formula = "=IF(COUNTIFS(K:K,K5,M:M,""<>" & M5 & """)=0,""1 supplier"",""Multi"")"
wsDest.Range("BH5:BH" & lastRow).Formula = "=IFERROR(MID($K5,FIND(""/"",$K5)+1,100),"""")"

完整修正后的代码 #

Sub FillFormulas()
Dim wbCreatingReports As Workbook
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim wsDestPlant As Worksheet
Dim filterValue As Variant
Dim lastRow As Long
Dim destFolder As String
Dim destFile As String
Dim wbDest As Workbook ' 补充声明遗漏的变量

' 打开"Creating reports"工作簿
Set wbCreatingReports = ThisWorkbook

' 打开数据源工作簿
Set wsSource = Workbooks.Open(ThisWorkbook.Path & "\07 Step ME External Production material_report.xlsx").Worksheets("Local_Currency")

' 设置目标文件夹路径
destFolder = ThisWorkbook.Path & "\Final reports\"

' 遍历目标文件夹中的所有Excel文件
destFile = Dir(destFolder & "*.xlsx")
Do While destFile <> ""
    ' 打开目标工作簿
    Set wbDest = Workbooks.Open(destFolder & destFile)
    ' 指定目标工作表
    Set wsDest = wbDest.Worksheets("PPF_Local_CY")
    Set wsDestPlant = wbDest.Worksheets("Cover")
    ' 获取筛选值（目标工作簿Cover表C10单元格）
    filterValue = wsDestPlant.Range("C10").Value
    
    ' 对源数据进行筛选（第7列）
    wsSource.AutoFilterMode = False
    wsSource.Range("A1:BF100000").AutoFilter Field:=7, Criteria1:=filterValue
    
    ' 复制筛选后的可见数据到目标工作表（从A5开始）
    wsSource.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy wsDest.Range("A5")
    
    ' 计算目标工作表BF列的最后一行
    lastRow = wsDest.Cells(wsDest.Rows.Count, "BF").End(xlUp).Row
    
    ' 批量填充BG列公式
    wsDest.Range("BG5:BG" & lastRow).Formula = "=IF(COUNTIFS(K:K,K5,M:M,""<>" & M5 & """)=0,""1 supplier"",""Multi"")"
    
    ' 批量填充BH列公式
    wsDest.Range("BH5:BH" & lastRow).Formula = "=IFERROR(MID($K5,FIND(""/"",$K5)+1,100),"""")"
    
    ' 取消源工作表的筛选
    wsSource.AutoFilterMode = False
    
    ' 保存并关闭目标工作簿
    wbDest.Close SaveChanges:=True
    
    ' 获取下一个文件
    destFile = Dir
Loop
End Sub

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