仅当从引用的工作表中调用时，范围才有效 [英] Range Only Works When Called from the Referenced Worksheet

问题描述

我为宏分配了两个按钮，每个按钮都位于不同的工作表上:打印页面1"和优化".宏从第一个获取信息，然后知道第二次复制一个范围的次数.

I've got a macro assigned to two buttons, each on a different worksheet: "Print Page 1" and "Optimization". The macro takes information from the first to know how many times to copy a range the second.

在优化"处于活动状态时调用该代码可以正常工作，但是在打印页面1"工作表处于活动状态时调用该代码，则在该行上生成"1004错误:应用程序定义或对象定义的错误"复制范围.见下文:

When called while "Optimization" is active, the code works perfectly, but when called with the "Print Page 1" worksheet active, a "1004 error: Application-defined or object-defined error" is produced on the line that copies the ranges. See below:

Sub Range_Tester()

Dim PrintRow, WallQty

WallQty = Worksheets("Print Page 1").Cells(24, 4)
PrintRow = 20

While WallQty > 1
    Worksheets("Optimization").Range(Cells(PrintRow - 1, 2), Cells(PrintRow - 1, 4)).Copy Worksheets("Optimization").Range(Cells(PrintRow, 2), Cells(PrintRow, 4))
    WallQty = WallQty - 1
    PrintRow = PrintRow + 1
Wend

End Sub

我认为问题是我的范围语法；如果我替换

I think the problem is my range syntax; if I replace

Worksheets("Optimization").Range(Cells(PrintRow - 1, 2), Cells(PrintRow - 1, 4)).Copy Worksheets("Optimization").Range(Cells(PrintRow, 2), Cells(PrintRow, 4))

使用

Worksheets("Optimization").Cells(PrintRow - 1, 2).Copy Worksheets("Optimization").Cells(PrintRow, 2)
Worksheets("Optimization").Cells(PrintRow - 1, 3).Copy Worksheets("Optimization").Cells(PrintRow, 3)
Worksheets("Optimization").Cells(PrintRow - 1, 4).Copy Worksheets("Optimization").Cells(PrintRow, 4)

无论哪个工作表处于活动状态，信息都可以完美复制.

The info copies flawlessly regardless of which worksheet is active.

我很抱歉问到一个可能是一个愚蠢的简单问题，我只是没有运气找到正确定义可更改位置的范围的信息.

My apologies for asking what is probably a stupidly simple question, I just haven't had any luck finding info on properly defining ranges that change locations.

推荐答案

Cells不合格，隐式引用ActiveSheet，因为该代码未写在工作表模块中.如果是，那么不合格的Cells就像Me.Cells. ActiveSheet.Cells.

Cells being unqualified, implicitly refers to ActiveSheet, because that code isn't written in a worksheet module. If it was, then unqualified Cells would be like Me.Cells. Anywhere else, it's ActiveSheet.Cells.

始终使用适当的Worksheet对象明确限定Worksheet成员调用，您将避免此问题.

Always explicitly qualify Worksheet member calls with a proper Worksheet object, and you'll avoid this problem.

Worksheets("Optimization").Range(Cells(PrintRow - 1, 2), Cells(PrintRow - 1, 4)).Copy Worksheets("Optimization").Range(Cells(PrintRow, 2), Cells(PrintRow, 4))

在这里，您要针对Worksheets("Optimization")调用.Range，然后使用Cells(PrintRow - 1, 2)参数化它，该可能属于另一个工作表(无论ActiveSheet是什么).当它发生时，事情就炸了.

Here, you're invoking .Range against Worksheets("Optimization"), but then parameterizing it with Cells(PrintRow - 1, 2), which may belong to another sheet (whatever ActiveSheet is). When it does, things blow up.

将Worksheets("Optimization")引用捕获到With块变量(或普通的旧局部变量)中:

Capture the Worksheets("Optimization") reference into a With block variable (or a plain old local variable):

With Worksheets("Optimization")
    .Range(.Cells(PrintRow - 1, 2), .Cells(PrintRow - 1, 4)).Copy .Range(.Cells(PrintRow, 2), .Cells(PrintRow, 4))
End With

请注意.取消引用运算符，该操作使.Cells像.Range调用那样在Worksheets("Optimization")上工作，确保所有内容都从同一张纸中提取.

Notice the . dereferencing operator, which makes .Cells work off Worksheets("Optimization") like the .Range call, ensuring everything is pulled from the same sheet.

这篇关于仅当从引用的工作表中调用时，范围才有效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！