找不到对象-将excel范围从一个工作表复制到另一个工作表

Question

Private Sub CommandButton1_Click()

    Dim cel As Range, lRow As Long

    'next line determines the last row in column 1 (A), of the first Worksheet
    lRow = Worksheets("Delta").UsedRange.Columns(5).Rows.Count

    'iterate over every cell in the UsedRange 
    For Each cel In Worksheets("Delta").Range("E10:E" & lRow)

        'cel represents the current cell
        'being processed in this iteration of the loop

        'Len() determines number of characters in the cell
        If Len(cel.Value2) > 0 Then

            'if cel is not empty, copy the value to the cell range (D1,D2,D3...) mentioned

            Sheets("Traceability").Select
            Traceability.Range("D3:D100").Select = cel.Value2    '--->Object not defined

        End If

    Next    'move on the next (lower) cell in column 1

End Sub

在复制数据范围时，我遇到了object not defined的错误。我复制单元格值的方法是否正确？

Answer 1

这就是我最终想到的

Private Sub CommandButton1_Click()

    Dim cel As Range, lRow As Long
    Dim i As Integer


    lRow = Worksheets("Delta").UsedRange.Columns(5).Rows.Count
    rw = 3
    'iterate over every cell in the UsedRange
    For Each cel In Worksheets("Delta").Range("E10:E" & lRow)
    If Len(cel.Value2) > 0 Then

        'if cel is not empty, copy the value to the cell
        Sheets("Traceability").Range("D" & rw).Value = cel.Value2
        rw = rw + 1
    End If
    Next

End Sub

Answer 2

尝试:删除：

Sheets("Traceability").Select

更改：

Traceability.Range("D3:D100").Select = cel.Value2

至

 Sheets("Traceability").Range("D3:D100") = cel.Value2

我已经有一段时间没有这样做了，但如果我没记错的话，选择工作表并不会将它赋给变量。你已经选择了可追溯性工作表，然后你试图在“可追溯性”上做一些事情，但没有告诉它什么是“可追溯性”。如果这有意义的话。