Excel VBA中的范围方法 [英] Range method in Excel VBA
问题描述
我知道这是一个愚蠢的问题,但我终于试图弄清楚。
I know this is a stupid question, but I'm at wit's end trying to figure it out.
这个小代码片段导致运行时错误'1004':对象_Worksheet的方法范围失败。但是仅当Sheet3不是活动工作表时。当Sheet3处于活动状态时,会执行预期的操作。
This little snippet of code is causing a Run-time error '1004': Method 'Range' of object '_Worksheet' failed. But only when Sheet3 is not the active sheet. It does what's expected when Sheet3 is active.
With Sheet3.Range(Cells(1, 1), Cells(nrows1, ncols1))
.Clear
.Value = SCDevNeeds1
End With
此外,如果我更改对工作表的选项卡名称的引用,我仍然会收到运行时错误,但这次是应用程序定义或对象定义的错误。这也只发生在工作表不活动时:
Furthermore, if I change the reference to the sheet's tab name, I still get a run-time error, but this time it's an Application-defined or Object-defined error. This ALSO happens only when the sheet is not active:
With Sheets("newest").Range(Cells(1, 1), Cells(nrows1, ncols1))
.Clear
.Value = SCDevNeeds1
End With
我缺少什么?
推荐答案
单元格$ c $表达式
Sheet3.Range(Cells(1,1),Cells(nrows1,ncols1))
指的是:
a)活动表单如果代码是单独的代码模块
b)模块的工作表如果代码在工作表模块中。
Cells
in the expression Sheet3.Range(Cells(1, 1), Cells(nrows1, ncols1))
refers to:
a) the active sheet if the code is a seperate code module
b) the module's sheet if the code is in a worksheet-module.
所以代码将工作它是在Sheet3模块内部,而是意外。
您应该始终使用所有范围
对象(如)的完整引用单元格
,范围
,行
,列
,...)。
So the code would work if it's inside the Sheet3-module, but rather by accident.
You should always use full references to all Range
objects (like Cells
, Range
, Rows
, Columns
, ...).
Sheet3.Range(Sheet3.Cells(1,1),Sheet3.Cells(nrows1,ncols1 ))
这篇关于Excel VBA中的范围方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!