VBA宏运行时错误1004：Worksheet的Range方法调用失败

问题场景 #

我有两列数据：P列是单元格引用列表，Q列是对应文本内容。尝试通过宏将Q列文本写入P列指定的单元格，但代码执行到ws.Range(cellRef).Value = nameCell.Value时抛出错误：

Run-time error 1004, Method 'Range' of object '_Worksheet' failed

已尝试添加合法格式校验、遇到空单元格退出循环，但问题仍未解决。

原VBA代码 #

Sub OverwriteCellReferencesWithNames()
    Dim ws As Worksheet
    Dim cell As Range
    Dim nameCell As Range
    Dim cellRef As String
    
    ' Set worksheet reference to Sheet1
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Clear the contents of the specified ranges
    ws.Range("B4:F8").ClearContents
    ws.Range("B11:F15").ClearContents
    
    ' Loop through each cell in column P
    For Each cell In ws.Range("P2:P" & ws.Cells(ws.Rows.Count, "P").End(xlUp).Row)
        ' Get the cell reference from column P
        
        cellRef = cell.Value
        
        ' Find the corresponding name in column Q
        Set nameCell = ws.Cells(cell.Row, "Q")
        
        ' If a corresponding name is found in column Q, overwrite the cell reference in column P with the name

        If Not nameCell Is Nothing Then
            ws.Range(cellRef).Value = nameCell.Value
        End If
    Next cell
End Sub

问题分析与解决方案 #

核心问题 #

1. 无效条件判断：If Not nameCell Is Nothing永远为真，因为nameCell是通过ws.Cells(cell.Row, "Q")直接定义的，不可能为Nothing，无法过滤空值或非法情况。
2. 未校验引用合法性：cellRef可能是空值、格式错误，或者指向其他工作表/工作簿，直接用ws.Range(cellRef)会触发1004错误。
3. 缺乏错误捕获：单个非法引用会导致整个宏中断，无法继续处理后续有效数据。

修改后的代码 #

Sub OverwriteCellReferencesWithNames()
    Dim ws As Worksheet
    Dim cell As Range
    Dim cellRef As String
    Dim targetRange As Range
    
    ' Set worksheet reference to Sheet1
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Clear target ranges
    ws.Range("B4:F8").ClearContents
    ws.Range("B11:F15").ClearContents
    
    ' Loop through non-empty cells in column P starting from P2
    For Each cell In ws.Range("P2:P" & ws.Cells(ws.Rows.Count, "P").End(xlUp).Row)
        cellRef = Trim(cell.Value)
        
        ' Skip empty cell references (exit loop, adjust to 'Continue' if needed)
        If cellRef = "" Then Exit For
        
        ' Validate and get target range, support cross-sheet/workbook references
        On Error Resume Next
        Set targetRange = Range(cellRef)
        On Error GoTo 0
        
        ' Only write if target range is valid and Q column has content
        If Not targetRange Is Nothing And ws.Cells(cell.Row, "Q").Value <> "" Then
            targetRange.Value = ws.Cells(cell.Row, "Q").Value
        End If
        
        ' Reset target range for next iteration
        Set targetRange = Nothing
    Next cell
End Sub

关键改进点 #

• 空值处理：通过Trim(cellRef) = ""判断空引用，直接退出循环（可根据需求改为跳过）。
• 合法性校验：用On Error Resume Next捕获引用错误，确保targetRange有效时才执行写入。
• 支持跨表引用：改用Range(cellRef)替代ws.Range(cellRef)，如果P列引用包含工作表名称（如Sheet2!A1）也能正常识别。
• 非空内容判断：仅当Q列对应单元格有内容时才执行写入，避免覆盖为空白。

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