VBA analyse multicritères d'un tableau

Bonjour,

Novice en VBA, je bloque sur la mise en forme d’un tableau d’analyse (cf fichier en pj).

Je souhaite brasser ces données de plusieurs manières mais n’ai malheureusement pas trouvé les codes pour y parvenir.

A noter que ce tableau peut avoir une taille très variable suivant la profondeur de l'analyse que l'on souhaite mener (de quelques centaines à plusieurs dizaines de milliers de lignes).

Mes besoins :

1) Supprimer les lignes pour lesquelles il y a une absence de valeur dans la colonne B ou alors une valeur ne respectant pas le format suivant dans la colonne B (1 lettre suivi de 3 chiffres) ; Si la 1ère lettre démarre par un « F » supprimer également la ligne.

2) Supprimer toutes les lignes pour lesquelles se trouve : FSA ou FSM dans la colonne E

3) En cas de doublons ou plus => si plusieurs PSA (colonne E) pour le même n° de SA (colonne B) et pour la même journée calendaire (colonne A) => ne conserver que la PSA la plus tôt (colonne G) et par conséquent supprimer les PSA suivantes (la ligne complète).

4) Une fois l'étape 3 réalisée : définir si PSA réalisée OUI / NON. Argument :

Pour un même Mnémo SA (colonne B) => Si l'heure d'une PSA (colonne G) se trouve être identique à l'heure indiquée pour une PSM ou REL de la même date (colonne A) : indiquer NON dans la colonne I ( même ligne que la PSA dont il est question).

J'ai essayé d'être le plus clair possible, si malgré tout des précisions restent nécessaires malgré le fichier en PJ n'hésitez pas.

Pour le coup je vous en demande beaucoup, mais malheureusement mon niveau en VBA étant ce qu’il est, j’ai vite atteint la limite.

Automatiser ce tri permettrait de me faire gagner des heures d’analyse surtout dans le cas n°3 et n°4 (cas qui ne trouvent pas réponse avec un simple filtre).

Access serait peut-être plus adapté j'en suis conscient mais que ça soit mon équipe ou moi-même nous ne maîtrisons absolument pas l'outil. Excel est de loin l'outil avec lequel nous serons le plus à l'aise pour travailler ponctuellement sur ce type d'extraction.

Merci d'avance pour votre aide.

Emmanuel

Bonjour Spike29 le forum

on suppose que la feuille à traiter c'est la feuille Suivi ????

à te relire

a+

Papou

Bonjour Spike29 le forum

bon alors réponse pour les deux premières conditions, le reste je n'ai pas le temps aujourd'hui le suis de cuisine

si tu as la patience ce sera lundi.

tu ouvres tu cliques tu regardes et tu me redis

a+

Papou

Bonjour Spike29 le forum

bon alors réponse pour les deux premières conditions, le reste je n'ai pas le temps aujourd'hui le suis de cuisine

mais entre deux plats voilà je n'ai pas contrôlé le résultat

tu ouvres tu cliques tu regardes et tu me redis

a+

Papou

Bonjour Spike29 le forum

bon alors réponse pour les deux premières conditions, le reste je n'ai pas le temps aujourd'hui le suis de cuisine

mais entre deux plats voilà je n'ai pas contrôlé le résultat

tu ouvres tu cliques tu regardes et tu me redis

a+

Papou

PS: dans la V2 j'avais oublié les non de la colonne I

Bonjour Paritec,

Un grand merci car malgré la cuisine tu m'as transmis un code qui fonctionne parfaitement !

J'arrive sensiblement au même résultat que mon analyse mano (avec les yeux rouges en moins )

Seule subtilité que j'ai oublié d'énoncer dans mon expression de besoins concernant le point 1 :

1) Supprimer les lignes pour lesquelles il y a une absence de valeur dans la colonne B ou alors une valeur ne respectant pas le format suivant dans la colonne B (1 lettre suivi de 3 chiffres) ; Si la 1ère lettre démarre par un « F » supprimer également la ligne.

