Problème de tri en vba

Version imprimable

Bonjour,

J'ai un problème de tri, en vba, avec une erreur 1004 récalcitrante.

Le tri s'effectue sur une zone nommée, par exemple "zone_test". Le nombre de lignes de la zone, qui est définie par la formule DECALER, peut varier, mais démarre toujours à la ligne 9 (jamais avant). Le tri doit s'effectuer, non sur cette zone, mais sur les lignes de cette zone.

J'ai donc débuté ma macro ainsi:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Application.ScreenUpdating = False Application.EnableEvents = False ActiveSheet.Unprotect Dim ws As Worksheet Dim lignes As Range Set ws = ThisWorkbook.Worksheets(1) Set lignes = ws.Range("zone_test").Rows With lignes .Sort Key1:=Range("AO1"), Key2:=Range("AQ1"), Key3:=Range("J1"), Header:=xlNo, _ Order1:=xlDescending, Order2:=xlAscending, Order3:=xlAscending End With
J'ai essayé de changer AO1 par AO, ainsi que AQ1 par AQ et J1 par J, mais cela ne change rien. J'ai toujours une erreur 1004.
Et la feuille est bien déprotégée avant le tri.
Quand à la zone nommée, elle définit le nombre de lignes où se trouvent des valeurs (par exemple, J9:j32). Dans ce cas, lignes devrait sélectionner les lignes 9 à 32.

D'où vient l'erreur?

18/05/2024, 10h57
Philippe Tulliez

Bonjour,
Est-ce que la plage nommée "zone_test" englobe bien les références passées aux arguments Key1, Key2 et Key3 ?
18/05/2024, 12h26
Cahlagan

La plage "zone_test" contient des données depuis la ligne 9 et en-dessous. Il n'y a jamais de ligne vide, elles se touchent toujours. Les libellés des colonnes, eux, sont en ligne 8. Lorsque j'ai utilisé l'enregistreur de macros, j'avais testé avec 3 lignes, il m'a mis dans les clés AO9:AO11. Mais comme cette plage va s'étendre, j'ai mis les premières lignes AO1 (ou AO, je crois que j'ai même essayé AO:AO), mais sans succès. Sachant que la plage "zone_test" va subir des tris, comment faire pour dénommer correctement les clés?

Merci de votre intérêt pour mon problème!
18/05/2024, 14h23
Philippe Tulliez

Bonjour,
Ce que je voulais dire, c'est que la référence aux colonnes à trier doivent absolument se trouver dans la périphérie de la plage nommée "zone_test" il est possible qu'avec la fonction DECALER les colonnes référencées soient hors du champ de cette plage.
Il faudra donc peut-être ajuster la référence aux colonnes à trier
Est-ce que Worksheets(1) est la bonne feuille ?

A vérifier avec l'instruction Debug.Print lignes.Address(External:=True)
18/05/2024, 14h54
Cahlagan

Oui, Worksheets(1) est la bonne feuille. Je n'ai que deux feuilles dans le fichier, et la feuille 2 n'est utilisée que pour stocker des listes pour des menus déroulants.
Il n'y a pas non plus de filtrage, toutes les cellules sont toujours affichées. Il n'y a que le tri qui bouscule les cellules.

Que signifie "ajuster la référence aux colonnes à trier"?

