Public Sub Activité_Unités_janvier()
Dim rng As Range, M As Integer
With Worksheets("récapitulatif")
M = .Range("G10").End(xlUp).Row
Set rng = .Range("G1099:G" & M)
rng.Name = "ColG"
Set rng = .Range("I1099:I" & M)
rng.Name = "ColI"
Set rng = .Range("H1099:H" & M)
rng.Name = "ColH"
Set rng = .Range("BH1099:BH" & M)
rng.Name = "ColBH"
End With
Set rng = Nothing
Dim L As Long, DerL As Long, E As String, B As String, C As String
Application.ScreenUpdating = False
With Worksheets("Production Unités")
DerL = .Range("G1099").End(xlUp).Row
E = .Range("f3") 'variable origine 1
B = .Range("B4") 'variable am ou qualité 1
D = .Range("B59") 'variable am ou qualité 2
F = .Range("B114") 'variable am ou qualité 3
G = .Range("B169") 'variable am ou qualité 4
H = .Range("B225") 'variable am ou qualité 5
For L = 7 To 55
E = .Range("f3"): C = .Range("C" & L)
.Cells(L, 6) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & B & """))")
E = .Range("G3")
.Cells(L, 7) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & B & """))")
E = .Range("H3")
.Cells(L, 8) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & B & """))")
E = .Range("I3")
.Cells(L, 9) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & B & """))")
E = .Range("J3")
.Cells(L, 10) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & B & """))")
E = .Range("K3")
.Cells(L, 11) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & B & """))")
E = .Range("L3")
.Cells(L, 12) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & B & """))")
E = .Range("M3")
.Cells(L, 13) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & B & """))")
E = .Range("N3")
.Cells(L, 14) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & B & """))")
E = .Range("O3")
.Cells(L, 15) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & B & """))")
E = .Range("P3")
.Cells(L, 16) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & B & """))")
E = .Range("Q3")
.Cells(L, 17) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & B & """))")
E = .Range("R3")
.Cells(L, 18) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & B & """))")
E = .Range("S3")
.Cells(L, 19) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & B & """))")
E = .Range("T3")
.Cells(L, 20) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & B & """))")
E = .Range("U3")
.Cells(L, 21) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & B & """))")
E = .Range("V3")
.Cells(L, 22) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & B & """))")
Next
' deuxieme choix am ou qualité
For L = 62 To 110
E = .Range("f3"): C = .Range("C" & L)
.Cells(L, 6) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & D & """))")
E = .Range("G3")
.Cells(L, 7) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & D & """))")
E = .Range("H3")
.Cells(L, 8) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & D & """))")
E = .Range("I3")
.Cells(L, 9) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & D & """))")
E = .Range("J3")
.Cells(L, 10) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & D & """))")
E = .Range("K3")
.Cells(L, 11) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & D & """))")
E = .Range("L3")
.Cells(L, 12) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & D & """))")
E = .Range("M3")
.Cells(L, 13) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & D & """))")
E = .Range("N3")
.Cells(L, 14) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & D & """))")
E = .Range("O3")
.Cells(L, 15) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & D & """))")
E = .Range("P3")
.Cells(L, 16) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & D & """))")
E = .Range("Q3")
.Cells(L, 17) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & D & """))")
E = .Range("R3")
.Cells(L, 18) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & D & """))")
E = .Range("S3")
.Cells(L, 19) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & D & """))")
E = .Range("T3")
.Cells(L, 20) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & D & """))")
E = .Range("U3")
.Cells(L, 21) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & D & """))")
E = .Range("V3")
.Cells(L, 22) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & D & """))")
Next
' troisieme choix am ou qualité
For L = 117 To 165
E = .Range("f3"): C = .Range("C" & L)
.Cells(L, 6) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & F & """))")
E = .Range("G3")
.Cells(L, 7) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & F & """))")
E = .Range("H3")
.Cells(L, 8) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & F & """))")
E = .Range("I3")
.Cells(L, 9) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & F & """))")
E = .Range("J3")
.Cells(L, 10) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & F & """))")
E = .Range("K3")
.Cells(L, 11) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & F & """))")
E = .Range("L3")
.Cells(L, 12) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & F & """))")
E = .Range("M3")
.Cells(L, 13) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & F & """))")
E = .Range("N3")
.Cells(L, 14) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & F & """))")
E = .Range("O3")
.Cells(L, 15) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & F & """))")
E = .Range("P3")
.Cells(L, 16) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & F & """))")
E = .Range("Q3")
.Cells(L, 17) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & F & """))")
E = .Range("R3")
.Cells(L, 18) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & F & """))")
E = .Range("S3")
.Cells(L, 19) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & F & """))")
E = .Range("T3")
.Cells(L, 20) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & F & """))")
E = .Range("U3")
.Cells(L, 21) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & F & """))")
E = .Range("V3")
.Cells(L, 22) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & F & """))")
Next
' quatrieme choix am ou qualité
For L = 172 To 220
E = .Range("f3"): C = .Range("C" & L)
.Cells(L, 6) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & G & """))")
E = .Range("G3")
.Cells(L, 7) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & G & """))")
E = .Range("H3")
.Cells(L, 8) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & G & """))")
E = .Range("I3")
.Cells(L, 9) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & G & """))")
E = .Range("J3")
.Cells(L, 10) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & G & """))")
E = .Range("K3")
.Cells(L, 11) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & G & """))")
E = .Range("L3")
.Cells(L, 12) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & G & """))")
E = .Range("M3")
.Cells(L, 13) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & G & """))")
E = .Range("N3")
.Cells(L, 14) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & G & """))")
E = .Range("O3")
.Cells(L, 15) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & G & """))")
E = .Range("P3")
.Cells(L, 16) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & G & """))")
E = .Range("Q3")
.Cells(L, 17) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & G & """))")
E = .Range("R3")
.Cells(L, 18) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & G & """))")
E = .Range("S3")
.Cells(L, 19) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & G & """))")
E = .Range("T3")
.Cells(L, 20) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & G & """))")
E = .Range("U3")
.Cells(L, 21) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & G & """))")
E = .Range("V3")
.Cells(L, 22) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & G & """))")
Next
' cinquieme choix am ou qualité
For L = 227 To 275
E = .Range("f3"): C = .Range("C" & L)
.Cells(L, 6) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & H & """))")
E = .Range("G3")
.Cells(L, 7) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & H & """))")
E = .Range("H3")
.Cells(L, 8) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & H & """))")
E = .Range("I3")
.Cells(L, 9) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & H & """))")
E = .Range("J3")
.Cells(L, 10) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & H & """))")
E = .Range("K3")
.Cells(L, 11) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & H & """))")
E = .Range("L3")
.Cells(L, 12) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & H & """))")
E = .Range("M3")
.Cells(L, 13) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & H & """))")
E = .Range("N3")
.Cells(L, 14) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & H & """))")
E = .Range("O3")
.Cells(L, 15) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & H & """))")
E = .Range("P3")
.Cells(L, 16) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & H & """))")
E = .Range("Q3")
.Cells(L, 17) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & H & """))")
E = .Range("R3")
.Cells(L, 18) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & H & """))")
E = .Range("S3")
.Cells(L, 19) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & H & """))")
E = .Range("T3")
.Cells(L, 20) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & H & """))")
E = .Range("U3")
.Cells(L, 21) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & H & """))")
E = .Range("V3")
.Cells(L, 22) = Evaluate("sumproduct((ColG=""" & E & """)*(ColI=""" & C & """)*(ColH=""" & H & """))")
Next
Application.ScreenUpdating = True
End With
End Sub