使用Cells.Range在Excel中插入VLOOKUP公式报错求助

嘿，这个错误我之前写VBA操作公式的时候也踩过坑，咱们来一步步拆解你的代码问题：

代码里的几个关键问题 #

• 未指定工作表的行数计算：你先计算了lRow = Cells(Rows.Count, 1).End(xlUp).Row，但这时候是基于当前激活的工作表，而不是你后续要操作的Consolidator表。如果运行代码时当前激活的不是这个表，lRow会拿到错误的行数，后续赋值时就会触发对象错误。
• 错误的公式赋值方式：你用.Value来设置R1C1格式的公式，Excel无法正确解析这种格式的公式到单元格里，应该用.FormulaR1C1属性来专门处理R1C1格式的公式。
• 不明确的Range引用：Cells.Range(Cells(5,4), Cells(lRow,4))这种写法没有指定所属工作表，虽然你之前激活了Consolidator，但激活工作表是不稳定的操作（比如用户切换表就会出错），最好直接绑定工作表对象。

修正后的代码 #

Sub WriteFormulasInSheet()
    Dim wsConsol As Worksheet
    Dim lRow As Long
    
    ' 直接绑定目标工作表，避免激活操作的不稳定
    Set wsConsol = ThisWorkbook.Sheets("Consolidator")
    
    ' 计算Consolidator表A列的最后一行（明确指定工作表）
    lRow = wsConsol.Cells(wsConsol.Rows.Count, 1).End(xlUp).Row
    
    ' 先判断是否有足够的数据行，避免lRow<5时Range报错
    If lRow >= 5 Then
        ' 用FormulaR1C1设置R1C1格式的公式，MAP!$N:$P对应R1C1的MAP!C14:C16
        wsConsol.Range(wsConsol.Cells(5, 4), wsConsol.Cells(lRow, 4)).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-3],MAP!C14:C16,2,0),"""")"
    Else
        MsgBox "Consolidator表中没有足够的数据行（需要至少5行）"
    End If
End Sub

额外排查点 #

• 确认MAP工作表确实存在于当前工作簿中，工作表名拼写完全一致（Excel不区分大小写，但最好严格匹配）。
• 检查MAP表的N:P列是否有VLOOKUP需要的数据，确保查找值能在N列匹配到。

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