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 48 49 50 51 52
| Sub Bouton1_Clic()
Application.ScreenUpdating = False
'trier les données
Columns("G:G").Select
Selection.Sort Key1:=Range("G1")
' convertir les données de la colonne G
For i = 1 To Range("G65535").End(xlUp).Row
For j = 1 To Len(Cells(i, 7))
If Mid(Cells(i, 7), j, 1) = "£" Then
Cells(i, 8) = Mid(Cells(i, 7), j + 1, 999)
Cells(i, 7) = Left(Cells(i, 7), j - 1)
End If
For k = 1 To Len(Cells(i, 8))
If Mid(Cells(i, 8), k, 1) = "£" Then Cells(i, 8) = Left(Cells(i, 8), k - 1)
Next k
Next j
Next i
'----------------------
'suppression des doublons
For i = 1 To Range("G65535").End(xlUp).Row
While UCase(Cells(i + 1, 7).Value) = UCase(Cells(i, 7).Value) And UCase(Cells(i + 1, 8).Value) = UCase(Cells(i, 8).Value) And (Cells(i + 1, 7).Value) <> ""
Rows(i).Delete
'Cells(i, 1).EntireRow.Delete
Wend
Next i
artiste = "aucun"
' répartition des données en colonne A et B
ligne = 1
For i = 1 To Range("G65535").End(xlUp).Row
If UCase(Cells(i + 1, 7)) <> UCase(Cells(i, 7)) Then
If UCase(Cells(i, 7)) <> UCase(artiste) Then
Cells(ligne, 1) = Cells(i, 7)
Cells(ligne, 1).Borders(xlEdgeTop).LineStyle = xlContinuous ' (xlEdgeTop)
artiste = UCase(Cells(i, 7))
End If
Cells(ligne, 2) = Cells(i, 8)
Else
If UCase(Cells(i, 7)) <> UCase(artiste) Then
Cells(ligne, 1) = Cells(i, 7)
Cells(ligne, 1).Borders(xlEdgeTop).LineStyle = xlContinuous
artiste = UCase(Cells(i, 7))
End If
Cells(ligne, 2) = Cells(i, 8)
Cells(ligne, 3) = Cells(i + 1, 8)
i = i + 1
End If
ligne = ligne + 1
Next i
Application.ScreenUpdating = True
End Sub |
Partager