Besoin supplémentaire :

Conserver les lignes pour lesquelles il y a un Mnémo SA (colonne B toujours) se terminant avec un "a" minuscules, exemple : H330a

Si jamais je détecte une irrégularité lors de l'assemblage de mes différents bout de codes, je reviendrai vers toi mais en tout cas chapeau bas ça va énormément m'aider.

Bonne journée et bon weekend !

Bonsoir Spike29 le forum

Le point 1 a été respecté et les H250 a bah n’y sont plus car une des conditions c’est d’avoir un format de 1 lettre et 3 chiffres en colonne B donc H250a c’est une lettre 3 chiffres et une lettre !!!

Je ne suis plus devant mon PC mais je sais ce que j’ai fait ce matin!!!!!

Vérifie et redis ce qui est en trop ou en pas assez

A plus

Papou

Bonsoir Paritec,

Oui, complètement, et tu as parfaitement respecté le besoin initiale que j'avais énoncé en 1), à savoir :

Supprimer les lignes pour lesquelles il y a une absence de valeur dans la colonne B ou alors une valeur ne respectant pas le format suivant dans la colonne B (1 lettre suivi de 3 chiffres) ; Si la 1ère lettre démarre par un « F » supprimer également la ligne.

Toutefois cette après-midi je me suis rendu compte qu'il me fallait également conserver les Mnémo agents de la colonne B ayant une lettre "a" minuscule à la fin Ex : "H330a".

Naturellement la condition initiale qui est de respecter le format : 1 lettre suivi de 3 chiffres vient effacer les lignes pour lesquelles il y a un "a" minuscule dans la colonne B (Mnémo SA).

Du coup, mon besoin est de conserver les conditions du point 1 mais d'y en ajouter une nouvelle à savoir : Si la cellule B contient 1 lettre + 3 chiffres suivi de la lettre "a" (ce qui fait un total de 5 caractères)=> ne pas supprimer la ligne

Ci-dessous le code que tu utilises et dans lequel je ne visualise pas où ni comment intégrer ma nouvelle condition :

If aa(i, 2) <> "" And Not Len(aa(i, 2)) < 4 And Not Len(aa(i, 2)) > 4 And Not aa(i, 2) Like "F*" And Not aa(i, 5) = "FSA" And Not aa(i, 5) = "FSM" Then

For a = 1 To UBound(aa, 2)

bb(n, a) = aa(i, a)

Next a

n = n + 1

End If

Une piste :

A la place de mettre comme critère si la sélection est <4 ou >4 ; indiquer plutôt :

Si la sélection n'est pas comprise entre 4 et 5 caractères en colonne B => Supprimer la ligne

Ce la permettra je pense de répondre à mes deux besoins à savoir de conserver les Mnémo SA qui ne contiennent que 4 caractères ( 1 lettre + 4 chiffres) exemple : H110

Puis idem pour les Mnémo cette fois ci à 5 caractères, 1 lettre + 3 chiffres + lettre "a" en minuscules => H110a

Cette piste te paraît-elle pertinente au vu de mon besoin ?

Si oui, comment coder a condition nécessaire à savoir :

indiquer si la sélection colonne B n'est pas comprise entre 4 et 5 caractères => Supprimer la ligne

Merci d'avance pour ton aide.

Bonne journée

Cordialement,

Emmanuel

Bonjour Spike29 le forum

Cette piste te paraît-elle pertinente au vu de mon besoin ?

bah moi je dirais qu'ayant fait le code dans son intégralité pour répondre à tes demandes, je pense que je vais arriver à modifier mon code pour répondre à ta dernière demande !!! (ne penses-tu pas?)

D'autant que la modification est extrêmement compliqué (lol) tu iras regarder, et tu vas rire je pense, enfin moi j'en

