为什么范围有效，但单元格无效？ [英] Why does Range work, but not Cells?

问题描述

我正在尝试通过将值从一个范围分配到另一个来将某些数据从一个工作簿移动到另一工作簿。当我使用普通的Range语法指定目标范围（Range（ A1：B2））时，我的代码可以运行，但是如果我尝试使用Range，Cells语法（Range（Cells（1,1），Cells（2 ，2）））我的代码不起作用。

I'm trying to move some data from one workbook into another by assigning the values from one range to another. When I use the normal Range syntax to specify the destination range (Range("A1:B2")) my code works, but if I try to use the Range, Cells syntax (Range(Cells(1,1),Cells(2,2))) my code doesn't work.

我激活目标工作簿（ActiveWorkbook），并使代码在源工作簿（ThisWorkbook）中运行。

I activate the destination workbook (ActiveWorkbook) and have the code running in the source workbook (ThisWorkbook).

此代码有效：

ActiveWorkbook.Worksheets(1).Range("A1:B2").Value _
= ThisWorkbook.Worksheets(1).Range(Cells(1, 1), Cells(2, 2)).Value

但是此代码不会：

ActiveWorkbook.Worksheets(1).Range(Cells(1, 1), Cells(2, 2)).Value _
= ThisWorkbook.Worksheets(1).Range(Cells(1, 1), Cells(2, 2)).Value

我得到的错误是运行时错误'1004'：应用定义或对象定义的错误。

The error I get is Run-time error '1004': Applicaton-defined or object-defined error.

有人知道为什么使用cells对象会导致我出现问题，或者是否存在其他我不知道的问题吗？ f？

Does anyone know why using the cells object is causing me problems, or if there is some other problem I'm not aware of?

推荐答案

问题是 Cells 不合格，这意味着这些单元格引用的工作表因代码所在位置而异。任何时候调用 Range 或 Cells 或 Rows 或 UsedRange 或返回Range对象的任何内容，而您不指定它在哪个工作表上，则工作表将根据以下条件进行分配：

The problem is that Cells is unqualified, which means that the sheet to which those cells refer is different depending on where your code is. Any time you call Range or Cells or Rows or UsedRange or anything that returns a Range object, and you don't specify which sheet it's on, the sheet gets assigned according to:

• 在工作表的类模块中：该工作表不管活动是什么


• 在其他任何模块中：ActiveSheet

您可以限定范围的引用，但可以限定 Cells 的引用不合格，可能指向Activesheet。就像写作

You qualify the Range reference, but the Cells reference is unqualified and is likely pointing to the Activesheet. It's like writing

ThisWorkbook.Worksheets(1).Range(ActiveSheet.Cells(1, 1), ActiveSheetCells(2, 2)).Value

除非ThisWorkbook.Worksheets（1）发生，否则这当然没有任何意义活跃起来。我经常喜欢使用With块，以确保所有内容都完全合格。

which of course doesn't make any sense unless ThisWorkbook.Worksheets(1) happens to be active. I often like to use a With block so that I make sure everything is fully qualified.

With Sheets(1)
    .Range(.Cells(1,1), .Cells(2,2)).Value = "something"
End With

但是您指的是两个不同的表，因此最好使用短表变量，例如：

But you refer to two different sheets, so you'll be better off using short sheet variables like:

Dim shSource As Worksheet
Dim shDest As Worksheet

Set shSource = ThisWorkbook.Worksheets(1)
Set shDest = Workbooks("myBook").Worksheets(1)

shDest.Range(shDest.Cells(1, 1), shDest.Cells(2, 2)).Value = _
    shSource.Range(shSource.Cells(1, 1), shSource.Cells(2, 2)).Value

但实际上，如果您要对 Cells进行硬编码参数，您可以像

But really, if you're going to hardcode the Cells arguments, you could clean that up like

shDest.Cells(1, 1).Resize(2, 2).Value = shSource.Cells(1, 1).Resize(2, 2).Value

这篇关于为什么范围有效，但单元格无效？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！