Sub Bilan()
Dim lastK&, lastG&
With Sheets("KARDEX")
If .Range("p1") Like "Already*" Then
MsgBox "Le traitement a déjà été fait -> Echec !", vbCritical
Exit Sub
Else
.Range("o:p").Clear
End If
lastK = .Cells(.Rows.Count, "e").End(xlUp).Row
If lastK < 2 Then
MsgBox "Aucunne donnée dans 'KARDEX' -> Echec !", vbCritical
Exit Sub
End If
lastG = Sheets("GOODS OUT").Cells(.Rows.Count, "b").End(xlUp).Row
If lastG < 2 Then
MsgBox "Aucunne donnée dans 'GOODS OUT' -> Echec !", vbCritical
Exit Sub
End If
Application.ScreenUpdating = False
.Range("o2").FormulaR1C1 = _
"=RC[-7]-SUMIF( 'GOODS OUT'!C2,RC5,'GOODS OUT'!C4)"
.Range("p2").FormulaR1C1 = _
"=IF(RC[-1]=RC[-7],NA(),"""")"
.Range("O2:P2").AutoFill Destination:=.Range("o2:p" & lastK)
.Range("o2:p" & lastK) = .Range("o2:p" & lastK).Value
On Error Resume Next
.Range("p2:p" & lastK).SpecialCells(xlCellTypeConstants, xlErrors) _
.EntireRow.Delete
On Error GoTo 0
With .Range("o1")
.Value = "New SAP Stk"
.Interior.Color = RGB(200, 255, 50)
.HorizontalAlignment = xlCenter
.Font.Bold = True
End With
.Range("p:p").Clear
With .Range("p1")
.Value = "Already processed"
.Interior.Color = RGB(255, 116, 0)
.HorizontalAlignment = xlCenter
.Font.Bold = True
.WrapText = True
End With
Application.Goto .[a1], True
MsgBox "Le traitement des données est achevé." & vbLf & vbLf & _
"Sur la feuille 'KARDEX', le traitement a supprimé " & _
(lastK - Sheets("KARDEX").Range("a1").CurrentRegion.Rows.Count) & _
" article(s)."
End With
End Sub