Voilà le fichier modifié, tu testes et tu me redis STP

cette version devrait te convenir.

a+

Papou

Bonsoir Paritec !

J'éviterai à l'avenir d'envoyer des mails en pleine nuit car visiblement ça ne me réussi pas

Merci de ne pas m'avoir renvoyé dans mes 22 car vu ma dernière demande... ^^

Effectivement, un jeu d'enfant.

En tout cas je tenais a te remercier pour ton aide et ta patience ! Ton code va énormément m'aider dans la réalisation de mon fichier d'analyse.

Il me reste quelques bout de code à fignoler avant d'assembler le tout mais j'ai envie de me casser un peu les dents dessus avant d'éventuellement renvoyer une bouteille à la mer ( on apprend mieux comme ça )

Bonne fin de journée et bonne continuation !!

Emmanuel

Bonjour Spike29 le forum

oui tu as raison c'est bien de vouloir faire par soi même, mais si tu ne maîtrises pas le vba, c'est pas toujours simple au début.

Une certitude si tu as besoin, tu fais comme pour ta première demande des explications claires et précises et tu envoies et on va te guider sans problèmes.

Et en cas de doute ou de question pareil tu sais comment faire

a+

Papou

Bonjour PARITEC,

Après plusieurs jours de recherches pour upgrader mon fichier d'analyses sans surprise je sèche.

Et rançon de la performance, je fais appel à tes lumières.

Mon sujet cette fois-ci est étroitement lié aux précédents, raison pour laquelle je le poste dans le fil de nos échanges :

Tu vas rapidement le remarquer, dans la Feuil1(Base de données) il y a plusieurs "Référentiels" à savoir une liste de Mnémo SA avec en face une heure théorique. Au total il y a donc 3 référentiels S3 / S1 / SO.

Ces heures théoriques vont être confrontées aux heures réelles dans la Feuil "Analyse" colonne F.

Ces heures théoriques applicables ne sont pas forcement les mêmes pour l'ensemble des jours calendaires.

Ma difficulté:

Point 1:

Mon traitement de données est mensuel (du 01 au 31). Sur cette liste de jours calendaire je suis susceptible d'appliquer différents référentiels, exemple: ( voir Feuil de calcul "Base de données")

• S0 : Du 01 au 05
• S1 : Du 06 au 16
• S3 : Du 17 au 31

Sur une même date, il n'est possible d'appliquer qu'un seul et unique référentiel.

Plusieurs options qui me viennent à l'esprit :

1) Un formulaire qui s'ouvre au déclenchement de la macro avec en première colonne les 31 jours calendaires puis en seconde colonne des textbox à choix déroulant face à chaque date avec le choix du référentiel a appliquer.

Compliqué et par forcément le plus adapté de mon ressenti.

2) La même logique que le formulaire mais directement sur une des feuils de calcul du fichier ; Un tableau avec deux colonnes, date puis choix du référentiel applicable.

J'ai opté pour cette option dans la Feuil de calcul ("Macro").

Un tableau à deux colonnes avec dans la 1ère les dates de 1 à 31 et dans la 2nd colonne une liste déroulante avec l'ensemble des référentiels existant au choix.

Le référentiel qui sera sélectionné dans la colonne "choix du référentiel" dans la Feuil ("Macro") sera également celui de référence pour cette date dans la Feuil ("Analyse")

