Your code contains some errors. This is better:
Code:Sub packingupdate() Dim LotFind As Range, Lots As Range, LotNum As Range Dim LotVal As Double Application.ScreenUpdating = False With Sheets("Packing") Set Lots = .Range("F5", .Range("F" & .Rows.Count).End(xlUp)) End With With Sheets("Production") For Each LotNum In Lots If Trim(LotNum.Value) <> "" Then Set LotFind = .Columns("K:K").Find(Left(LotNum, 6), After:=.Range("K" & Rows.Count), _ LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) If Not LotFind Is Nothing Then LotVal = LotFind.Offset(0, -1).Value - LotNum.Offset(0, 1).Value If LotVal <= 0 Then LotFind.EntireRow.Delete xlShiftUp ElseIf LotFind.Offset(0, -1).HasFormula Then LotFind.Offset(0, -1).Formula = LotFind.Offset(0, -1).Formula & "-" & LotNum.Offset(0, 1).Value Else LotFind.Offset(0, -1).Formula = "=" & LotFind.Offset(0, -1).Value & "-" & LotNum.Offset(0, 1).Value End If LotNum.EntireRow.ClearContents End If End If Next End With Application.ScreenUpdating = True End Sub




Reply With Quote