VBA宏运行时错误1004：基于日期填充VLOOKUP公式失败

问题场景 #

运行以下VBA宏时持续抛出运行时错误'1004'，宏意图基于日期条件用VLOOKUP公式填充单元格：

Sub CC_Update()

    Dim DateR As Range
    Dim Update_till As Date
    
    Dim Linked_Users_CC As Range
    Dim Applications_CC As Range
    Dim New_Provisioned_CC As Range
    Dim Total_Accounts_Act_CC As Range
    Dim total_Spend_CC As Range
    Dim total_Transactions_CC As Range
    Dim App_To_Provisioned_CC As Range
    
    ' Set the ranges for the data
    Set DateR = Range("B5:B32")
    Update_till = Range("'Audit'!C1")
    Set Linked_Users_CC = Range("D2:D31")
    Set Applications_CC = Range("H2:H31")
    Set New_Provisioned_CC = Range("L2:L31")
    Set Total_Accounts_Act_CC = Range("P2:P31")
    Set total_Spend_CC = Range("U2:U31")
    Set total_Transactions_CC = Range("V2:V31")
     
    Dim i As Long
    For i = 1 To DateR.Rows.Count
        If DateR.Cells(i, 1).Value <= Update_till Then
            Linked_Users_CC.Cells(i, 1).formula = "='Users Forecast'!R[0]C9"
            Applications_CC.Cells(i, 1).formula = "=VLOOKUP(R[0]C2,applications!C2:C3,2,FALSE))"
            New_Provisioned_CC.Cells(i, 1).formula = "=VLOOKUP(R[0]C2,new_accounts_provisioned!C1:C2,2,FALSE)"
            Total_Accounts_Act_CC.Cells(i, 1).formula = "=VLOOKUP(R[0]C2,total_cc_accounts!C1:C2,2,FALSE),P21)"
            total_Spend_CC.Cells(i, 1).formula = "=VLOOKUP(R[0]C2,total_transactions_and_spend!C1:C3,2,FALSE)"
            total_Transactions_CC.Cells(i, 1).formula = "=VLOOKUP(R[0]C2,total_transactions_and_spend!C1:C3,3,FALSE)"

            
        End If
    Next i
    
End Sub

错误原因排查 #

1. 公式语法错误：

   • Applications_CC的公式末尾多了一个右括号：=VLOOKUP(R[0]C2,applications!C2:C3,2,FALSE)) → 多余的)导致公式无效
   • Total_Accounts_Act_CC的公式存在语法混乱：=VLOOKUP(R[0]C2,total_cc_accounts!C1:C2,2,FALSE),P21) → 末尾的,P21)属于无效内容，破坏了VLOOKUP的语法结构

2. 行范围不匹配：
   DateR是B5:B32（共28行），而其他目标范围如Linked_Users_CC是D2:D31（共30行），循环时用i直接索引会导致日期行和目标填充行错位（比如DateR的第1行对应B5，而目标范围的第1行对应D2，逻辑上不匹配）

3. 未指定工作表：
   所有Range对象未明确指定所属工作表，默认使用当前活动工作表，若运行宏时活动表切换，会导致引用错误

修复后的代码 #

Sub CC_Update()
    Dim ws As Worksheet
    Dim DateR As Range
    Dim Update_till As Date
    
    Dim Linked_Users_CC As Range
    Dim Applications_CC As Range
    Dim New_Provisioned_CC As Range
    Dim Total_Accounts_Act_CC As Range
    Dim total_Spend_CC As Range
    Dim total_Transactions_CC As Range
    
    ' 指定数据所在工作表，替换为你的实际工作表名称
    Set ws = ThisWorkbook.Worksheets("YourSheetName")
    
    ' 设置范围，明确绑定到指定工作表
    Set DateR = ws.Range("B5:B32")
    Update_till = ThisWorkbook.Worksheets("Audit").Range("C1").Value
    
    ' 调整目标范围，使其与DateR的行号一一对应（B5对应D5，以此类推）
    Set Linked_Users_CC = ws.Range("D5:D32")
    Set Applications_CC = ws.Range("H5:H32")
    Set New_Provisioned_CC = ws.Range("L5:L32")
    Set Total_Accounts_Act_CC = ws.Range("P5:P32")
    Set total_Spend_CC = ws.Range("U5:U32")
    Set total_Transactions_CC = ws.Range("V5:V32")
     
    Dim i As Long
    For i = 1 To DateR.Rows.Count
        ' 检查单元格是否为有效日期，避免空值或非日期导致的错误
        If IsDate(DateR.Cells(i, 1).Value) And DateR.Cells(i, 1).Value <= Update_till Then
            Linked_Users_CC.Cells(i, 1).FormulaR1C1 = "='Users Forecast'!RC9"
            ' 修复多余的右括号
            Applications_CC.Cells(i, 1).FormulaR1C1 = "=VLOOKUP(RC2,applications!C2:C3,2,FALSE)"
            New_Provisioned_CC.Cells(i, 1).FormulaR1C1 = "=VLOOKUP(RC2,new_accounts_provisioned!C1:C2,2,FALSE)"
            ' 修复语法错误，移除多余的,P21)
            Total_Accounts_Act_CC.Cells(i, 1).FormulaR1C1 = "=VLOOKUP(RC2,total_cc_accounts!C1:C2,2,FALSE)"
            total_Spend_CC.Cells(i, 1).FormulaR1C1 = "=VLOOKUP(RC2,total_transactions_and_spend!C1:C3,2,FALSE)"
            total_Transactions_CC.Cells(i, 1).FormulaR1C1 = "=VLOOKUP(RC2,total_transactions_and_spend!C1:C3,3,FALSE)"
        End If
    Next i
End Sub

修改说明 #

• 修正了两处公式语法错误，移除多余括号和无效内容
• 调整目标范围的行号，使其与DateR的行完全对应（B5对应D5），避免错位
• 明确指定所有Range所属的工作表，防止活动表切换导致的引用错误
• 添加IsDate检查，避免非日期值或空值引发的比较错误
• 使用FormulaR1C1替代formula，更适合R1C1格式的公式书写

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