En PJ, mon fichier Bdd est en xlsm (j'ai quelque bases de codes).

Dans le module "RechercheVcalendrier" j'ai 3 macro qui font chacune référence aux 3 référentiels présents dans la Feuil ("Base de données") => S0 / S1 / S3

La première macro qui se nomme "RechercheVcalendrier" (un simple bout de code non fonctionnel) est censée faire la liaison entre :

• Le choix du référentiel applicable pour une date (tableau de la Feuil Macro)
• Le type de référentiel applicable (tableau Feuil Base de données)
• La colonne heure théorique (Col "F") puis colonne date (col A) => (tableau Feuil Analyse)

Point 2 :

Ma quantité de référentiels applicable est susceptible d'évoluer avec l'ajout de nouveau référentiels.

Ainsi, depuis la Feuil("Macro") et avec l'utilisation du bouton : "Ajouter nouveau référentiel" je souhaite donner la possibilité aux futurs utilisateurs d'ajouter un nouveau référentiel applicables sans passer par le code source.

Process :

Après avoir saisi manuellement dans la Feuil("base de données") un nouveau référentiel à la suite des autres, l'utilisateur clic sur le bouton "Ajouter nouveau référentiel" (Feuil Macro)

Cela aura pour conséquence de:

• Ajouter une ligne supplémentaire au tableau "Liste des référentiels applicables)
• Etendre la liste déroulante du tableau "référentiels applicables" de +1 pour intégrer le nouveau référentiel
• Définir le nouveau référentiel ajouté manuellement dans la Feuil ("base de données")

Mon fichier sera utilisé par des personnes n'ayant aucunes base VBA, d'où la nécessité d'avoir cette interface présentant le maximum de souplesse dans l'utilisation du fichier.

J'espère avoir été le plus clair possible quant à mon expression de besoins. Si des précisions sont nécessaires n'hésites surtout pas.

Mes fragiles compétences en VBA étant ce qu'elles sont, je n'y arriverai pas sans un autre coup de pouce

Merci d'avance pour ton aide et ta patience.

Bonne journée et bonne fin de weekend !

Emmanuel

Bonjour Spike29 le forum

je n'ai rien compris à ce que tu souhaites, un essai dans le fichier pour la liste des référenciels, mais qui ne conviendra certainement pas, mais bon

a+

Papou

Bonjour PARITEC,

ça ne m'étonnes pas. Vu la demande, il n'est pas évident de la traduire de manière la plus simple possible.

Vu la particularité de ma demande, il va falloir procéder par étapes, je vais fractionner mes besoins pour plus de clarté.

Je vais essayer d'apporter le maximum de précisions :

Mon objectif principal est de pouvoir afficher dans la Feuil " Analyse " colonne F, l'heure théorique selon le Mnémo SA présent en colonne B.

Pour afficher l'heure théorique adéquate, il va falloir aller chercher la bonne information dans la Feuil "Base de donnée".

J'ai déjà une macro qui me permet cela (RechercheVS3) mais cette dernière ne permet d'assurer la rechercheV que d'un seul référentiel à savoir avec la colonne E & F de la Feuil "Base de donnée".

Cette colonne E&F de la Feuil "Base de donnée" représente 1 référentiel ( Le S3).

Le contenu des référentiels se trouve donc dans la Feuil Base de données.

Un référentiel se compose de deux colonnes :

• Mnémo SA
• Heure théorique

Ce référentiel, va servir de base pour ma rechercheV souhaitée à savoir obtenir une heure théorique dans la colonne F de la Feuil Analyse.

Comme tu peux l'observer, il y a 3 Référentiels repris dans la Feuil "Base de donnée" :

S3 => colonne E & F

S1 => colonne G & H

S0 => colonne I & J

Mon problème :

Lorsque tu retourne dans la Feuil Analyse, tu observeras que mon analyse cours du 6 jours (colonne A), du 01.01 au 06.01.

Et bien avec ma macro actuelle (RechercheVS3), je ne peux utiliser que le référentiel S3 (colonne E&F Feuil Base de données) comme élément pour faire apparaître les heures théoriques dans la colonne F de la Feuil "Analyse".

Seulement, pour certaines dates, il se peux que ça soit un autre référentiel qu'il faille appliquer.

Dans la Feuil Analyse, regarde les mnémo SA => Colonne B.

Pour le 01.01 tu pourras noter que les Mnémo SA démarrent par la lettre K.

En retournant dans la Feuil base de données tu notera immédiatement qu'il s'agit donc du référentiel S0 ( colonne I&H).

Du coup, si j'applique "bêtement" ma macro (RechercheVS3) qui elle fait appel au référentiel S3 ( colonne E&F Feuil Base de données), je ne retrouve naturellement aucune heure théoriques dans la colonne F de la Feuil D'analyse car les Mnémo SA ne correspondent pas.

• Ma macro fait appel au référentiel S3 avec des MnéomoSA démarrant avec la lettre H
• Côté Feuil analyse à la date du 01.01, les MnémoSA sont numéroté avec la lettre K

Je pense qu'a ce stade tu vois déjà ou je veux en venir.

J'aimerais pouvoir dire grâce à mon tableau présent dans la Feuil Macro ( colonne B&C) le référentiel qu'il faut utiliser dans la recherchV suivant la date présente dans la colonne A de la Feuil Analyse.

Par exemple :

Pour le 01.01 comme nous l'avons vu précédemment, il faut utiliser le référentiel S0 (colonne I&J Feuil Base de données)

Pour le 02.02 ( les Mnémo SA en col B Feuil analyse sont n° en "G"), donc utiliser le référentiel S1 ((colonne G&H Feuil Base de données)

Puis a partir du 03.01, Mnémo n° en H, appliquer le référentiel S3.

Voilà ci-dessus mon principal besoin.

Je garde mon besoin secondaire de côté.

J'espère avoir réussi a préciser ma demande.

Si des précisions restent nécessaires sur quelques axes n'hésites surtout pas.

En tout cas merci du coup de main pour mes demandes exotiques....

Bonne fin de matinée !

Emmanuel

Bonjour Emmanuel le forum

bon alors ta réponse dans ton fichier joint

Résumé en trois lignes de ta demande

Conditions à respecter:

1) lancer la macro Traiter

2) En fonction de la valeur de la colonne B de la feuille Analyse, chercher dans la feuille Base de Donnée la valeur de cette colonne B

