Créer une liste de tri avec + de 255 caractères
Bonjour à tous !
"" Petite présentation de mon activité, mon problème et moi : je suis salarié d'une entreprise exploitant une très vieille GMAO (Gestion de la Maintenance Assistée par Ordinateur) qui dans mon cas me sert à établir le plan de maintenance préventif des différentes machines du parc. Tout les matins, un rapport (fichier Excel) contenant des milliers de lignes est extrait du logiciel GMAO. Seulement, ce fichier étant illisible, je souhaite à partir d'Excel créer une version amélioré de ce plan de maintenance en "temps réel" (avec des graphiques, courbes, indicateurs, ...), pour des questions de lisibilités pour nos collaborateurs de terrains via des écrans d'affichages dans les ateliers. (Ce n'est que temporaire, une nouvelle GMAO tout intégrée devrait se déployer d'ici quelques années) ""
Voilà c'était le contexte du sujet, si ça vous intéresse je pourrais vous donner plus de détails :)
Pour en venir au problème :
Dans la gestion de mon parc machine (après avoir filtré mon fichier machine par machine), je dois trier les éléments suivant un ordre précis. Par exemple, pour la première machine, je dois trier selon cet ordre :
SMX-JOURNALIER |
SMX-HEBDOMADAIRE |
SMX-MENSUEL |
SMX-MENSUEL2 |
SMX-MENSUEL-DEP |
SMX-TRIMESTRIEL1 |
SMX-TRIMESTRIEL2 |
SMX-TRIMESTRIEL3 |
SMX-TRIMESTRIEL4 |
SMX-TRIMESTRIEL5 |
SMX-SEMESTRIEL1 |
SMX-SEMESTRIEL2 |
SMX-SEMESTRIEL3 |
SMX-SEMESTRIEL4 |
SMX-SEMESTRIEL5 |
SMX-SEMEST-DEP |
SMX-ANNUEL1 |
SMX-ANNUEL2 |
SMX-ANNUEL3 |
SMX-ANNUEL4 |
SMX-ANNUEL5 |
SMX-ANNUEL6 |
SMX-ANNUEL7 |
SMX-ANNUEL8 |
SMX-ANNUEL9 |
SMX-ANNUEL10 |
SMX-BIENNAL1 |
SMX-BIENNAL2 |
SMX-BIENNAL3 |
SMX-QUAD-ANNUEL1 |
SMX-QUAD-ANNUEL2 |
SMX-QUAD-ANNUEL3 |
SMX-QUAD-ANNUEL4 |
SMX-QUINQUENNAL |
SMX-QUINQUENNAL2 |
SMX-P10-1 |
SMX-P10-2 |
Seulement Excel est bloqué à 255 caractères et je n'arrive pas à contourner ce problème. Sachant que ceci n'est que pour une machine, je dispose encore de pas mal de tri à faire dans le même genre (qui ne sont pas tous identiques). De plus, j'essaye de réaliser un code VBA générique où les changements seront pratiquement minimes voir inexistants entre chaque rapport pour chaque machine.
Voici ce que j'avais commencé à faire (ne marche pas) :
Sub trier(N As String, FP As Worksheet)
'Feuille de paramétrage (contenant le code VBA) pour exploitation du rapport
FP.Activate
FP.Range("B19:B55").Select 'Plage où il y a la liste de tri dans l'ordre
Application.AddCustomList ListArray:=Range("B19:B55")
'Feuille où il y a le rapport (N correspond au Nom du classeur précédemment inséré)
Workbooks(N).Activate
Workbooks(N).Worksheets(1).Activate
Columns("A:P").Select
Workbooks(N).Worksheets(1).Sort.SortFields.Clear
Workbooks(N).Worksheets(1).Sort.SortFields.Add2 Key:=Range("D2:D65536"), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
CustomOrder:= _
"SMX-JOURNALIER,SMX-HEBDOMADAIRE,SMX-MENSUEL,SMX-MENSUEL2,SMX-MENSUEL-DEP,SMX-TRIMESTRIEL1" & _
"SMX-TRIMESTRIEL2,SMX-TRIMESTRIEL3,SMX-TRIMESTRIEL4,SMX-TRIMESTRIEL5,SMX-SEMESTRIEL1,SMX-SEMESTRIEL2" & _
"SMX-SEMESTRIEL3,SMX-SEMESTRIEL4,SMX-SEMESTRIEL5,SMX-SEMEST-DEP,SMX-ANNUEL1,SMX-ANNUEL2,SMX-ANNUEL3" & _
"SMX-ANNUEL4,SMX-ANNUEL5,SMX-ANNUEL6,SMX-ANNUEL7,SMX-ANNUEL8,SMX-ANNUEL9,SMX-ANNUEL10,SMX-BIENNAL1" & _
"SMX-BIENNAL2,SMX-BIENNAL3,SMX-QUAD-ANNUEL1,SMX-QUAD-ANNUEL2,SMX-QUAD-ANNUEL3,SMX-QUAD-ANNUEL4" & _
"SMX-QUINQUENNAL,SMX-QUINQUENNAL2,SMX-P10-1,SMX-P10-2" _
, DataOption:=xlSortNormal
With Workbooks(N).Worksheets("Rapport1").Sort
.SetRange Range("A2670:M65536")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
J'espère avoir été clair et que vous pourrez m'aider dans mon problème. Je ne peux pas partager les fichiers pour des raisons de confidentialités liés à l'entreprise.
Merci pour votre aide, ou vos conseils.
Bonjour limp_63, le forum,
Vois ci ceci peut t'aider....
source: https://contexturesblog.com/archives/2010/12/15/excel-autofilter-with-criteria-in-a-range/
Cordialement,
Bonjour @xorsankukai
Merci pour votre réponse rapide :)
Votre fichier me permet d'appliquer des filtres alors que je souhaite faire un tri selon un ordre défini. Pour ma pars, le côté filtre fonctionne et ne prend pas en compte la limite de caractère.
C'est bien la liste de tri qui me pose problème ...
Peut-être y à t'il dans votre fichier une astuce pour faire de même avec le tri ? Si oui, pouvez-vous m'expliquer succinctement celle-ci ?
Je suis allé voir le code en question et je ne suis pas trop à l'aise avec le type Variant.
Cordialement,
Bonjour Itmp_63, Xorsankukai,
Quelle limite de 255 caractères parles tu? Inscris Type en 1ière ligne d'un colonne et sur la 2ième ligne de cette même colonne.
=REPT("MARIN";52)&REPT("MATIN";52)
Soit 520 caractères. Donc une différence à partir du 262 caractères
Puis sur la 3ième ligne de cette même colonne. Ou tu peux appliquer une couleur de police rouge pour la remarquer du texte précédent.
=REPT("MARIN";104)
Tu n'auras aucun souci de tri (A>Z) ou (Z>A)
Donc à préciser... Rends anonyme une de tes sorties (en plaçant n'importe quelles données) et poste le fichier.
A suivre...
Bonjour à tous
PowerQuery intégré à ta version Excel pourrait sans doute servir ici.
Il t'aiderait à nettoyer la source et trier sans problème
Peux tu joindre un exemple du fichier source et un exemple de résultat voulu.
Re @xorsankukai ,
Oui vous avez raison c'est tout à fait mon problème
Bonjour @78chris,
PowerQuery intégré à ta version Excel pourrait sans doute servir ici. -> Je vais regarder. Je pense que cela risque d'être compliqué vu qu'il s'agit d'un PC pro avec des accès restreint (je ferais la demande auprès de l'administrateur).
Bonjour @X Cellus,
Rends anonyme une de tes sorties (en plaçant n'importe quelles données) et poste le fichier. -> Qu'entendez vous pas anonyme ?
Fichier source simplifié :
Fichier de paramétrage :
Merci pour votre aide précieuse :)
Cordialement,
A nouveau,
Tri effectué selon la liste ordonnée transmise. Est-ce le tri que tu souhaites obtenir?
Sub TRIER()
Lig = 2
Application.ScreenUpdating = False
For T = 2 To 38
Windows("parametres.xlsm").Activate
Cible = Range("A" & T) 'Range("A2").select
'Range(Selection, Selection.End(xlDown)).Select
'Application.AddCustomList Array(Range("A" & T))
Windows("actions-realisees-prevention.xls").Activate
Columns("A:M").Select
ActiveWorkbook.Worksheets("Rapport1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Rapport1").Sort.SortFields.Add2 Key:=Range("D" & Lig & ":D3740"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
Cible & "", DataOption:=xlSortNormal
' "SMX-JOURNALIER,SMX-HEBDOMADAIRE,SMX-MENSUEL,SMX-MENSUEL2,SMX-MENSUEL-DEP,SMX-TRIMESTRIEL1,SMX-TRIMESTRIEL2,SMX-TRIMESTRIEL3,SMX-TRIMESTRIEL4,SMX-TRIMESTRIEL5,SMX-SEMESTRIEL1,SMX-SEMESTRIEL2,SMX-SEMESTRIEL3,SMX-SEMESTRIEL4,SMX-SEMESTRIEL5,SMX-SEMEST-DEP,SMX-ANNUEL1,SMX-ANNUEL2,SMX-ANNUEL3,SMX-ANNUEL4,SMX-ANNUEL5,SMX-ANNUEL6,SMX-ANNUEL7,SMX-ANNUEL8,SMX-ANNUEL9,SMX-ANN" & _
' "UEL10,SMX-BIENNAL1,SMX-BIENNAL2,SMX-BIENNAL3,SMX-QUAD-ANNUEL1,SMX-QUAD-ANNUEL2,SMX-QUAD-ANNUEL3,SMX-QUAD-ANNUEL4,SMX-QUINQUENNAL,SMX-QUINQUENNAL2,SMX-P10-1,SMX-P10-2" & _
' "", DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Rapport1").Sort
.SetRange Range("A" & Lig & ":K3740")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
For C = 3740 To 2 Step -1
If Range("D" & C) Like Cible Then Lig = C: Exit For
Next C
Next T
Application.ScreenUpdating = True
End Sub
A suivre...
Bonjour,
Une proposition Power Query, comme suggéré par 78chris.
Cdlt.
Nota: @78chris,
Je t'ai mis un vote négatif à l'insu de mon plein gré !...
Bonjour
Re @xorsankukai ,
Bonjour @78chris,PowerQuery intégré à ta version Excel pourrait sans doute servir ici. -> Je vais regarder. Je pense que cela risque d'être compliqué vu qu'il s'agit d'un PC pro avec des accès restreint (je ferais la demande auprès de l'administrateur).
Intégré cela veut dire que c'est déjà dedans sauf s'ils ont forcé à l'installation pour conserver l'ancienne gestion de données.
Jean-Eric a donné un exemple.
Comme on ne sait pas les autres actions que tu fais entre le fichier brut et le résultat on ne peut aller plus loin dans la démarche mais il est quasi certain que tout peut être fait en requête
Bonjour
Nota: @78chris,Je t'ai mis un vote négatif à l'insu de mon plein gré !...
Même pas vu. Je crois que je m'en remettrais
Bonjour à tous !
Merci pour toutes vos réponses
@X Cellus votre code marche et c'est exactement le tri que je souhaite réaliser merci. De plus j'ai l'impression qu'il s'agit d'un code assez générique que je pourrais réutiliser pour les autres machines. Petit bémol cependant (sinon ça ne serait pas drôle), la ligne 2 n'est pas trié.
@Jean-Eric je comprend mieux avec votre fichier l'utilité de Power Query et ça à l'air de fonctionner. Merci à vous deux avec @78chris.
Je vais essayer de décrire ci dessous le processus total que j’attends (avec des captures d'écrans)
Etape 1 : A l'aide de mon fichier Excel paramètres, j'ouvre à partir du réseau usine le Rapport extrait de la GMAO (ACTION REALISEES Préventif.xls)
Etape 2 : Création d'une sauvegarde des fichiers avant modifications dans un dossier spécifique avec date de modification
Etape 3 : Applique un filtre en fonction de l'équipement concerné (dans l'exemple ici 1539)
Etape 4 : Deuxième filtre où je supprime de l'affichage le préventif journalier (lié à la production et systématique -> Pas pertinent pour la suite)
Etape 5 : Je rajoute 2 colonnes (Une où je converti la date prévu de réalisation en N° de semaine)(Une où je calcul l'état de mon OT : 0 ou 1)
Si Etat OT = 0 ça veut dire que l'action est réalisée sinon Etat OT = 1
Etape 6 : Je crée un TCD pour une première mise en forme :
Ce TCD me permet de croiser les données et d'afficher le nom de l'OT avec filtre sur l'équipement (colonne de gauche) en fonction du numéro de semaine (ligne supérieure). L'intérieur du tableau fait la somme pour chaque action de L’État de l'OT. Ainsi si dans le TCD on a un 0 ça veut dire que l'action et réalisée sinon l'action n'est pas terminée.
Étape 7 : trier le rapport préventif (cela sur quoi vous m'aider actuellement
Étape 8 : le TCD tri par ordre alphabétique alors que moi je souhaite conserver le même ordre que sur le rapport
Étape 9 : Je déplace toutes le nom des OT sur un autre tableau Excel (dans description de la tâche) dans l’ordre trié par le TCD (Étape 7) :
Ensuite, pour chaque chiffre (0 ou 1) dans le TCD j'ajoute un coloriage Jaune dans la case K5 (cela représente la périodicité des actions). En gros ça affichera qu'une action de préventif est prévu pour telle ou telle semaine.
Ensuite, j'affiche un coloriage noir de la case K6 pour chaque 0 dans le TCD. Cela veut dire que l'action est réalisée.
Les deux autres cases serviront par la suite (Une case en rouge L5 en cas de panne machine lors de la maintenance -> un bouton sera ajouté par la suite uniquement disponible pour le responsable)
Je sais pas si j'ai été très clair c'est pas évident de vous expliquer tout ça avec les mains... Si vraiment je bloque trop je créerai des versions "bidons" de mes fichiers pour pouvoir vous les partager
En tout cas merci à tous pour votre implication, votre aide mes précieuses.
Cordialement,
Bonjour Itmp_63, le Forum,
En fait j'ai déplacé une ligne de code en début de macro et mal copié. C'est Lig = 1 qu'il faut prendre.
La boucle, elle, commence bien à 2, soit le début de liste du fichier paramêtres. Changer aussi le 3740 (ici adapté à l'exemple) lorsque le nombre de lignes différent.
Sub TRIER()
Lig = 1
Application.ScreenUpdating = False
Re X Cellus merci pour votre retour,
Ci dessous, j'ai essayé d'adapter votre code à mon fichier original mais cela laisse encore à désirer, pourriez vous jeter un petit coup d’œil s'il vous plaît ?
Sub trier(N As String, FP As Worksheet)
Application.ScreenUpdating = False
Dim down As Integer 'représente la longueur de la liste de tri en nb de cellules
Dim all As Long 'représente la longueur des données à trier en nb de cellules
Lig = 1
down = FP.Range("B19").SpecialCells(xlCellTypeLastCell).Row 'Renvoi la dernière cellule non vide de la liste
For T = 19 To down Step 1
Cible = FP.Range("B" & T) 'Première cellule de la liste de tri B19 jusqu'a "down"
Workbooks(N).Worksheets(1).Activate
all = Workbooks(N).Worksheets(1).Range("A1").SpecialCells(xlCellTypeLastCell).Row 'Renvoi la dernière cellule
Columns("A:M").Select
Workbooks(N).Worksheets(1).Sort.SortFields.Clear
Workbooks(N).Worksheets(1).Sort.SortFields.Add2 Key:=Range("D" & Lig & ":D" & all), _
SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
Cible & "", DataOption:=xlSortNormal
With Workbooks(N).Worksheets(1).Sort
.SetRange Range("A" & Lig & ":K" & all)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
For C = all To 2 Step -1
If Workbooks(N).Worksheets(1).Range("D" & C) Like Cible Then Lig = C: Exit For
Next C
Next T
Application.ScreenUpdating = True
End Sub
Le "down" fonctionne et prend bien la valeur 36. Cependant le "all" ne fonctionne pas du tout (avec Integer il me dit qu'il n'a pas assez de mémoire) ?
Ducoup le tri n'est plus effectué
Cordialement,
A nouveau,
Sub TRIER()
Lig = 1 'Au départ Lig doit être égal à 1 et non 2
Application.ScreenUpdating = False
LigList = Range("A" & Rows.Count).End(xlUp).Row
LigAct = Workbooks("actions-realisees-prevention.xls").Sheets("Rapport1").Range("A" & Rows.Count).End(xlUp).Row
For T = 2 To LigList
Windows("parametres.xlsm").Activate
Cible = Range("A" & T)
Windows("actions-realisees-prevention.xls").Activate
Columns("A:K").Select
ActiveWorkbook.Worksheets("Rapport1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Rapport1").Sort.SortFields.Add2 Key:=Range("D" & Lig & ":D" & LigAct), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
Cible & "", DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Rapport1").Sort
.SetRange Range("A" & Lig & ":K" & LigAct)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
For C = LigAct To 2 Step -1
If Range("D" & C) Like Cible Then Lig = C: Exit For
Next C
Next T
Application.ScreenUpdating = True
End Sub
Voici le code pour s'adapter à toute feuille. Changer uniquement les noms des fichiers ou feuilles concernées. Ou les variabiliser.
Bonne continuation
Bonjour @X Cellus, le forum !
Désolé pour la réponse tardive je n'avais pas eu le temps de regarder le code de plus près.
C'est chose faite ! Votre code fonctionne parfaitement. Cela va beaucoup m'aider, je vous en remercie.
J'avais un petit problème mais j'ai trouvé : le tri ne s’effectue pas si l'on applique un filtre avant sur la sélection, du coup j'ai changé l’ordre des opérations et ça marche
Maintenant me reste plus qu'à faire pareil avec le TCD !
Je vais chercher un peu par moi même avant, si je ne trouve pas je reviendrez vers vous sur un autre Topic.
Merci à tous ! A bientôt