Type Personne
Nom As String
Note As Integer
Etape As String
End Type
Sub essai()
Dim Tablo, Etapes(), i As Long, j As Long, n As Long
Dim a(1 To 6) As Personne, temp As Personne, Dico
Etapes = Array("Strasbourg", "Mulhouse", "Besançon", "Dijon", "Lyon", "Marseille", "Nice")
Set Dico = CreateObject("Scripting.Dictionary")
For i =LBound(Etapes)To UBound(Etapes): Dico.Add Etapes(i), i: Next i
a(1).Nom = "Dupont": a(1).Note = 40: a(1).Etape = "Strasbourg"
a(2).Nom = "Balu": a(2).Note = 30: a(2).Etape = "Lyon"
a(3).Nom = "Charlie": a(3).Note = 20: a(3).Etape = "Besançon"
a(4).Nom = "Durand": a(4).Note = 25: a(4).Etape = "Nice"
a(5).Nom = "Campas": a(5).Note = 35: a(5).Etape = "Lyon"
a(6).Nom = "Vidal": a(6).Note = 25: a(6).Etape = "Mulhouse"
'---- tri bubble
For i = 1 To 6
For j = i To 6
If Dico.Item(a(j).Etape) < Dico.Item(a(i).Etape) Then
temp = a(j)
a(j) = a(i)
a(i) = temp
End If
Next j
Next i
'--
ReDim Tablo(1 To UBound(a))
For n = 1 To UBound(a)
Tablo(n) = a(n).Nom & Chr(10) & a(n).Note & Chr(10) & "-----" & Chr(10) & a(n).Etape
Next n
'renvoi les données
Feuil1.Cells(3, 2).Resize(, UBound(a)) = Application.Transpose(Application.Transpose(Tablo))
End Sub