Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

excel - Run-time error '1004' when using fully qualified cells in range object

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?

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

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

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Range expects a string or TWO cells to define the 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

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

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

Also skip the dual lines:

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

Copy takes an argument of the destination.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...