Option Explicit
Option Compare Text
Sub StockCalculator()
Dim PlageCible As Range, PlageSource As Range, CellCible As Range, CellSource As Range
Dim WS As Worksheet
Dim Counter As Integer
Dim ArchiveCounter As Integer
With Worksheets('Stocks')
Set PlageCible = .Range(.Range('A2'), .Range('A5000').End(xlUp))
End With
For Each CellCible In PlageCible
For Each WS In ThisWorkbook.Worksheets
Select Case WS.Name
Case 'Achats'
With WS
Set PlageSource = .Range(.Range('A2'), .Range('A5000').End(xlUp))
End With
For Each CellSource In PlageSource
If CellCible = CellSource Then
Counter = Counter + CellSource.Offset(0, 4)
End If
Next
Case 'Commandes'
With WS
Set PlageSource = .Range(.Range('B2'), .Range('B5000').End(xlUp))
End With
For Each CellSource In PlageSource
If CellCible = CellSource Then
Counter = Counter - CellSource.Offset(0, 1)
End If
Next
Case 'Archives'
With WS
Set PlageSource = .Range(.Range('B2'), .Range('B5000').End(xlUp))
End With
For Each CellSource In PlageSource
If CellCible = CellSource Then
ArchiveCounter = ArchiveCounter - CellSource.Offset(0, 1)
End If
Next
End Select
Next WS
With CellCible
.Offset(0, .End(xlToRight).Column) = ArchiveCounter
.Offset(0, 1) = Counter + Application.WorksheetFunction.Sum(Range(.Offset(0, 2), .Offset(0, 254)))
End With
Counter = 0
ArchiveCounter = 0
Next CellCible
End Sub