vba 应用程序定义或对象定义错误 1004
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11288798/
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
Application-defined or object-defined error 1004
提问by speci
VBA is throwing the above given error on the line Sheets("Sheet1").Range("A" & i).Copy Destination:=Sheets("Sheet2").Range("A" & i & "A" & LastCol - 1)
VBA 在行上抛出上述给定的错误 Sheets("Sheet1").Range("A" & i).Copy Destination:=Sheets("Sheet2").Range("A" & i & "A" & LastCol - 1)
What I am trying to do is actually to copy the "A" & icell (in first iteration it's A2) to a range in the second worksheet named Sheet2.
我想要做的实际上是将"A" & i单元格（在第一次迭代中A2）复制到名为 Sheet2 的第二个工作表中的范围。
Sub FindFill()
Dim DatesRange As Range
Dim i As Integer
Dim TransposeThis As Range
Dim LastCol As Integer
If WorksheetFunction.CountA(Cells) > 0 Then
LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
End If
With Sheets("Sheet1")
Set DatesRange = Range("B2" & LastCol)
End With
i = 1
Do While i <= ActiveSheet.Rows.Count
Sheets("Sheet1").Range("A" & i + 1).Copy Destination:=Sheets("Sheet2").Range("A" & i & "A" & LastCol - 1)
i = i + 1
Loop
End
End Sub
回答by Siddharth Rout
You are missing a ":" before "A"
您在“A”之前缺少“：”
Range("A" & i & ":A" & LastCol - 1)
FOLLOWUP
跟进
After I went through your comments, I saw lot of errors in your code
看完你的评论后，我发现你的代码中有很多错误
1)You have dimmed ias Integer. This can give you an error in Excel 2007 onwards if your last row is beyond 32,767. Change it to LongI would recommend having a look at this link.
1)您已变暗i为Integer。如果您的最后一行超过 32,767，这可能会给您在 Excel 2007 以后的错误。将其更改为Long我建议查看此链接。
Topic: The Integer, Long, and Byte Data Types
主题：整数、长整型和字节数据类型
Link: http://msdn.microsoft.com/en-us/library/aa164754%28v=office.10%29.aspx
链接：http: //msdn.microsoft.com/en-us/library/aa164754%28v=office.10%29.aspx
Quote from the above link
从上面的链接引用
Integer variables can hold values between -32,768 and 32,767, while Long variables can range from -2,147,483,648 to 2,147,483,647
整数变量可以保存 -32,768 到 32,767 之间的值，而 Long 变量的范围可以从 -2,147,483,648 到 2,147,483,647
2)You are finding the Last Column But in Which Sheet? You have to fully qualify the path Like this.
2)您正在查找最后一列，但在哪个工作表中？你必须像这样完全限定路径。
If WorksheetFunction.CountA(Sheets("Sheet1").Cells) > 0 Then
LastCol = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
End If
Same is the case with
同样是这种情况
With Sheets("Sheet1")
Set DatesRange = Range("B2" & LastCol)
End With
You are missing a DOT before Range
您之前缺少一个 DOT Range
This is the correct way...
这是正确的方法...
.Range("B2....
Also Range("B2" & LastCol)will not give you the range that you want. See the code below on how to create your range.
另外Range("B2" & LastCol)，你要不会给你的范围内。有关如何创建范围的信息，请参阅下面的代码。
3)You are using a variable LastColumnbut using LastCol. I would strongly advise using Option ExplicitI would also recommend having a look at this link (SEE POINT 2 in the link).
3）您正在使用一个变量，LastColumn但使用LastCol. 我强烈建议使用Option Explicit我还建议查看此链接（请参阅链接中的第 2 点）。
Topic: To ‘Err' is Human
话题: 'Err' 是人类
Link: http://www.siddharthrout.com/2011/08/01/to-err-is-human/
链接：http: //www.siddharthrout.com/2011/08/01/to-err-is-human/
4)What happens if there .CountA(Sheets("Sheet1").Cells) = 0? :) I would suggest you this code instead
4)如果有会发生什么.CountA(Sheets("Sheet1").Cells) = 0？:) 我建议你改用这个代码
If WorksheetFunction.CountA(Sheets("Sheet1").Cells) > 0 Then
LastCol = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
Else
MsgBox "No Data Found"
Exit Sub
End If
5)ActiveSheet.Rows.Countwill not give you the last active row. It will give you the total number of rows in that sheet. I would recommend getting the last row of Col A which has data.
5）ActiveSheet.Rows.Count不会给你最后一个活动行。它将为您提供该工作表中的总行数。我建议获取包含数据的 Col A 的最后一行。
You can use this for that
你可以用这个
With Sheets("Sheet")
LastRow =.Range("A" & .Rows.Count).End(xlup).row
End With
Now use LastRowinstead of ActiveSheet.Rows.CountYou also might want to use a For Loopso that you don't have to increment ievery time. For example
现在使用LastRow而不是ActiveSheet.Rows.Count您可能还想使用 aFor Loop以便您不必i每次都增加。例如
For i = 1 to LastRow
6)Finally You should never use End. Reason is quite simple. It's like Switching your Computer using the POWER OFF button. The End statement stops code execution abruptly, without invoking the Unload, QueryUnload, or Terminate event, or any other Visual Basic code. Also the Object references held (if any) by other programs are invalidated.
6)最后你不应该使用End. 原因很简单。这就像使用电源关闭按钮切换您的计算机。End 语句会突然停止代码执行，而不调用 Unload、QueryUnload 或 Terminate 事件或任何其他 Visual Basic 代码。其他程序持有的对象引用（如果有的话）也会失效。
7)Based on your image in Chat, I believe you are trying to do this? This uses a code which doesn't use any loops.
7)根据您在 Chat 中的形象，我相信您正在尝试这样做？这使用不使用任何循环的代码。
Option Explicit
Sub FindFill()
Dim wsI As Worksheet, wsO As Worksheet
Dim DatesRange As Range
Dim LastCol As Long, LastRow As Long
If Application.WorksheetFunction.CountA(Sheets("Sheet1").Cells) = 0 Then
MsgBox "No Data Found"
Exit Sub
End If
Set wsI = Sheets("Sheet1")
Set wsO = Sheets("Sheet2")
With wsI
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
Set DatesRange = .Range("B1:" & Split(Cells(, LastCol).Address, "$")(1) & 1)
.Columns(1).Copy wsO.Columns(1)
DatesRange.Copy
wsO.Range("B2").PasteSpecial xlPasteValues, _
xlPasteSpecialOperationNone, False, True
.Range("B2:" & Split(Cells(, LastCol).Address, "$")(1) & LastCol).Copy
wsO.Range("C2").PasteSpecial xlPasteValues
End With
End Sub
