在范围对象中使用完全限定的单元格时出现运行时错误'1004' [英] Run-time error '1004' when using fully qualified cells in range object

问题描述

我正在尝试使用range对象的cells属性从一个工作簿中复制值并将其粘贴到另一个工作簿中.如何正确声明范围引用，以免收到"1004"运行时错误?

I am trying to copy values from one workbook and paste them into another using the cells property of the range object. How do I properly state range references so that I am not receiving a '1004' runtime error?

我正在使用Excel 2013，并且正在具有活动工作表的"Practicebook"工作簿中运行代码.

I'm working on Excel 2013, and I am running the code in "Practicebook" workbook with an active worksheet.

我已经研究了许多类似的问题，例如范围内的运行时错误1004(Cells())，但他们并没有帮助我.我已经确保我的推荐人完全合格.

I've researched many similar problems like this one, Run time error 1004 in Range(Cells()), but they have not helped me. I've made sure my references are fully qualified.

Sub Transfer()

    Dim wsCopy As Worksheet
    Dim wsDest As Worksheet

    Set wsCopy = Workbooks("Practicebook2").Worksheets("Sheet1")
    Set wsDest = Workbooks("Practicebook").ActiveSheet
    wsCopy.Range(wsCopy.Cells(2,8)).Copy
    wsDest.Range("J5").PasteSpecial

End Sub

运行代码时，出现错误消息对象'_Worksheet'的方法'范围'失败"，调试器突出显示了下面粘贴的第六行代码.当更改对A1样式符号的引用时，代码将按预期在目标表中生成粘贴的值.

When I run the code, error message "Method 'Range' of object'_Worksheet' failed" appears, with the 6th line of code pasted below highlighted by the debugger. When changing the reference to A1 style notation, the code produces the pasted value in the destination sheet as expected.

推荐答案

范围需要一个字符串或两个单元格来定义范围.

Range expects a string or TWO cells to define the range.

仅使用一个CELL删除RANGE:

With only one CELL remove the RANGE:

wsCopy.Cells(2,8).Copy

使用两个单元格时，将是这样的:

When using two cells it would be something like this:

wsCopy.Range(wsCopy.Cells(2,8),wsCopy.Cells(4,10)).Copy

如果相反，您在单元格中具有所需的字符串范围，则需要将 .Value 附加到 Cells()

If instead you have the range desired as a string in the the cell one would need to append .Value to the Cells()

wsCopy.Range(wsCopy.Cells(2,8).Value).Copy

现在，它将以该单元格中的值作为字符串，并将其传递给Range.

Now it will take the value in that cell as a string an pass it to the Range.

也跳过双行:

wsCopy.Cells(2,8).Copy wsDest.Range("J5")

复制接受目标的参数.

这篇关于在范围对象中使用完全限定的单元格时出现运行时错误'1004'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！