For回圈沒有按預期作業但沒有給出任何錯誤

unused_row = report.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    
    For Each rng In export.Range("D1:D600")
        If Not IsEmpty(rng) Then
            Set ferie = rng.Offset(0, 17)
            Set permessi = rng.Offset(1, 17)
            Set flessibilita = rng.Offset(2, 17)
        ferie.Copy report.Range("b" & unused_row)
        permessi.Copy report.Range("c" & unused_row)
        flessibilita.Copy report.Range("d" & unused_row)
        End If
    Next

我有以下代碼沒有按預期作業。它應該遍歷 export.Range("D1:D600") 中的每個單元格，并在 B 列到 D 列的另一張表中復制（使用最新未使用的行來不覆寫資料）在回圈到達的偏移量中指定的值在，用 rng 指定。

代碼運行沒有任何錯誤，但不復制所需的資料。

有任何想法嗎？

uj5u.com熱心網友回復：

復制到另一個作業表

• Export并且Report是包含此代碼的作業簿中兩個作業表的代碼名稱。
• 要復制到下一行，您必須在陳述句unused_row = unused_row 1末尾執行。If
• 我選擇使用單元格范圍并在回圈開始時將其偏移作為替代方案。
• 例如，如果您將該...End(xlup)...行放在回圈中（不推薦），那么您必須確保它在第 2 列（“B”）上進行計算，因為您沒有寫入第 1 列（“A”）。
• 這三個“中間”范圍變數似乎沒什么用。請參閱沒有它們的測驗 2-4。

Option Explicit

Sub Test1() ' copy values, formats and formulas
    
    Dim dCell As Range: Set dCell = Report.Cells(Report.Rows.Count, "B") _
        .End(xlUp) ' last occupied destination cell
    
    Dim ferie As Range, permessi As Range, flessibilita As Range
    Dim sCell As Range
    
    For Each sCell In Export.Range("D1:D600").Cells
        If Not IsEmpty(sCell) Then
            Set dCell = dCell.Offset(1) ' next destination cell
            With sCell
                Set ferie = .Offset(0, 17)
                Set permessi = .Offset(1, 17)
                Set flessibilita = .Offset(2, 17)
            End With
            With dCell
                ferie.Copy .Offset(, 0)
                permessi.Copy .Offset(, 1)
                flessibilita.Copy .Offset(, 2)
            End With
        End If
    Next

End Sub

Sub Test2() ' copy values, formats and formulas
    
    Dim dCell As Range: Set dCell = Report.Cells(Report.Rows.Count, "B") _
        .End(xlUp) ' last occupied destination cell
    
    Dim sCell As Range
    
    For Each sCell In Export.Range("D1:D600").Cells
        If Not IsEmpty(sCell) Then
            Set dCell = dCell.Offset(1) ' next destination cell
            sCell.Offset(0, 17).Copy dCell.Offset(, 0)
            sCell.Offset(1, 17).Copy dCell.Offset(, 1)
            sCell.Offset(2, 17).Copy dCell.Offset(, 2)
        End If
    Next sCell

End Sub

Sub Test3() ' copy only values; more efficient
    
    Dim dCell As Range: Set dCell = Report.Cells(Report.Rows.Count, "B") _
        .End(xlUp) ' last occupied destination cell
    
    Dim sCell As Range
    
    For Each sCell In Export.Range("D1:D600").Cells
        If Not IsEmpty(sCell) Then
            Set dCell = dCell.Offset(1) ' next destination cell
            dCell.Offset(, 0).Value = sCell.Offset(0, 17).Value
            dCell.Offset(, 1).Value = sCell.Offset(1, 17).Value
            dCell.Offset(, 2).Value = sCell.Offset(2, 17).Value
        End If
    Next sCell

End Sub

Sub Test4() ' copy only values shorter; more efficient
    
    Dim dCell As Range: Set dCell = Report.Cells(Report.Rows.Count, "B") _
        .End(xlUp) ' last occupied destination cell
    
    Dim sCell As Range
    Dim i As Long
    
    For Each sCell In Export.Range("D1:D600").Cells
        If Not IsEmpty(sCell) Then
            Set dCell = dCell.Offset(1) ' next destination cell
            For i = 0 To 2
                dCell.Offset(, i).Value = sCell.Offset(i, 17).Value
            Next i
        End If
    Next sCell

End Sub

標籤：擅长 vba

上一篇：修改合并和匯總重復行的子

下一篇：用戶表單在取消命令上卸載，但不是在可能正確提交的資料上