VBA#1004使用Range(Cells(，)，Cells(，)) [英] VBA #1004 using Range(Cells(,),Cells(,))

问题描述

运行以下代码时出现错误#1004:

I get an Error #1004 when running the following code:

Sub Test()
      Worksheets("Snapshot").Range(Cells(1, 1), Cells(10, 2)).Copy _
      Destination:=Worksheets("Snapshot2").Range(Cells(1, 1), Cells(10, 2))
End Sub

但是当我更改为这个

Sub Test()
      Worksheets("Snapshot").Range("A1:B10").Copy _
      Destination:=Worksheets("Snapshot2").Range("A1:B10")
End Sub

它工作正常.

是否存在使用Cells (，)运行此代码的解决方案?还是我必须构建一个显示(:")语句的字符串?

Is there a solution to run this code using Cells( , )? Or do I have to build a String that shows the (" : ") statement?

推荐答案

不合格时，单元格默认情况下引用当前活动工作表的单元格.之所以会出现此错误，是因为您正在工作表"Snapshot"(或"Snapshot2")中查找其他工作表(当前处于活动状态)中的单元格.

When unqualified, Cells refers by default to the cells of the currently active sheet. You're getting that error because you're looking in sheet "Snapshot" (or "Snapshot2") for cells that are in a different sheet (the currently active one).

您可以完全限定 Cells ，最好使用变量:

You can either fully qualify Cells, preferably using variables:

Dim sht1 As Worksheet
Dim sht2 As Worksheet
Set sht1 = Worksheets("Snapshot")
Set sht2 = Worksheets("Snapshot2")
sht1.Range(sht1.Cells(1, 1), sht1.Cells(10, 2)).Copy _
    Destination:=sht2.Range(sht2.Cells(1, 1), sht2.Cells(10, 2))

或者，我更喜欢这样做，请使用 Resize :

Or, and I prefer this, use Resize:

Dim rangeHeight As Long
Dim rangeWidth As Long
rangeHeight = 10
rangeWidth = 2
Worksheets("Snapshot").Cells(1, 1).Resize(rangeHeight, rangeWidth).Copy _
    Destination:=Worksheets("Snapshot2").Cells(1, 1).Resize(rangeHeight, rangeWidth)    

这篇关于VBA#1004使用Range(Cells(，)，Cells(，))的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！