Discussion :

[Tekiila_67]

Bonjour à tous,

Je me débrouille un petit peu en VBA, du moins je cherche par moi-même et c'est pourquoi parfois j'ai un code qui est fonctionnel mais pas optimal...
Je vais tenter de vous expliquer rapidement mon souci.

Je dois gérer des arrêts techniques dans mon travail, et nous avons donc des BT (bons de travail) qui se génèrent périodiquement dans une GMAO (Gestion de maintenance assistée par ordinateur), une base de données en gros.
Nous pouvons extraire les données sous format Excel afin de travailler avec comme on le souhaite, ce que je fais pour planifier les différents BT, ajouter infos, etc.
Lors d'un changement d'état, c'est-à-dire passage de "A réaliser" à "En cours" par exemple, il fallait fouiller dans la GMAO pour trouver où en était le BT.
A savoir qu'il y a environ 2000 BT/an, donc c'est relativement em...........

J'ai donc pensé générer tous les jours une extraction de la GMAO, et créer un VBA qui va me mettre à jour les états.
Cela se passe comme ça : Import de la nouvelle extraction ajoutée à la suite de la dernière, et ensuite, 2 boucles imbriquées qui viennent prendre la première ligne de l'ancien tableau, pour la comparer à la première ligne du nouveau tableau, puis à la deuxième, etc... Jusqu'à trouver une correspondance : Si le code trouve deux lignes identiques (par rapport au numéro de BT), il supprime l'ancienne, donc il me garde celle avec le nouvel état, donc à jour. S'il ne trouve pas de deuxième ligne, il laisse, s'il ne trouve qu'une nouvelle ligne, il l'ajoute, c'est un nouveau BT qui a été ajouté en GMAO.

Le souci que j'ai, c'est qu'à l'import je me trouve avec donc 2x 2000 BT (En ce moment même 4x car je reviens sur 2018), donc le programme met une eternité à avancer.
Je suis quasi sur que ma méthode n'est pas la meilleure, car il va rechercher TOUTES les lignes, pour avoir 99% de non trouvé -> Inutile, mais je ne suis pas un pro et sur quelques lignes ça fonctionne bien.

Auriez-vous une idée de comment procéder pour accelérer ça ? Partir d'un RECHERCHEV par exemple ?

Je pourrais juste utiliser la nouvelle extraction me direz-vous, mais le problème est que je planifie avec ce fichier, donc je marque par exemple une date dans les colonnes suivantes, et avec mon programme il va récupérer les infos, et les remettre aux bons endroits sur la nouvelle ligne à chaque fois...

Merci d'avance de votre aide.

[mfoxy]

Bonjour,

À la lecture de ton descriptif, il semble que tes Bt ont le même id.

Au lieu d effectuer des boucles imbriquées, pourquoi ne pas simplement supprimer les lignes en doublons en fonction de l id ?

Exemple à test sur une copie

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
Sub RemoveDuplicateRows()
 
Dim MyRange As Range
Dim LastRow As Long
 
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
' modifie range ("a1:d...) par ta plage de cellule
Set MyRange = ActiveSheet.Range("A1:D" & LastRow)
' modifier colums =3 par le num de colonne avec id bt
 
MyRange.RemoveDuplicates Columns:=3, Header:=xlYes
 
End Sub

[Tekiila_67]

Bonjour mfoxy,

Alors je n'ai pas fait ça pour deux raisons :
Premièrement je ne sais pas s'il va bien supprimer la dernière. Ca se teste rapidement, je vais faire ça en utiliser tout simplement l'option "supprimer les doublons".
Deuxièmement, car il va m'enlever aussi d'autres informations.

On prend un exemple :
Col A : ID ; Col B : Titre du BT ; Col D : Etat ; Col C : Planif

Les colonnes A, B et C sont issues de la GMAO et sont donc récupérées automatiquement par le début de mon programme qui fait un simple copier/coller.
La colonne D en revanche, est une donnée que j'entre manuellement et qui me permet de savoir à quelle date est planifié ce BT.
Si je fais "supprimer les doublons" et qu'il supprime bien la plus ancienne, soit la première des deux, il va aussi supprimer les infos de la colonne D, et à la fin du programme j'aurais donc bien l'état à jour, mais plus la date de planification ...

De plus, imaginons que je puisse dire de supprimer les doublons juste sur les colonnes A,B et C en laissant D, un moment le nombre de BT va bouger et la colonne D aura des infos pas forcément au bon endroit...

Je ne sais pas si c'est assez clair.

[mfoxy]

Essaie toujours sur une copie de ton fichier, en sélectionnant bien tout le tableau, et uniquement les critères en double.

