应用程序定义或对象定义的错误1004 [英] Application-defined or object-defined error 1004

问题描述

VBA正在将 Sheets（Sheet1）的范围（A& i）.Copy Destination：= Sheets（Sheet2）。Range（ A&A& LastCol - 1）

我想要做的是实际复制A&我单元格（第一次迭代它是 A2 ）到第二个工作表名为Sheet2的范围。

  Sub FindFill（）
 
 Dim DatesRange As Range 
 Dim i As Integer 
 Dim TransposeThis As Range 
 Dim LastCol As Integer 
 
如果WorksheetFunction.CountA（Cells）> 0然后
 LastColumn = Cells.Find（什么：=*，之后：= [A1]，_ 
 SearchOrder：= xlByColumns，_ 
 SearchDirection：= xlPrevious）.Column 
结束如果
 
 
带表格（Sheet1）
设置DatesRange =范围（B2& LastCol）
结束
i = 1 
尽管i< = ActiveSheet.Rows.Count 
表单（Sheet1）。Range（A& i + 1）.Copy Destination：= Sheets（Sheet2）。范围（A&A& LastCol  -  1）
i = i + 1 
循环
 
 
 
 
结束
 
 
结束子
  

解决方案

您在A之前缺少：

 范围（A& ;：A& LastCol  -  1）
  

FOLLOWUP p>

经过您的意见，我的代码中看到很多错误

1） strong>您将 i 变为整数。如果您的最后一行超过32,767，这可能会导致Excel 2007中的错误。将其更改为 Long 我建议您查看此链接。

主题 ：整数，长和字节数据类型

链接： http://msdn.microsoft.com/en-us/library/aa164754%28v=office.10%29.aspx

从上述链接引用

整数变量可以在-32,768和32,767之间保存，而Long变量可以在-2,147,483,648到2,147,483,647之间

2）您正在找到最后一列，但在哪张表？你必须完全符合这条路。

 如果WorksheetFunction.CountA（Sheets（Sheet1）。Cells）> 0然后
 LastCol = Cells.Find（what：=*，After：= [A1]，_ 
 SearchOrder：= xlByColumns，_ 
 SearchDirection：= xlPrevious）.Column 
结束如果
  

与

相同
设置DatesRange =范围（B2& LastCol）
结束

您在范围

$ b之前缺少一个DOT $ b

这是正确的方式...

  .Range（B2 .... 
  

另外范围（B2& LastCol）不会给你你想要的范围，见下面的代码如何创建你的范围。

3）你正在使用一个变量 LastColumn ，但使用 LastCol 。我强烈建议使用 Option Explicit I还将建议您查看此链接（链接中的SEE POINT 2）。

主题：Err是人类

链接： http://www.siddharthrout.com/2011/08/01/to-err-is-human/

4）如果 .CountA（Sheets（Sheet1）。Cells）= 0 会发生什么？ :)我建议你这个代码

 如果WorksheetFunction.CountA（Sheets（Sheet1）。Cells）> 0然后
 LastCol = Cells.Find（what：=*，After：= [A1]，_ 
 SearchOrder：= xlByColumns，_ 
 SearchDirection：= xlPrevious）.Column 
 Else 
 MsgBoxNo Data Found
 Exit Sub 
 End If 
  

5） ActiveSheet.Rows.Count 不会给你最后一个活动的行。它将给您该工作表中的总行数。我建议你得到最后一排有数据的Col A。

你可以使用这个

  With Sheets（Sheet）
 LastRow = .Range（A& .Rows.Count）.End（xlup）.row 
结束
  

现在使用 LastRow 而不是 ActiveSheet.Rows.Count 您还可能需要使用 For Loop ，以便您不必增加我每次都是例如

 对于i = 1 to LastRow 
  

6）最后你不应该使用 End 。原因很简单这就像使用电源关闭按钮切换电脑。 End语句突然停止代码执行，而不调用Unload，QueryUnload或Terminate事件或任何其他Visual Basic代码。另外由其他程序保存的对象引用（如果有）也是无效的。

7）根据你在聊天中的形象，我相信你是试图这样做？这使用不使用任何循环的代码。

  Option Explicit 
 
 Sub FindFill（） 
 Dim wsI As Worksheet，wsO As Worksheet 
 Dim DatesRange As Range 
 Dim LastCol As Long，LastRow As Long 
 
如果Application.WorksheetFunction.CountA（Sheets（ Sheet1）。单元格）= 0然后
 MsgBox找不到数据
退出子
结束如果
 
设置wsI =表（Sheet1）
设置wsO = Sheets（Sheet2）
 
带有wsI 
 LastCol = .Cells（1，.Columns.Count）.End（xlToLeft）.Column 
 LastRow = .Range（A& .Rows.Count）.End（xlUp）.Row 
设置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）.Add ress，$）（1）& LastCol）.Copy 
 wsO.Range（C2）。PasteSpecial xlPasteValues 
 End with 
 End Sub 
  

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)

What I am trying to do is actually to copy the "A" & i cell (in first iteration it's A2) to a range in the second worksheet named 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

解决方案

You are missing a ":" before "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 i as Integer. This can give you an error in Excel 2007 onwards if your last row is beyond 32,767. Change it to Long I would recommend having a look at this link.

Topic: The Integer, Long, and Byte Data Types

Link: 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

2) You are finding the Last Column But in Which Sheet? You have to fully qualify the path Like this.

    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

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.

3) You are using a variable LastColumn but using LastCol. I would strongly advise using Option Explicit I would also recommend having a look at this link (SEE POINT 2 in the link).

Topic: To ‘Err’ is Human

Link: 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

    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.Count will 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.

You can use this for that

With Sheets("Sheet")
    LastRow =.Range("A" & .Rows.Count).End(xlup).row
End With

Now use LastRow instead of ActiveSheet.Rows.Count You also might want to use a For Loop so that you don't have to increment i every time. For example

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.

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.

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

这篇关于应用程序定义或对象定义的错误1004的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！