Une alternative serait de stocker dans une cellule la valeur du nombre de lignes de la plage "zone_test" (finalement, la fonction DECALER ne m'est utile que pour déterminer ça, le nombre de lignes contenues dans la plage). Car, en fin de compte, le tri s'effectuera sur un nombre de lignes, précisément défini par la fonction DECALER.
20/05/2024, 11h49
Philippe Tulliez

Bonjour,

Citation:

Oui, Worksheets(1) est la bonne feuille. Je n'ai que deux feuilles dans le fichier, et la feuille 2 n'est utilisée que pour stocker des listes pour des menus déroulants.

Même si l'on est certain de la feuille, ce n'est pas une bonne idée d'utiliser le numéro d'index d'une feuille sachant que l'index 1 correspond à la première feuille à gauche. Il suffirait d'un déplacement de feuille pour ne plus trier sur la bonne.

Citation:

Que signifie "ajuster la référence aux colonnes à trier"?

Comme on utilise la fonction DECALER, il serait possible que les colonnes soient déplacées et donc il y a lieu d'adapter l'adresse des colonnes à trier

Citation:

Une alternative serait de stocker dans une cellule la valeur du nombre de lignes de la plage "zone_test" (finalement, la fonction DECALER ne m'est utile que pour déterminer ça, le nombre de lignes contenues dans la plage). Car, en fin de compte, le tri s'effectuera sur un nombre de lignes, précisément défini par la fonction DECALER.

Pourquoi se compliquer la vie. En utilisant les tableaux structurés, qui existe depuis la version 2007, on trie les colonnes sans se préoccuper du nombre de lignes et sans devoir lui indiquer que le tri doit se faire sur la plage de données uniquement.
20/05/2024, 20h00
Cahlagan

Bonjour,

Il n'y a pas de chance que je déplace la feuille (les onglets seront de toutes façons masqués). Et il m'est déjà arrivé de changer le nom de la feuille, ce qui complique aussi les choses... Mais de toutes façons, le terme Worksheets(1) fait toujours référence à la feuille 1, non?

J'ai trouvé une solution à mon problème. J'ai renoncé à mon idée de sélectionner toutes les lignes contenant les données, mais j'ai opté pour une sélection (toujours calculée avec la fonction DECALER) plus petite, mais garantissant que toutes les lignes contenant des données en fassent partie, ainsi que toutes les colonnes impliquées. Et ce qui coinçait avec la sélection des lignes fonctionne beaucoup mieux avec une sélection "plus petite".

Je n'avais jamais entendu parler de tableaux structurés. J'ai toujours utilisé la fonction DECALER pour calculer la plage exacte, au fur et à mesure qu'elle s'agrandit. A vous lire, je me suis compliqué la tâche... Ma prudence venait surtout des mefc: si j'ai un tableau contenant 50 lignes avec des mefc, est-ce que, avec un tableau structuré, la mefc s'appliquera automatiquement à la 51e ligne que je créerais?
20/05/2024, 21h43
Philippe Tulliez
Bonjour,

Citation:

Il n'y a pas de chance que je déplace la feuille (les onglets seront de toutes façons masqués). Et il m'est déjà arrivé de changer le nom de la feuille, ce qui complique aussi les choses... Mais de toutes façons, le terme Worksheets(1) fait toujours référence à la feuille 1, non?

Pour éviter les risques de renommage, j'utilise le CodeName de la feuille (voir lien plus bas).

Citation:

Je n'avais jamais entendu parler de tableaux structurés. J'ai toujours utilisé la fonction DECALER pour calculer la plage exacte, au fur et à mesure qu'elle s'agrandit. A vous lire, je me suis compliqué la tâche... Ma prudence venait surtout des mefc: si j'ai un tableau contenant 50 lignes avec des mefc, est-ce que, avec un tableau structuré, la mefc s'appliquera automatiquement à la 51e ligne que je créerais?

On en parle pratiquement à chaque discussion.
Un tableau structuré n'a que des avantages.
A chaque nouvelle ligne, les formules, les validations de données, les mises en forme conditionnelles sont recopiées automatiquement et d'autres fonctionnalités intéressantes sont proposées. De plus la manipulation des données des tables en VBA en est facilitée (voir liens plus bas).

Quelques liens utiles
20/05/2024, 22h00
Cahlagan

On cherche quelque chose, on trouve autre chose...

En tout cas, merci pour les infos! Je vais m'intéresser de près aux tableaux structurés. A première vue, ils semblent beaucoup plus aisés à manier que ceux que j'ai réalisés jusqu'ici.

Bonne suite à vous
21/05/2024, 17h43
Philippe Tulliez

Bonjour,

Citation:

Je vais m'intéresser de près aux tableaux structurés. A première vue, ils semblent beaucoup plus aisés à manier que ceux que j'ai réalisés jusqu'ici.

Et de plus, si vous utilisez les tableaux structurés (ListObject en VBA), vous pourriez, pour trier vos données, utiliser une fonction générique que j'ai créée et dont j'ai publié la source dans ce billet VBA Excel - Fonction générique - Trier un objet ListObject (Tableau structuré)

Il suffit de lui passer comme argument l'objet table et le nom des étiquettes de colonnes à trier comme SortTable oList:=Range("t_People").ListObject, LabelList:="Ville;Service;-Salaire"
30/05/2024, 11h52
Campomoro2A

Moi aussi j'ai un problème de tri en VBA

Bonjour
Voici le contexte de mon problème.

1 - Présentation du tableau
Mon tableau comprend entre autres les colonnes Date (C), Tiers (D), Notes (E) et Montant (F).
J'ai créé deux macros pour trier mon tableau soit par Date (plus exactement Date, Tiers & Notes) soit par Tiers (Tiers, Date, Notes)
L'encolonnage est en ligne 2. La première ligne Détail est en 3 et la dernière en 90 (pour l'instant, mais il est possible que j'ajoute d'autres lignes plus tard). Il y a ensuite une ligne de totalisation des montants.
Lorsque j'ai créé mes macros, la dernière ligne renseignée était la 31.
Jusque là, quand j'utilise mes macros, les tris se font correctement.

2 - Problème rencontré
Si j'ajoute des lignes Détail (32 à 50 par exemple), elles ne sont pas prises en compte par les macros. Le tri continue de s'effectuer sur les 31 premières lignes.

3 - Macro utilisé
N'étant pas un expert en VBA, j'ai créé mes macros en utilisant l'enregistreur de macros Excel.
Voici ce qui a été généré :

Sub Tri_Tiers()
'
' Tri_Tiers Macro
' Tri par Tiers, Date & Notes
'
Range("A2").Select
ActiveWorkbook.Worksheets("Journal").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Journal").Sort.SortFields.Add2 Key:=Range("D3:D31" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Journal").Sort.SortFields.Add2 Key:=Range("C3:C31" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Journal").Sort.SortFields.Add2 Key:=Range("E3:E31" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Journal").Sort
.SetRange Range("A2:I31")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A3").Select
End Sub

