[COLOR=GRAY][B][I]DANS UN MODULE DE CODE STANDARD[/I][/B][/COLOR]
[COLOR=NAVY]Option Explicit[/COLOR]
[COLOR=NAVY]Sub[/COLOR] Traitement(Cel [COLOR=NAVY]As[/COLOR] Range)
[COLOR=GREEN]'myDearFriend! - www.mdf-xlpages.com[/COLOR]
[COLOR=NAVY]Dim[/COLOR] Operateurs [COLOR=NAVY]As Object
Dim[/COLOR] Result [COLOR=NAVY]As[/COLOR] Range
[COLOR=NAVY]Dim[/COLOR] TabTemp [COLOR=NAVY]As Variant[/COLOR], N [COLOR=NAVY]As Variant[/COLOR], S [COLOR=NAVY]As Variant
Dim[/COLOR] Ateliers [COLOR=NAVY]As String[/COLOR], Atelier [COLOR=NAVY]As String
Dim[/COLOR] L [COLOR=NAVY]As Long
Dim[/COLOR] C [COLOR=NAVY]As Integer
Dim[/COLOR] Car [COLOR=NAVY]As Byte[/COLOR]
[COLOR=GREEN]'Extrait les ateliers concernés (sans séparateur)[/COLOR]
Ateliers = Replace(Cel.Offset(0, -3).Text, "-", "")
[COLOR=NAVY]With[/COLOR] Sheets("Opérateurs")
[COLOR=GREEN]'Mémorise le tableau des opérateurs[/COLOR]
C = .Range("IV2").[COLOR=NAVY]End[/COLOR](xlToLeft).Column
L = .Range("A65536").[COLOR=NAVY]End[/COLOR](xlUp).Row
TabTemp = .Range(.Cells(2, 1), .Cells(L, C)).Value
[COLOR=NAVY]Set[/COLOR] Operateurs = CreateObject("Scripting.Dictionary")
[COLOR=GREEN]'Pour chaque Atelier à trouver[/COLOR]
[COLOR=NAVY]For[/COLOR] Car = 1 [COLOR=NAVY]To[/COLOR] Len(Ateliers) [COLOR=NAVY]Step[/COLOR] 2
Atelier = Mid(Ateliers, Car, 2)
[COLOR=NAVY]For[/COLOR] C = 3 [COLOR=NAVY]To UBound[/COLOR](TabTemp, 2)
[COLOR=NAVY]If[/COLOR] TabTemp(1, C) = Atelier [COLOR=NAVY]Then
For[/COLOR] L = 5 [COLOR=NAVY]To UBound[/COLOR](TabTemp, 1)
[COLOR=NAVY]If[/COLOR] TabTemp(L, C) <> "" [COLOR=NAVY]Then[/COLOR]
[COLOR=GREEN]'"Collecte" les statuts et noms des opérateurs (sans doublon)[/COLOR]
[COLOR=NAVY]On Error Resume Next[/COLOR]
Operateurs.Add TabTemp(L, 2), TabTemp(L, 1)
[COLOR=NAVY]On Error GoTo[/COLOR] 0
[COLOR=NAVY]End If
Next[/COLOR] L
[COLOR=NAVY]Exit For
End If
Next[/COLOR] C
[COLOR=NAVY]Next[/COLOR] Car
[COLOR=NAVY]End With[/COLOR]
[COLOR=GREEN]'MAJ résultats[/COLOR]
[COLOR=NAVY]With[/COLOR] Sheets("Edition Nom")
[COLOR=NAVY]Set[/COLOR] Result = .Range("A8:B65536")
Result.ClearContents
.Cells(2, 2).Value = Cel.Text
S = Operateurs.items
N = Operateurs.keys
[COLOR=NAVY]For[/COLOR] L = 0 [COLOR=NAVY]To[/COLOR] Operateurs.Count - 1
.Cells(L + 8, 1).Value = S(L) [COLOR=GREEN]'Statuts[/COLOR]
.Cells(L + 8, 2).Value = N(L) [COLOR=GREEN]'Noms[/COLOR]
[COLOR=NAVY]Next[/COLOR] L
Result.Sort Key1:=.Range("A8"), Order1:=xlAscending, Key2:=.Range("B8") _
, Order2:=xlAscending
[COLOR=NAVY]End With
End Sub[/COLOR]