通过VBA宏写入Excel公式时遭遇1004错误求助

我有两个Excel工作簿，尝试通过VBA宏向单元格写入公式时，始终出现1004错误；但手动将公式复制到单元格却可正常运行。以下是出错的VBA代码及可正常运行的手动公式：

出错的VBA代码 #

Sub Macro1()

Dim WorkbookName As String
Dim WB As Workbook
Dim Sht As Worksheet


WorkbookName = "WhatsApp_IF_SuspenseAging_082023.xlsx"

' set the workbook object
On Error Resume Next
Set WB = Workbooks(WorkbookName) ' first try to see if the workbook already open
On Error GoTo 0
If WB Is Nothing Then ' if workbook = Nothing (workbook is closed)
    Set WB = Workbooks.Open("C:\RPA\IDN-OPS-POS-08_SuspenseAging\Status_WA\082023\" & WorkbookName)
End If

' set the worksheet object

ThisWorkbook.Activate

Range("P2:P" & LastRow).Formula = "=IF(NOT(ISERROR(MATCH(A3,[WhatsApp_IF_SuspenseAging_082023.xlsx]Sheet1!$A:$A,0))), IF(INDIRECT(CELL("""address""" & ",INDEX([WhatsApp_IF_SuspenseAging_082023.xlsx]Sheet1!$A:$O,MATCH(A3,[WhatsApp_IF_SuspenseAging_082023.xlsx]Sheet1!$A:$A,0),15)))="""No""" & ", TRUE, FALSE), FALSE)"

End Sub

手动可正常运行的公式 #

=IF(NOT(ISERROR(MATCH(A2,[WhatsApp_IF_SuspenseAging_082023.xlsx]Sheet1!$A:$A,0))), IF(@INDIRECT(@CELL("address",INDEX([WhatsApp_IF_SuspenseAging_082023.xlsx]Sheet1!$A:$O,MATCH(A2,[WhatsApp_IF_SuspenseAging_082023.xlsx]Sheet1!$A:$A,0),15)))="No", TRUE, FALSE), FALSE)

错误原因及修复方案 #

1. 字符串转义错误 #

VBA中用双引号包裹字符串时，内部的双引号需要用两个双引号转义，原代码存在多处转义错误和多余拼接：

• 错误写法：CELL("""address""" & ",INDEX(... → 正确转义后应为CELL("""address""",INDEX(...
• 错误写法：="""No""" & ", TRUE → 正确应为="""No""", TRUE

2. 单元格引用不一致 #

VBA代码中公式引用A3，但手动公式用A2，导致批量填充时引用错位，需统一为A2（对应起始行P2）。

3. 动态数组@符号冗余 #

手动公式中的@INDIRECT、@CELL是Excel动态数组的隐式交集运算符，但VBA批量写入公式时无需添加，Excel会自动适配每个单元格的引用。

4. 未定义LastRow变量 #

原代码未定义和赋值LastRow，导致Range("P2:P" & LastRow)无效，需先获取数据区域的最后行号。

修复后的完整VBA代码 #

Sub Macro1()
    Dim WorkbookName As String
    Dim WB As Workbook
    Dim Sht As Worksheet
    Dim LastRow As Long ' 定义最后行号变量
    
    WorkbookName = "WhatsApp_IF_SuspenseAging_082023.xlsx"
    
    ' 检查目标工作簿是否已打开，未打开则启动
    On Error Resume Next
    Set WB = Workbooks(WorkbookName)
    On Error GoTo 0
    If WB Is Nothing Then
        Set WB = Workbooks.Open("C:\RPA\IDN-OPS-POS-08_SuspenseAging\Status_WA\082023\" & WorkbookName)
    End If
    
    ' 获取当前工作表A列的最后数据行号
    LastRow = ThisWorkbook.ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    
    ' 写入修复后的公式
    ThisWorkbook.ActiveSheet.Range("P2:P" & LastRow).Formula = _
        "=IF(NOT(ISERROR(MATCH(A2,[WhatsApp_IF_SuspenseAging_082023.xlsx]Sheet1!$A:$A,0))), " & _
        "IF(INDIRECT(CELL(""address"",INDEX([WhatsApp_IF_SuspenseAging_082023.xlsx]Sheet1!$A:$O,MATCH(A2,[WhatsApp_IF_SuspenseAging_082023.xlsx]Sheet1!$A:$A,0),15))=""No"", TRUE, FALSE), FALSE)"
End Sub

额外优化建议 #

• 避免使用Activate，直接指定工作表对象操作（比如Set Sht = ThisWorkbook.Sheets("你的工作表名")），再通过Sht.Range(...)写入公式，代码更稳定。
• 若使用Excel 365，可改用Formula2替代Formula，更好适配动态数组功能。

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