时间戳

首页 > 解决方案 > 错误 1004 函数并选择多个列

excel - 错误 1004 函数并选择多个列

问题描述

我遇到了基于某个值的特定单元格的问题。

这个想法是，如果“B”中的单元格包含 119，则选择“B:E”、“G:H”、“J:M”列并将其复制到同一工作表的不同部分。我不希望行命令选择整行，因为有些单元格的公式我不想被移动。

此外，显示的错误是 1004：对象定义错误，我正在努力查看哪个部分正在抛出该函数。

任何帮助将不胜感激

Dim r As Long, endRow As Long, pasteRowIndex As Long, y As Range

endRow = 31 ' last row
pasteRowIndex = 67 ' paste row
Set y = ActiveSheet.Range("B:E,G:H,J:M")

For r = 1 To endRow 'Loop through sheet1 and search for your criteria

    If Cells(r, Columns("B").Column).Value = "119" Then 'Found

            'Copy the current row
            Rows(r, y).Select
            Selection.Copy

            'Switch to the row where i want to paste it & paste

            Rows(pasteRowIndex).Select
            ActiveSheet.Paste

            'Next match
            pasteRowIndex = pasteRowIndex + 1



    End If
Next r
End Sub```

标签： excelvba

解决方案

也许是这样，虽然不确定你是否向下粘贴到A67？

Sub x()

Dim r As Long, endRow As Long, pasteRowIndex As Long, y As Range

endRow = 31 ' last row
pasteRowIndex = 67 ' paste row

Set y = ActiveSheet.Range("B:E,G:H,J:M")

For r = 1 To endRow 'Loop through sheet1 and search for your criteria
    If Cells(r, "B").Value = 119 Then 'Found
        'Copy the current row
        Intersect(y, Rows(r)).Copy Cells(pasteRowIndex, 1) 'not sure about this destination
        pasteRowIndex = pasteRowIndex + 1
    End If
Next r

End Sub

推荐阅读