En guise de test, si nok, il sera alors opportun de faire un code avec boucle, tbl()...

[Tekiila_67]

J'essaye ça ce soir ou demain, et je te tiens au courant

Merci pour tes réponses !

[Tekiila_67]

Bonjour,

Je viens de faire le test, et effectivement ça ne fonctionne pas car il me supprime le dernier de la liste.
Par exemple :

• 100520 - Réparation pompe - En cours - AT20
• 100520 - Réparation pompe - Soldé

Le premier cas étant le tableau numéro 1 dans lequel le BT est en cours. J'ai planifié de faire cette intervention à l'arrêt technique 2020, d'où "AT20", donnée entrée manuellement.
La deuxième ligne est l'extraction du jour, les données sont les mêmes hormis l'état. Je veux donc bien voir que celui-ci a évolué. Il n'y a pas AT20 car c'est une donnée manuelle.

Après la suppression des doublons, il me laisse la ligne "En cours", ce n'est pas ce que je veux. Certes j'ai AT20, mais l'état n'est plus à jour.
SI ça avait fonctionné comme je le souhaite, qu'il ne garde la dernière ligne uniquement, j'aurais l'état à jour mais plus AT20, ce n'est pas non plus ce que je veux.

Donc je pense que les boucles sont la seule solution.

Pour info, j'arrive à la ligne 9000 et même un peu plus, donc je vous laisse imaginer le nombre d'opération que le VBA doit faire, je pense qu'il mettra plus qu'un jour à tout faire ^^

Dois-je tenter de poster mon code ? Quelqu'un y trouvera probablement des choses mal écrites, ou écrites de façon non optimale

[halaster08]

Bonjour,

Après la suppression des doublons, il me laisse la ligne "En cours", ce n'est pas ce que je veux. Certes j'ai AT20, mais l'état n'est plus à jour.
SI ça avait fonctionné comme je le souhaite, qu'il ne garde la dernière ligne uniquement, j'aurais l'état à jour mais plus AT20, ce n'est pas non plus ce que je veux.

Une proposition:
-Duplique ta feuille
-sur une feuille inverse le tri, puis supprime les doublons: tu auras l'état à jour mais plus AT20
-sur l'autre feuille supprime toute les lignes où la colonne ( D ?) contenant le AT20 est vide
-sur la première feuille fait une rechercheV pour récupérer l'info AT20, si tu as besoin de plusieurs colonnes pour identifier une ligne rajoute une colonne qui concatène les infos ça te fera un identifiant unique

[Tekiila_67]

Bonjour halaster08,

Un problème se pose en faisant ce que tu me proposes : le tri. Celui-ci se fait par une colonne où il y a des dates.

Je suis dans l'industrie pharmaceutique, et nous avons nos BT à réaliser dans une période précise, d'où ce fichier qui me tri par date pour orienter ma planification.
Actuellement j'ai donc un tableau de 2000 BT, et mon code va en premier lieu récupérer le tableau du jour avec les 2000+ BT et le mettre à la suite du premier.

Le tri se fait par date de début, issue de la GMAO, donc en gros un BT qui peut être en ligne 3000, se retrouvera peut-être en ligne 15 si sa date de début est la plus proche de celle du jour.
Je ne suis pas forcément très clair dans mes propos ...

En revanche ta proposition me donne des idées : L'import se fait automatiquement grâce à la date du jour, donc le fichier de données se nomme "Extr 20200123" pour aujourd'hui.
Je peux ajouter cette donnée à la suite de chaque ligne, pour différencier l'extraction du jour à celle passée, soit celle d'hier.
Ensuite inverser le tri
Récupérer les données "AT20" là où elles manquent
Supprimer les doublons

Logiquement je devrais avoir l'état à jour puisqu'il supprime le dernier, donc les anciennes extractions, et la donnée "AT20" sera récupérée à l'étape d'avant.

Petite question : la fonction Recherchev fonctionne-t-elle dans le même tableau ? Par exemple si en ligne 15 j'ai un BT non à jour, et en ligne 3500 j'ai le même BT mais à jour, est-ce que la Recherchev peut me remettre le "AT20" de la ligne 15 en ligne 3500 ?

[halaster08]

Je ne suis pas forcément très clair dans mes propos ...

Je confirme. Sans avoir de visuel c'est parfois compliqué de suivre, une capture d'écran pourrait aider

la fonction Recherchev fonctionne-t-elle dans le même tableau ? Par exemple si en ligne 15 j'ai un BT non à jour, et en ligne 3500 j'ai le même BT mais à jour, est-ce que la Recherchev peut me remettre le "AT20" de la ligne 15 en ligne 3500 ?

