1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
| Sub Trier()
Const cln = "A" ' colonne liste numéro
Const lln = 2 ' ligne liste numéro
Const ctn = "A" ' colonne tri numéro
Dim der As Long ' dernière ligne
Dim lig As Long ' ligne
Dim p_t As Range ' position tri
Dim nbn As Long ' nombre numéros
Dim wl As Worksheet ' onglet liste
Dim wt As Worksheet ' onglet tri
Set wl = ActiveWorkbook.Worksheets("liste") ' affectaion liste
Set wt = ActiveWorkbook.Worksheets("tri") ' affectaion tri
der = wl.Cells(wl.Rows.Count, cln).End(xlUp).Row ' dernière ligne
With wl.Sort ' tri numéros
With .SortFields
.Clear
.Add Key:=wl.Cells(lln, cln).Resize(der, 1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
End With
.SetRange wl.Cells(lln, cln).Resize(der, 2)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
lig = 2
While lig < der ' tri sous-numéros
nbn = Application.CountIf(wl.Columns(cln), wl.Cells(lig, cln)) ' nombre numéros
Set p_t = wt.Columns(ctn).Find(wl.Cells(lig, cln), wt.Range(ctn & "1"), xlValues, xlWhole)
If Not p_t Is Nothing Then
With wl.Sort
With .SortFields
.Clear
.Add Key:=wl.Cells(lig, Chr(Asc(cln) + 1)).Resize(nbn, 1), SortOn:=xlSortOnValues, Order:=xlAscending, _
CustomOrder:=Replace(p_t.Offset(0, 1), " ", ","), DataOption:=xlSortNormal
End With
.SetRange wl.Cells(lig, cln).Resize(nbn, 2)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If
lig = lig + nbn
Wend
End Sub |
Partager