3) Retourner en colonne F de la feuille Analyse l'heure qui est dans la colonne +1 de la valeur trouvée (sur la même ligne que la valeur trouvée)

Tu enregistres le fichier sur ton bureau et tu l'ouvres tu cliques le bouton traiter et tu me redis

Tu as remarqué que la demande raccourcie est compréhensible et qu'elle correspond exactement à ce que tu voulais.

Tu liras bien le pavé texte aussi!!

a+

Papou

Du coup, si j'applique "bêtement" ma macro (RechercheVS3) qui elle fait appel au référentiel S3

PS: je me suis occupé de modifier tes macros avec tes référentiels, maintenant plus aucun soucis!!

Bonsoir Paritec !

Encore une fois, ça fonctionne parfaitement ! Mille merci.

Effectivement bien résumé.

Comme c'est un document dans lequel il n'est pas forcément évident de s'y projeté j'ai préféré peut-être maladroitement d'étayer ma demande (peut-être trop j'en convient ).

Dans mes deux précédents mails, j'avais évoqué un besoin complémentaire (besoin détaillé dans ma première et longue demande du 15.03) :

Celui de définir via le tableau "référentiel" applicable" Feuil 4, le référentiel qui sera applicable pour une date donnée

Exemple : le 01/01/2020 application du référentiel S1(colonnes G:H de la Feuil 1).

Pourquoi cette demande ?

Car il n'est pas exclu à l'avenir que certains de nos référentiels partagent la même lettre.

En anticipation et pour résoudre ce futur problème, je souhaite donner la possibilité à l'utilisateur de définir manuellement dans le tableau "Référentiels applicables" Feuil 4, le bon référentiel à sélectionner à partir de la Feuil 1 " Base de données".

Autre bénéfice, cela permet à l'utilisateur de s'assurer qu'il va bien faire appel au bon référentiel par cet exercice de bouclage.

