时间戳

首页 > 解决方案 > 对于每个循环中的 cell.activate：运行时错误“1004”：Range 类的激活方法失败

excel - 对于每个循环中的 cell.activate：运行时错误“1004”：Range 类的激活方法失败

问题描述

我正在尝试使用以下代码进行 For Each 循环。

Sub child_builder()

Dim cell As Range

For Each cell In ActiveSheet.Range("D5:D102")

    If cell.Value = "Y" Then

        cell.Activate
        Selection.Offset(0, -2).Select
        Selection.Copy
        Sheets("Child").Select
        ActiveSheet.Paste
        Selection.Offset(0, 1).Select
        ThisWorkbook.Sheets("Product Builder").Activate
        Range("G6:I10").Select
        Selection.Copy
        Sheets("Child").Select
        ActiveSheet.Paste
        Selection.End(xlDown).Select
        Selection.Offset(1, -1).Select

    End If

Next cell

End Sub

第一个循环有效，但由于该行而在下一个循环中失败cell.activate。

错误是

运行时错误“1004”： Range 类的激活方法失败。

标签： excelvbaforeach

解决方案

也许这会让你开始，我知道开始重写宏记录器的东西很难。考虑代码中的注释。

Sub child_builder()

Dim cell As Range
Dim RowToPasteTo As Long

RowToPasteTo = 1

For Each cell In ThisWorkbook.Sheets("PutTheNameOfYourSheetHere").Range("D5:D102")

    If cell.Value = "Y" Then

    'From the cell that has a Y in it, move left 2 columns and copy that to the A1 cell on the sheet "Child". Also consider just assigning a value with = if you dont need formats etc.
    cell.Offset(0, -2).Copy ThisWorkbook.Sheets("Child").Range("A" & RowToPasteTo) 'Change the range here if you wanna paste somewhere else

    'I am not sure what you are doing now but I assume you want to copy Range G6:I10 from the "Product Builder" sheet just to the right of the value we just copy pasted?
    ThisWorkbook.Sheets("Product Builder").Range("G6:I10").Copy ThisWorkbook.Sheets("Child").Range("B" & RowToPasteTo) 'Change the range here if it needs to go somewhere else.

    'From what I can tell you want to paste stuff just below what ever you just pasted on the next loop?
    RowToPasteTo = RowToPasteTo + 5 'Move down 5 rows (because G6:I10 are 5 rows)

    End If

Next

End Sub

推荐阅读