Ca me parait plus judicieux de le faire dans un autre tableau.
La fonction rechercheV donne le premier résultat qu'elle trouve dans ton tableau, c'est pour ça que je t'avais proposé de dupliquer la feuille et de supprimer les lignes où l'info que tu souhaites rajouter est manquante, de cette manière tu es sûr que la recherchev renvoie le résultat attendu

[Tekiila_67]

EDIT suite à bug dans les fichiers.

Bonjour halaster08,

Je joins une capture d'écran de mon fichier excel, du moins des colonnes dont je parle. Celui-ci est bien plus grand que dans mes exemples évidemment, mais le principe est le même.



Je n'ai pas eu le temps de travailler sur le sujet en fin de semaine car j'ai aussi l'embrayage de ma voiture à faire en même temps et justement les BT à traiter au travail, donc dès que j'ai un peu de temps, demain et après-demain logiquement, je me penche dessus.

En revanche, en faisant un essai, effectivement j'arrive à faire fonctionner mon excel comme je le désire et avec Recherchev sur le même tableau, mais il faut automatiquement supprimer la première ligne sinon il reprend directement cette ligne. Autant faire comme tu dis, passer par un autre tableau et faire ça proprement, sans même avoir besoin de supprimer la première ligne puisque ce sera dans ce tableau qu'il devra rechercher.

J'essaierai ça, et te (vous) tiens au courant.

[Tekiila_67]

Bonjour,

Aujourd'hui j'ai pu modifié un peu le code.

Actuellement j'en suis à ça :
- Création d'un onglet "Traitement"
- Ouverture du excel d'extraction du jour
- Copie du tableau pour le mettre dans "Traitement"
- Utilisation de la fonction Recherchev

Je bloque sur ce dernier point car je voudrais que le code fasse cette fonction sur toute la plage, et pas uniquement sur une cellule. D'autant plus que la syntaxe est un peu spéciale j'ai l'impression car il faut l'écrire en R1C1 ?
J'ai vu plein de topics différents à ce sujet, et aucun ne dit la même chose. J'ai essayé avec l'enregistreur mais il ne prend à chaque fois qu'une ligne. J'ai donc essayé avec une boucle For mais il m'incrémente aussi les arguments...

Quelqu'un aurait une idée de la syntaxe ou méthode pour éxecuter cette fonction correctement ?

Je vous joins une capture d'écran du tableau actuel et des onglets



Je voudrais mettre en U1 de l'onglet "Traitement", la cellule U1 de l'onglet "DATA", evidemment en correspondance avec le numéro BT qui est dans la cellule A. C'est cette fameuse ligne planif dont on parle depuis le début.

[Tekiila_67]

Bonjour,

J'ai tenté par une autre fonction, avec deux boucles For imbriquées et cela fonctionne mais prend quand même un certain temps.
C'est logique puisque dans ma boucle il va devoir comparer un tableau à l'autre, et même une fois la valeur trouvée, il continuera de chercher dans le reste du tableau.
Ce n'est pas très logique, mais c'est la seule fonction qui tourne correctement pour l'instant. Je ne maitrise pas les "Do, while, until" mais je pense que ça pourrait faire gagner du temps.

Ou encore mieux, réussir à faire fonctionner la recherchev...

Voici le code actuel

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
For i = 1 To DerLign  'Dernière ligne de l'onglet "Traitement"
    For j = 2 To DernLign  'Dernière ligne de l'onglet "DATA"
        If Sheets("Traitement").Range("A" & i).Value = Sheets("DATA").Range("A" & j).Value Then
            Sheets("Traitement").Range("U" & i).Value = Sheets("DATA").Range("U" & j).Value
        End If
        Application.StatusBar = "Ligne i = " & i & "; Ligne j = " & j  'Me permet de voir où ça en est.
    Next j
Next i

Ce code met environ un peu moins de 10mn. Mais même en faisant une recherchev manuellement je fais bien plus vite...

[halaster08]

Je ne comprends pas quel est ton problème avec la rechercheV ?
Y arrive tu à le faire sans vba ? si oui quelle est la formule exacte que tu as utilisé ? sur quel plage souhaite tu copier la formule ?

[Tekiila_67]

Oui sans VBA j'arrive à l'utiliser.
J'ai utilisé cette formule là en cellule U1 dans l'onglet "Traitement" : RECHERCHEV(A1;DATA!A2:AA4913;21;FAUX).

