尝试复制区域时出现运行时错误“1004”（Excel VBA）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4378239/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Run-time error '1004' when trying to copy a range (Excel VBA)
提问by rryanp
I am trying to copy a range of cells for which I don't know the last row and column (though I can easily get to those using variables). Unfortunately though, the way I am trying to reference a range is giving me a run-time error 1004 (Application-defined or object-defined error) when I use variables, and I can't figure out why. Below is a sample of the code:
我正在尝试复制一系列我不知道最后一行和最后一列的单元格（尽管我可以使用变量轻松获取那些单元格）。不幸的是，当我使用变量时，我尝试引用范围的方式给了我一个运行时错误 1004（应用程序定义或对象定义的错误），我不知道为什么。下面是代码示例：
Dim wkbk As Workbook
Dim copy_rng As Range
...
Set copy_rng = wkbk.Worksheets("Payable").Range("A1:Y3500")
Set copy_rng = wkbk.Worksheets("Payable").Range(Cells(1, 1), Cells(last_row_pay, last_col_pay))
The first Set statement is simply an example, and it works fine (so I know wkbk is properly defined and it's finding the "Payable" worksheet). Does anybody know why the second Set statement would not work? Is there a syntax issue? (During debug, if I hover over the last_row_pay and last_col_pay variables, I can see valid values--1533 and 25 respectively.) Thanks for any help.
第一个 Set 语句只是一个示例，它工作正常（所以我知道 wkbk 已正确定义并且它正在查找“应付账款”工作表）。有人知道为什么第二个 Set 语句不起作用吗？是否有语法问题？（在调试期间，如果我将鼠标悬停在 last_row_pay 和 last_col_pay 变量上，我可以分别看到有效值--1533 和 25。）感谢您的帮助。
回答by ja72
Use the resize function, because the Cells()function works for the active worksheet and it might get confused.
使用调整大小功能，因为该Cells()功能适用于活动工作表，它可能会混淆。
Dim wkbk As Workbook
Dim copy_rng As Range
Dim pay_rows As Integer ,pay_columns As Integer
...
pay_rows = 3500
pay_columns = 23
Set copy_rng = wkbk.Worksheets("Payable").Range("A1").Resize(pay_rows,pay_columns)
where A1is the top left cell of your data.
A1数据的左上角单元格在哪里。
回答by Alex P
If the range that you are trying to copy is a contiguous set of cells then I find the easiest way to get the size of the range without hard coding any row / column counts is to use CurrentRegion
如果您尝试复制的范围是一组连续的单元格，那么我发现无需硬编码任何行/列计数即可获得范围大小的最简单方法是使用 CurrentRegion
Sub GetCurrentRange
Dim rng as range
Set rng = Worksheets("Payable").Range("A1").CurrentRegion
End Sub
The advantage here is that even if you add rows / columns to your dataset you don't need to bother working out the new column and row limits as CurrentRegiondoes this for you
这里的好处是，即使你添加行/列到你的数据集，你不必理会制定新的列和行限制为CurrentRegion做到这一点你
Example:
例子：
A B C
1 10 20 30
2 40 50 60
3 70 80 90
Sub GetCurrentRange
Dim rng as range
Set rng = Worksheets("Payable").Range("A1").CurrentRegion
Debug.Print rng.Address //Prints $A:$C
End Sub
回答by user2978925
with Worksheets("Payable")
copy_rng = .Range(.Cells(1, 1), .Cells(last_row_pay, last_col_pay)).Value
end with
more lines, but work..
更多的行，但工作..
回答by bs0d
I had the same issues. You need to qualify both cell and range properties.
我有同样的问题。您需要限定单元格和范围属性。
Set copy_rng = wkbk.Worksheets("Payable").Range(Cells(1, 1), wkbk.Sheets("Payable").Cells(last_row_pay, last_col_pay))
Set copy_rng = wkbk.Worksheets("Payable").Range(Cells(1, 1), wkbk.Sheets("Payable").Cells(last_row_pay, last_col_pay))
