VBA使用命名整数指定Range范围触发Run-time错误'1004'，求解决

错误原因 #

报错的核心问题是：代码中Cells(TargetRow, 5)和Cells(TargetRow, 6)没有明确指定归属的工作表，VBA会默认使用当前活动工作表。如果此时活动表不是BHLSTracker，就会出现跨工作表引用不匹配的情况，触发"对象'_Worksheet'的'Range'方法失败"错误。

解决方案 #

方案1：给Cells指定工作表前缀

确保Range和Cells都指向同一个工作表对象，修改最后一行代码：

Set MacroPage = Workbooks("Monthly Inventory Report 2.xlsm").Worksheets("Macro Page")
Set BHLSTracker = Workbooks("Monthly Inventory Report 2.xlsm").Worksheets("Blackhole & Lost Stock Tracker")

Dim DataDate As String: DataDate = MacroPage.Range("D4").Value
Dim TargetDate As Range, TargetRow As Integer
    Set TargetDate = BHLSTracker.Columns("A").Find(What:=CDate(DataDate), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows)
    If Not TargetDate Is Nothing Then TargetRow = TargetDate.Row
    
BHLSTracker.Range("E1:F1").Copy
' 给Cells添加BHLSTracker前缀，明确指定工作表
BHLSTracker.Range(BHLSTracker.Cells(TargetRow, 5), BHLSTracker.Cells(TargetRow, 6)).PasteSpecial xlPasteFormulas

方案2：用With语句简化代码（推荐）

通过With语句包裹目标工作表的操作，内部的Range和Cells会自动归属到该工作表，避免重复写工作表名：

Set MacroPage = Workbooks("Monthly Inventory Report 2.xlsm").Worksheets("Macro Page")
Set BHLSTracker = Workbooks("Monthly Inventory Report 2.xlsm").Worksheets("Blackhole & Lost Stock Tracker")

Dim DataDate As String: DataDate = MacroPage.Range("D4").Value
Dim TargetDate As Range, TargetRow As Integer
    Set TargetDate = BHLSTracker.Columns("A").Find(What:=CDate(DataDate), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows)
    If Not TargetDate Is Nothing Then TargetRow = TargetDate.Row
    
With BHLSTracker
    .Range("E1:F1").Copy
    .Range(.Cells(TargetRow, 5), .Cells(TargetRow, 6)).PasteSpecial xlPasteFormulas
End With

额外优化建议 #

1. 避免空值报错：如果Find未找到匹配日期，TargetRow会是初始值0，此时引用Cells(0,5)也会报错，建议增加判断：

   If Not TargetDate Is Nothing Then
       TargetRow = TargetDate.Row
       With BHLSTracker
           .Range("E1:F1").Copy
           .Range(.Cells(TargetRow, 5), .Cells(TargetRow, 6)).PasteSpecial xlPasteFormulas
       End With
   Else
       MsgBox "未找到对应日期的记录"
   End If
   

2. 数据类型优化：Integer的最大取值是32767，当工作表行数超过这个数值时会溢出，建议将TargetRow改为Long类型：Dim TargetRow As Long

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