VBA使用Range和Cells更新其他工作表报运行时错误1004求助

核心原因 #

VBA中不显式指定父对象的前提下，Cells、Range 等单元格对象默认指向当前处于激活状态的工作表。你遇到的报错本质是Range的父工作表和构造Range所用的Cells的父工作表不匹配，系统无法识别合法范围，因此抛出运行时错误1004。

逐行代码运行逻辑说明 #

• 第一行可正常执行：Worksheets("Raw rates").Cells(1, 8).Value = 1
  已经显式指定Cells的父工作表为Raw rates，无对象匹配歧义。
• 第二行执行失败：Worksheets("Raw rates").Range(Cells(1, 8), Cells(1, 9)).Value = 1
Range的父对象是Raw rates表，但括号内的两个Cells没有指定工作表，默认指向当前激活的工作表，只要当前激活的不是Raw rates表，就会出现跨表构造范围的非法操作，触发报错。
• 第三行可正常执行：Range(Cells(1, 8).Offset(1, 1), Cells(1, 9).Offset(0, 0)).Value = 1
  代码中的Range和内部Cells都没有指定工作表，全部默认指向当前激活的工作表，父对象完全匹配，所以可以正常运行。
• 第四行执行失败：Worksheets("Raw rates").Range(Cells(1, 8).Offset(1, 1), Cells(1, 9).Offset(0, 0)).Value = 1
  触发逻辑和第二行完全一致，Range所属工作表和构造范围的Cells所属工作表不匹配。

修复方案 #

只需保证构造Range时用到的所有单元格对象的父工作表，和Range本身的父工作表保持一致即可，以下是两种常用的简洁写法：

方案1：工作表变量统一引用 #

Dim wsRaw As Worksheet
' 统一赋值目标工作表，后续直接复用变量即可
Set wsRaw = Worksheets("Raw rates")
' 修复原第二行
wsRaw.Range(wsRaw.Cells(1, 8), wsRaw.Cells(1, 9)).Value = 1
' 修复原第四行
wsRaw.Range(wsRaw.Cells(1, 8).Offset(1, 1), wsRaw.Cells(1, 9).Offset(0, 0)).Value = 1

方案2：With语句简化重复引用 #

With Worksheets("Raw rates")
    ' 注意Cells前的点号不能省略，点号代表继承With块的父对象
    .Range(.Cells(1, 8), .Cells(1, 9)).Value = 1
    .Range(.Cells(1, 8).Offset(1, 1), .Cells(1, 9).Offset(0, 0)).Value = 1
End With

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