VBA中向公式插入变量时出现对象定义错误求助

问题根源 #

你的代码存在3个关键错误导致报错：

• 变量拼写错误：输入框语句里的newTow是笔误，正确变量名应为newRow，未定义的变量会触发对象定义错误
• 字符串引号转义错误：
  • TEXTJOIN的分隔符", "在VBA字符串中需要用双引号转义，需写成""", """
  • 变量Trainer是文本值，插入公式时必须用双引号包裹，否则Excel会将其识别为单元格引用而非文本内容
• 公式符号错误：VBA的Formula属性无需使用HTML转义符<，直接写<即可

修正后的代码 #

Sub test()
  Dim ws As Worksheet
  Dim lastRow As Long
  Dim newRow As Long
    
  ' 指定目标工作表
  Set ws = ThisWorkbook.Sheets("Skill Matrix")
    
  ' 查找A列最后一行数据
  lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
  ' 确定下一个可写入的空行
  newRow = IIf(lastRow = 1 And ws.Cells(lastRow, 1).Value = "", 1, lastRow + 1)

  Dim Trainer As String
  ' 修正变量拼写错误：newTow → newRow
  Trainer = InputBox("Enter trainer for Row " & newRow & ":")

  With ws
    ' 修正引号转义、Trainer变量包裹、小于号转义问题
    .Cells(newRow, 13).Formula = "=IFERROR(TEXTJOIN("""", "", TRUE, """ & Trainer & """, IF($P" & newRow & ":$XFD" & newRow & "=5, IF($P$5:$XFD$5<>"""",$P$5:$XFD$5, """"),""""), """"))"
  End With
End Sub

优化建议 #

如果要降低公式字符串拼接的出错概率，可以将公式拆分为多段拼接：

Dim formulaHead As String
Dim formulaTail As String
Dim fullFormula As String

formulaHead = "=IFERROR(TEXTJOIN("""", "", TRUE, """
formulaTail = """, IF($P" & newRow & ":$XFD" & newRow & "=5, IF($P$5:$XFD$5<>"""",$P$5:$XFD$5, """"),""""), """"))"
fullFormula = formulaHead & Trainer & formulaTail

.Cells(newRow, 13).Formula = fullFormula

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