vba - 想不通为什么会有错误

Question

当我运行下面的代码时，有时它运行良好，并且大多数时候它会给我一条错误消息，运行时“91”错误（对象变量或未设置块变量）。有什么遗漏吗？拜托我需要你的帮忙

Sub movedata3()
    Dim lastRow As Long
    Dim sht2 As Worksheet
    Dim lastRow2 As Long
    Set sht2 = Workbooks.Open("\\NMFPLPCLB130010\Public\PACKAGING\tracker.xlsm").Sheets("log") 'set destination sheet as sheet 1 of the opened workbook
    lastRow2 = Range("i" & Rows.Count).End(xlUp).Row
    lngLast = Range("b" & Rows.Count).End(xlUp).Row
    With ThisWorkbook.Sheets("PKG Avail nights") ' reference "source" sheet
        lastRow = .Range("D5:O37").Find("*", SearchDirection:=xlPrevious).Row
        With .Range("D:F").Rows("5:" & lastRow) ' reference referenced sheet columns D to F cells from row 5 down to 'LastRow'
            sht2.Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Resize(.Rows.Count, .Columns.Count).Value = .Value ' paste referenced range values to "destination" sheet form column D first empty cell after last not empty one
        End With
        With .Range("K:O").Rows("5:" & lastRow)
            sht2.Range("G" & Rows.Count).End(xlUp).Offset(1, 0).Resize(.Rows.Count, .Columns.Count).Value = .Value
            With ThisWorkbook.Sheets("SHIFT PERFORMANCE DAYS")
                With .Range("AM1")
                    sht2.Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = .Value
                End With
                With .Range("J1")
                    sht2.Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = .Value
                End With
            End With
        End With
    End With
End Sub

标签： vbaexcel

Answer 1

好的，它需要大量的破译，但我认为我已经把你正在做的事情整理成一种不使用大量With陈述的方式。

Sub movedata3()

    Dim sheetLog As Worksheet
    Dim sheetDays As Worksheet
    Dim sheetNights As Worksheet

    Set sheetLog = Workbooks.Open("\\NMFPLPCLB130010\Public\PACKAGING\tracker.xlsm").Sheets("log") 'set destination sheet as sheet 1 of the opened workbook
    Set sheetDays = ThisWorkbook.Sheets("SHIFT PERFORMANCE DAYS")
    Set sheetNights = ThisWorkbook.Sheets("PKG Avail nights")


    'getting the last non-empty row in "PKG Avail nights"
    Dim lastRow As Long
    lastRow = sheetNights.Range("D5:O37").Find("*", SearchDirection:=xlPrevious).row

    'copying from "PKG Avail nights" into the next open cell in "log"
    sheetNights.Range("D5:F" & lastRow).Copy _
        Destination:=sheetLog.Range("D" & sheetLog.Rows.count).End(xlUp).Offset(1, 0)

    sheetNights.Range("K5:O" & lastRow).Copy _
        Destination:=sheetLog.Range("G" & sheetLog.Rows.count).End(xlUp).Offset(1, 0)


    'copying from "SHIFT PERFORMANCE DAYS" into the next open cell in "log"
    Dim lastRowD As Long
    lastRowD = sheetLog.Range("D" & sheetLog.Rows.count).End(xlUp).Offset(1, 0)

    sheetDays.Range("AM1").Copy _
        Destination:=sheetLog.Range("B" & lastRowD)


    Dim lastRowE As Long
    lastRowE = sheetLog.Range("E" & sheetLog.Rows.count).End(xlUp).Offset(1, 0)

    sheetDays.Range("J1").Copy _
        Destination:=sheetLog.Range("C" & lastRowE)

End Sub