Excel VBA代码模块运行正常，绑定按钮触发时出现运行时错误1004求助

问题描述 #

VBA代码在模块中单独运行正常，但绑定到按钮点击触发时，抛出运行时错误1004（应用程序定义或对象定义错误）。

原代码如下：

Sub Parent_Name()

'从第1列更新父级名称的公式

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("DB")

    Sheets("DB").Select
            
    last_row = Cells(Rows.Count, 1).End(xlUp).Row

    Range("Y2").Select
    ActiveCell.Formula = "=VLOOKUP(X2,'ML DB'!C:G,5,0)"

    ActiveCell.AutoFill Destination:=Range(ActiveCell.Address & ":Y" & last_row)


    Columns("Y:Y").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            
    '将#N/A替换为##
            
    Columns("Y:Y").Select
    Selection.Replace What:="#N/A", Replacement:="##", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Range("Y1").Select

Sheets("DB").Select

last_row = Cells(Rows.Count, 1).End(xlUp).Row

    For iRow = 2 To last_row

        If Range("Y" & iRow).Value = "##" Then

' 点击按钮时，以下代码行触发运行时错误1004
        ws.Range("Y" & iRow).Formula = Replace(Range("Y" & iRow), "##", "=VLOOKUP(X" & iRow & "'ML DB'!D:G,4,0")
        

        End If

    Next iRow


    Columns("Y:Y").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            
    '将#N/A替换为##
            
    Columns("Y:Y").Select
    Selection.Replace What:="#N/A", Replacement:="##", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

Range("Y1").Select

End Sub

错误原因 #

1. 公式语法错误：触发错误的代码行中，构造的VLOOKUP公式缺少查找值与查找区域之间的逗号，正确格式应为=VLOOKUP(XiRow,'ML DB'!D:G,4,0)，原代码写成了=VLOOKUP(XiRow'ML DB'!D:G,4,0)，导致公式无效。
2. Replace函数误用：此时Range("Y" & iRow)的值是"##"，Replace操作完全多余，直接赋值正确公式即可。
3. 依赖Select/Selection的风险：大量使用Select和Selection会依赖当前激活的工作表，按钮触发时若当前工作表不是"DB"，会导致对象引用错误。

修复后的完整代码 #

Option Explicit ' 强制声明所有变量，避免隐式错误

Sub Parent_Name()
    '从第1列更新父级名称的公式
    Dim ws As Worksheet
    Dim last_row As Long
    Dim iRow As Long
    
    Set ws = ThisWorkbook.Sheets("DB")
    
    ' 获取最后一行行号
    last_row = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' 批量设置VLOOKUP公式并转为值
    ws.Range("Y2:Y" & last_row).Formula = "=VLOOKUP(X2,'ML DB'!C:G,5,0)"
    ws.Range("Y2:Y" & last_row).Value = ws.Range("Y2:Y" & last_row).Value
    
    ' 将#N/A替换为##
    ws.Columns("Y:Y").Replace What:="#N/A", Replacement:="##", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    
    ' 重新获取最后一行（防止数据变化）
    last_row = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' 处理标记为##的单元格，重新设置VLOOKUP公式
    For iRow = 2 To last_row
        If ws.Range("Y" & iRow).Value = "##" Then
            ' 修正公式语法，补充逗号，直接赋值公式
            ws.Range("Y" & iRow).Formula = "=VLOOKUP(X" & iRow & ",'ML DB'!D:G,4,0)"
        End If
    Next iRow
    
    ' 再次转为值并替换#N/A
    ws.Range("Y2:Y" & last_row).Value = ws.Range("Y2:Y" & last_row).Value
    ws.Columns("Y:Y").Replace What:="#N/A", Replacement:="##", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    
    ' 定位到Y1单元格（可选）
    ws.Range("Y1").Select
End Sub

修复说明 #

• 增加Option Explicit强制变量声明，避免隐式类型错误。
• 全程使用ws对象引用工作表单元格，彻底摆脱对Select/Selection的依赖，避免激活状态导致的错误。
• 修正VLOOKUP公式的语法错误，补充查找值与区域之间的逗号。
• 简化批量操作，直接对整列范围设置公式和转为值，提升代码效率。
• 移除多余的Replace操作，直接为目标单元格赋值正确公式。

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