4 - Ma demande
Comment modifier ma macro pour qu'elle fonctionne quelque soit le nombre de lignes ?
Merci pour votre assistance.
30/05/2024, 12h09
Cahlagan

Il y a peut-être mieux, mais pour ma part, lorsque je suis confronté à ce type de problème, je nomme la zone qui sera triée, avec la formule DECALER. De cette manière, lorsque des lignes sont ajoutées (ou supprimées), elles sont prises en compte dans le tri. Petite contrainte: il ne faut pas laisser de ligne vide!

Salut, teste ça:

Code:

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
Sub Tri_Tiers()
 
    Dim ws As Worksheet
    Dim lastRow As Long
 
    Set ws = ActiveWorkbook.Worksheets("Journal")
 
    ' Trouver la dernière ligne utilisée dans la colonne A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
 
    ' Effacer les anciens critères de tri
    ws.Sort.SortFields.Clear
 
    ' Ajouter de nouveaux critères de tri
    ws.Sort.SortFields.Add2 Key:=ws.Range("D3:D" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ws.Sort.SortFields.Add2 Key:=ws.Range("C3:C" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ws.Sort.SortFields.Add2 Key:=ws.Range("E3:E" & lastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
 
    ' Définir la plage de tri
    With ws.Sort
        .SetRange ws.Range("A2:I" & lastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
 
      ws.Range("A3").Select
End Sub

30/05/2024, 13h45
Campomoro2A

Merci à tous deux de vos réponses rapides.

J'ai appliqué la solution de Franc. Cela marche parfaitement.

Bonne journée
30/05/2024, 14h49
deedolith

Salut,

Je te conseilles vivement de passer par des tableaux structurés.
Ils offrent de multiples avantages, à mon sens supérieur aux plages nommées, ou plages dont la taille est calculée par code.