在 VBA 中使用数组复制和粘贴

Copy and Paste using array in VBA

提问by Lance Roberts

I am trying to create a relatively simple copy and paste procedure. I have certain cell addresses stored in an array (Results1()) and I am trying to extrapolate the row which it is in to copy an entire row of data into another Excel sheet. I have the following so far and currently I am getting an object required error when I try to define what my 'NextRow' is. Any advice or feedback would be greatly appreciated!

我正在尝试创建一个相对简单的复制和粘贴过程。我将某些单元格地址存储在一个数组 (Results1()) 中，我试图推断它所在的行以将整行数据复制到另一个 Excel 工作表中。到目前为止，我有以下内容，目前当我尝试定义我的“NextRow”是什么时，我收到了一个需要对象的错误。任何建议或反馈将不胜感激！

For i1 = LBound(Results1) To UBound(Results1)
    Set NextRow = Worksheets("searchresult").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
                Range(Results(i1)).EntireRow.Copy NextRow
Next i1

Editted code

编辑代码

    For i1 = LBound(Results1) To UBound(Results1)
        Worksheets("properties").Select
        Set p1results = Range(Results1(i1))
        p1results.EntireRow.Copy
            With Worksheets("SearchResult").Select
                NextRow = Range("D65536").End(xlUp).Row + 1
                Cells(NextRow, 1).Select
                ActiveSheet.Paste
            End With
    Next i1

Update: In actuality my data ranges only range from columns D:P so when I count rows I count from column D (4) because columns A-C are empty ranges. Would this affect the way it is pasting my data?

更新：实际上，我的数据范围仅从 D:P 列开始，因此当我计算行数时，我从 D (4) 列开始计数，因为 AC 列是空范围。这会影响它粘贴我的数据的方式吗？

       On Error Resume Next
   For i1 = LBound(Results1) To UBound(Results1)
        Set NextRow = shSearchResult.Cells(shSearchResult.Rows.Count, 4).End(xlUp).Offset(1, 0)
        shProperties.Range(Results1(i1)).EntireRow.Copy NextRow
        If Err.Number <> 0 Then
            Err.Clear
            MsgBox "Bad address" & Results1(i1)
        End If
    Next i1

回答by chris neilsen

A few potential causes here:

这里有一些潜在的原因：

Your for loop references Results1but your copy references Results(typo in question? or route cause?)

您的 for 循环引用了Results1，但您的副本引用了Results（有问题的错字？或路由原因？）

In your Set NextRow =line references .Cells(Rows.Count,unqualified, Rows refers to the active sheet not "searchresult"sheet. Probably not what you intended, but also will meke no difference in this case.

在未限定的Set NextRow =行引用中.Cells(Rows.Count,，行是指活动工作表而不是工作"searchresult"表。可能不是你想要的，但在这种情况下也没有区别。

Likewise Range(Results(i1))refers to the active sheet, may be ok - depends on the wider context of your application

同样Range(Results(i1))是指活动表，可能没问题 - 取决于您的应用程序的更广泛的上下文

I suspect that your imediate problem is that NextRowhas not been DIM'd. Try adding

我怀疑您的直接问题是NextRow尚未解决DIM。尝试添加

Dim NextRow as Rangeto your Sub

Dim NextRow as Range给你的子

In general it is good practice to use Option Explicit(first line in your module). This forces explicit declaration of all variable.

通常，使用Option Explicit（模块中的第一行）是一种很好的做法。这会强制显式声明所有变量。

EDIT

编辑

based on your edit a "Method 'Range' of object '_Global' failed"error may be caused by an invalid adress string

根据您的编辑，"Method 'Range' of object '_Global' failed"错误可能是由无效的地址字符串引起的

Your first code was actually better than your second. Here's a refactor version

你的第一个代码实际上比你的第二个更好。这是一个重构版本

Sub zxx()
    Dim Results1(0 To 1) As Variant
    Dim i1 As Long
    Dim NextRow As Range
    Dim shProperties As Worksheet
    Dim shSearchResults As Worksheet


    '  other code ...

    Set shSearchResults = ActiveWorkbook.Worksheets("searchresult")
    Set shProperties = ActiveWorkbook.Worksheets("properties")

    On Error Resume Next
    For i1 = LBound(Results1) To UBound(Results1)
        Set NextRow = shSearchResults.Cells(shSearchResults.Rows.Count, 1).End(xlUp).Offset(1, 0)
        shProperties.Range(Results1(i1)).EntireRow.Copy NextRow
    Next i1


End Sub

To detect bad addresses try adding On Error Resume Nextbefore the For and

要检测错误地址，请尝试On Error Resume Next在 For 和

        If Err.Number <> 0 Then
            Err.Clear
            MsgBox "Bad Address " & Results1(i1)
        End If

after the copy (inside the for loop)

复制后（在 for 循环内）

EDIT2

编辑2

Copying an EntireRowinto a single cell only works if the single cell is in column A because because the EntireRow range overhangs the right hand side of the sheet.

将EntireRowa复制到单个单元格仅在单个单元格位于 A 列中时才有效，因为 EntireRow 范围悬垂在工作表的右侧。

Use this to offset back to column A

使用它来偏移回 A 列

Set NextRow = _
    shSearchResults.Cells(shSearchResults.Rows.Count, 4).End(xlUp).Offset(1, -3)

回答by Lance Roberts

You never use the With Statement, so just do this instead:

您从不使用 With 语句，因此只需执行以下操作：

For i1 = LBound(Results1) To UBound(Results1)
    Worksheets("properties").Select
    Set p1results = Range(Results1(i1))
    p1results.EntireRow.Copy
    Worksheets("SearchResult").Select
    NextRow = Range("D65536").End(xlUp).Row + 1
    Cells(NextRow, 1).Select
    ActiveSheet.Paste           
Next i1

or better:

或更好：

For i1 = LBound(Results1) To UBound(Results1)
    Worksheets("properties").Range(Results1(i1)).EntireRow.Copy
    Worksheets("SearchResult").Cells(Range("D65536").End(xlUp).Row + 1, 1).Paste
Next i1

Depending on what you're doing, you might want to use PasteSpecial instead.

根据您在做什么，您可能希望改用 PasteSpecial。