J'ai pris A1 en premier argument car dans l'onglet "Traitement" il n'y a pas de titre ni rien, uniquement les données, et le numéro de BT se trouve en colonne A.
La plage où excel doit chercher les infos est sur l'onglet DATA, et j'ai pris A2:AA4913 car j'ai des titres, d'où le début en ligne 2, et ça va jusqu'à AA car j'ai pris toutes les colonnes, et 4913 car pour mon essai il fallait bien que je prenne une plage avec une fin.
21 représente la colonne U, celle dans laquelle se trouve ma donné à copier : "ATH20" par exemple.

Une fois que ça fonctionne sur la cellule U1, j'ai tiré la formule jusqu'à la fin du tableau.

Mon souci est qu'en VBA je n'arrive pas à l'écrire, et avec l'enregistreur il me garde les valeurs comme 4913, tandis que moi j'ai une variable mais je ne sais pas comment l'utiliser ici.
De plus, comment tirer ma formule en VBA ?
Si j'utilises une boucle, il fera augmenter du même nombre mes valeurs, ce qui n'est pas ce que je souhaite.

[halaster08]

J'ai utilisé cette formule là en cellule U1 dans l'onglet "Traitement" : RECHERCHEV(A1;DATA!A2:AA4913;21;FAUX)

en supposant que ta dernière ligne est la même sur tes deux feuilles (sinon tu utilise deux variables différentes)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
dernière_linge = 4913
sheets("traitement").Range("u1:u" & dernière_linge).Formula = "=vlookup(A1,DATA!A2:AA" & dernière_linge & " ,21,false)"

[Tekiila_67]

Je vais essayer ta solution.

Sinon oui effectivement, j'ai bien deux variables différentes car les deux tableaux n'ont pas le même nombre de lignes.

[Tekiila_67]

Je viens de tester, et il utilise bien recherchev sur toute la colonne U, en revanche il me fait le même souci que j'avais au départ, c'est qu'il incrémente toutes les valeurs.

En U1, j'ai bien le recherchev initial : l'argument est bon, puisqu'il est lié à la ligne, donc il doit s'incrémenter dès qu'on descend d'une ligne.
En revanche, à la ligne suivante, au lieu de chercher dans DATA!A2:AA4912, il cherche dans DATA!A3:AA4913, et ainsi de suite.

Sachant que le 4912 est issu d'une variable.

Peut-on bloquer la table matrice ? Comme lorsqu'on met les $ ?

En tout cas on s'approche de ce que je souhaite faire, et je te remercie déjà de ton aide !

EDIT : J'ai trouvé comment bloquer, c'était simple en fait, vu que ce n'est pas du R1C1 il suffit de faire comme lorsqu'on n'est pas en VBA, avec les $. En revanche, je ne sais pas trop où le mettre avec la variable ...

EDIT2 : C'est bon, on peut rajouter un & "$" entre le nom de colonne et la variable et ça fonctionne.

Il me reste à trouver comment traiter les 0 qui apparaissent lorsqu'il y a un nouveau BT et que la fonction ne trouve donc rien.

[Tekiila_67]

Bonjour,

Je suis arrivé à écrire tout mon code comme je le souhaite, cela fonctionne désormais correctement, en utilisant parfois quelques petits subterfuges, mais tout est ok.

Le problème principal étant un code VBA trop long, il est désormais de même pas 10s avec cette autre méthode.

J'ai par contre un autre souci : Lorsque je le lance en pas à pas ça fonctionne bien, mais quand je lance toute la macro il me rend un fichier non aligné, non trié, les infos ne sont pas présentes, etc...
En pas à pas j'ai remarqué une chose, c'est que lorsqu'il est à l'étape de recherche du fichier d'extraction dans mon répertoire, il met 2-3 secondes à l'ouvrir, et il marque "Impossible d'effectuer ceci en mode arrêt" quelque chose du genre. Je dois faire Entrée 2x et tout fonctionne par la suite.
Et si en pas à pas j'appuie sur F8 assez rapidement, il saute quelques étapes aussi.

Est-ce cette ouverture de fichier qui peut bloquer ? Je veux dire que les 2-3s qu'il utilise pour ouvrir le fichier sont trop longues et qu'il continue la macro sans avoir ouvert le fichier ? Et du coup sans pouvoir faire quoi que ce soit ?

[Tekiila_67]

Bonjour à tous,

J'ai trouvé comment résoudre mon problème. Je pensais qu'en gros le code "n'attendait" pas l'ouverture du fichier, mais en fait si.
C'était un simple .Select qui ne voulait pas fonctionner correctement.

Le programme met à jour, en couleur, ordonne, tri, fait tout ce que je demande, en 7s

Je vous remercie donc pour votre aide @halaster08 et @mfoxy !

Je passe en résolu, et vous souhaite une bonne journée !