J'espère avoir été plus clair que dans le passé.

Je te remercie par avance car tes compétences semblent sans limites

Bonne fin de journée

Emmanuel

Re Bonjour Spike29 le forum

Car il n'est pas exclu à l'avenir que certains de nos référentiels partagent la même lettre.

bon alors tu parles de date, tu parles de référentiel, mais mais mais!!!!

1) Ton train il va toujours bien s’appeler avec une lettre et 3 chiffres? (quelque soit la lettre devant)

2) Et quelque soit le référentiel choisi, ce train va toujours avoir une seule heure Théorique???

3) Dans ce cas la recherche de ce train dans la feuille je ne sais plus elle donnera toujours le bon train et donc la bonne heure théorique???

4) la seule chose donc éventuellement à modifier c'est l'analyse sur la ou les dates souhaitées???

Car il est évident, qu'un même train ne peut pas avoir de doublon dans la feuille je ne sais plus le nom!!(ton tableau)

merci de répondre par "oui" "non" ou "tu es à coté de la plaque ", pour chaque question STP

pour avancer il me faut comprendre le fonctionnement et avec tes kilomètres d'explications malheureusement je n'ai pas compris ton besoin réel, surtout pour ta dernière demande.

à te relire

bonne journée

Papou

Re Re Bonjour Spike29 le forum

bon alors voilà avec ta demande en feuille Choix du Référentiel

Car il n'est pas exclu à l'avenir que certains de nos référentiels partagent la même lettre.

mais comme je ne tiens pas compte de la lettre mais de la valeur de la colonne B !!!!

a+

Papou

Re Re Bonjour Spike29 le forum

bon alors voilà avec ta demande en feuille Choix du Référentiel

Car il n'est pas exclu à l'avenir que certains de nos référentiels partagent la même lettre.

mais comme je ne tiens pas compte de la lettre mais de la valeur de la colonne B !!!! Et surtout que dans la feuille "Base de Données", par définition il ne doit y avoir aucun doublon !!! Même si les lettres sont utilisées dans chaque référentiel.............(Bref)

Voilà la V6 avec une liste de validation correcte et surtout classée Alpha pour plus de facilité.

a+

Papou

Re Spike29 le forum

je relis le blabla de 1 kilomètre et je relis:

Sur une même date, il n'est possible d'appliquer qu'un seul et unique référentiel.

cela c'est une base que tu te fixes ou qui est une obligation par rapport au travail??

Un tableau à deux colonnes avec dans la 1ère les dates de 1 à 31 et dans la 2nd colonne une liste déroulante avec l'ensemble des référentiels existant au choix.

Cela c'est fait et avec une liste propre

Le référentiel qui sera sélectionné dans la colonne "choix du référentiel" dans la Feuil ("Macro") sera également celui de référence pour cette date dans la Feuil ("Analyse")

C'est fait dans la feuille Choix Référentiel, tu as un bouton Traiter qui tient compte des choix en colonne B pour les dates sélectionnées

là ou je ne comprends plus rien!!!

Ainsi, depuis la Feuil("Macro") et avec l'utilisation du bouton : "Ajouter nouveau référentiel" je souhaite donner la possibilité aux futurs utilisateurs d'ajouter un nouveau référentiel applicables sans passer par le code source.

Ajouter un nouveau référentiel applicable signifie quoi pour toi??? Applicable à quoi? pour le traitement de l'analyse? ou bien ???

si c'est pour le traitement de l'analyse, tu as déjà la liste de validation en colonne B de la feuille "Choix du Référentiel"

Cela aura pour conséquence de:

• Ajouter une ligne supplémentaire au tableau "Liste des référentiels applicables)
• Etendre la liste déroulante du tableau "référentiels applicables" de +1 pour intégrer le nouveau référentiel
• Définir le nouveau référentiel ajouté manuellement dans la Feuil ("base de données")

a+

Papou