Trouver la ligne satisfaisant deux critères

Bonjour,

Dans le cadre de mon travail, je dois développer un outil de pilotage de portefeuille de projets. Sauf que voilà... je bloque depuis plusieurs heures sur le sujet qui est le suivant.

J'ai un portefeuille de projets/challenges qui se décomposent chacun en trimestres (Q1 2019, Q2 2019, Q3 2019, Q4 2019, Q1 2020, Q2 2020, etc.) qui eux-mêmes se décomposent en livrables (en effet, il peut y avoir plusieurs livrables attendus par trimestre).

Mon objectif est de pouvoir ajouter un livrable à n'importe quel trimestre de n'importe quel challenge dans mon tableau des données.

J'ai créé un formulaire permettant de prendre les input suivants :
- le nom du challenge associé au livrable qu'on souhaite ajouter ;
- le trimestre pour lequel le livrable est attendu (un livrable ne peut être assigner qu'à un semestre).

(c.f. image ci-dessous)
Pièce jointe 476939

Voici le format de ma datasheet :
Pièce jointe 476942

Voici le fichier téléchargeable si besoin :
Pièce jointe 476946

L'objectif est donc d'insérer une ligne en-dessous. La difficulté réside dans le fait que chaque projet, pour chaque trimestre peut compter entre 0 et n livrables. Les lignes sans livrables sont vouées à disparaître.

J'ai essayé plusieurs formules à l'aide de la fonction FIND mais n'arrive pas à lui appliquer mes deux critères input via le formulaire.
Les solutions sur ce forum ou sur stackoverflow ou sur d'autres forums ne satisfont pas mon problème ou parfois je ne comprends juste pas le code associé...

Qu'en pensez-vous ? Il y a certainement plusieurs moyens de faire la chose. Et beaucoup propose des solutions à double boucle mais je doute que ce soit opti.

Merci énormément à celle ou celui qui pourra m'aider. :)

Salut.

Normalement, tu devrais utiliser une table de données pour stocker tes données, et dans cette table, l'ordre des lignes de devrait pas avoir de l'importance. C'est une règle capitale d'Excel qui dit qu'il faut séparer les données de leur exploitation. Tu devrais donc normalement encoder à la suite de la table, quitte à les trier par la suite selon l'ordre qui te convient.


Cela dit
Perso, j'utilise, en VBA (je dis en VBA car nous sommes sur le forum vba Excel, malgré que ton fichier soit en xlsx => sans macros), la fonction Evaluate(...) qui permet d'évaluer une formule, et la formule, c'est un EQUIV matriciel.

Je te l'illustre en Excel en cellule G1 (attention, à valider avec SHIFT+CTRL+ENTER sans les accolades, Excel les ajoute) pour trouver la ligne de la table de donnée qui contient les x infos recherchées.

Tu peux utiliser cette fonction en VBA pour en retrouver la syntaxe internationale, et tu vois comment elle est utilisée avec EVALUATE. Attention qu'elle renvoie la position de la ligne, par rapport à la première ligne de la référence utilisée.

Pièce jointe 476977

Pierre, merci pour ta réponse. J'ai dû mettre ce travail en pause ces derniers jours mais reviens dessus ajd.

J'ai bien créé un tableau de données comme tu me l'as conseillé.

Je veux maintenant écrire ma fonction VBA qui permettra d'insérer un livrable grâce aux données rentrées dans le formulaire.

Pour se faire, j'ai écrit :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

Sub ajouter_un_livrable() ' ' ajouter_un_livrable Macro ' Ajoute un livrable en fonction de son challenge et de son trimestre. '   Dim numero_ligne As Long numero_ligne = MATCH(1, (t_data[Associated_challenge] = MENU!D10)*(t_data[Associated_quarter] = MENU!D12),0)       ' End Sub

---

J'ai un message d'erreur affichant "Attendu : )". Et je ne comprends pas pourquoi tu as mis 1 dans le premier argument, j'ai reproduit la même chose bêtement mais ne saisi pas car c'est la valeur cherchée à la base ?

Pourrais-tu m'aiguiller ?

J'ai utilisé la fonction VBA EVALUATE, qui reçoit une chaîne de caractères et qui l'évalue. Si tu compares ton code et le mien, tu verras plusieurs différences.

Tu peux utiliser WorksheetFunction.Match, mais la syntaxe est un peu différente. Perso, je te conseille de viens regarder comment j'ai procédé et ce que j'ai exactement écrit pour récupérer la ligne souhaitée (celle avant ou après laquelle tu souhaites insérer ta donnée).

Idéalement, il faudra tester l'erreur en retour, mais on verra cela après.

Pour ce qui est de chercher le 1, je détaille dans le tableau Excel suivant pourquoi c'est 1 que l'on cherche. L'arithmétique booléenne permet de manipuler les valeurs VRAI et FAUX au sein d'opérations arithmétiques. Dans ce cas, VRAI et FAUX sont transtypés respectivement en 1 et 0, de sorte que l'on peut les multiplier et les additionner.

Pièce jointe 477759

Pierre, un grand merci pour ta réactivité et ton sens de la pédagogie.

J'ai écrit dans une case au pif :
=EQUIV(1;([Associated_challenge] = MENU!$D$10)*([Associated_quarter] = MENU!$D$12);0)
suivi d'un SHIFT+CTRL+ENTER

J'ai bien obtenu le numéro de ligne que je voulais à une lignes près (vu qu'il commence à partir de la seconde ligne seulement (tableau de données) et qu'il me faut la ligne en dessous de celle qui match). Je suppose que ce ne sera pas très dérangeant pour la suite. Je soustrairai par 1.

Je code donc :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

Sub ajouter_un_livrable() ' ' ajouter_un_livrable Macro ' Ajoute un livrable en fonction de son challenge et de son trimestre. '   Dim result As Variant match_formula = "EQUIV(1;(t_data[Associated_challenge] = MENU!$D$10)*(t_data[Associated_quarter] = MENU!$D$12);0)" result = Evaluate(match_formula)   numero_ligne = CLng(result) numero_ligne = numero_ligne - 1   Set datasheet = Worksheets("TRT RTI Challenges").ListObjects("t_data") With datasheet '.Cells(numero_ligne, 10).Select 'Selection.ListObject.ListRows.Add (numero_ligne) Set myNewDeliverable = .ListRows.Add(numero_ligne) End With ' End Sub

---

numero_ligne prend Erreur 2015 du coup il vaut 2015... je n'arrive pas à reprendre la valeur du EVALUATE :s

J'ai une erreur pour la ligne :

Code:

---

Set myNewDeliverable = .ListRows.Add(numero_ligne)

---

J'ai pourtant essayé plusieurs combinaisons... c'est là que je bloque pour le moment. Je continue de chercher.

Qu'en penses-tu toutefois ?

Evaluate nécessite que la fonction soit exprimée en "langage international" (en anglais, avec la syntaxe anglaise). Si tu regardes ce que j'ai illustré dans ma première réponse, tu verras que EVALUATE reçoit en paramètre une valeur textuelle (entre guillemets, donc) reprenant la formule à évaluer en anglais. J'y illustrais également la façon de récupérer facilement la version internationale de la formule via la fenêtre d'exécution et comment l'intégrer dans le Evaluate.

Pièce jointe 477911


Pour récupérer la ligne intéressante, il n'est pas besoin de faire +1... Normalement, EQUIV (MATCH en anglais) va travailler sur une table de donnée, dès lors, on peut récupérer la ligne de la table de données grâce à l'objet ListRow issu de la "collection" ListRows, et cet objet expose une propriété Range qui reprend la plage Excel Concernée.

Pièce jointe 477912

Pour généraliser ton approche, tu pourrais créer une fonction qui va récupérer le ListRow d'un projet selon les deux arguments que tu lui passes. Il "suffit" de recréer la formule en y insérant les valeurs des variables (attention aux guillemets qu'il faut doubler à l'intérieur de la chaine en VB pour les échapper).

Code:

---

1 2 3 4 5 6 7 8

Function getRowForProjectQuarter(Project As String, Quarter As String) As ListRow Dim Formula As String Dim Index As Variant   Formula = "MATCH(1,(t_Projets[Trimestre]=""" & Quarter & """)*(t_Projets[Projet]=""" & Project & """),0)" Index = Evaluate(Formula) If Not IsError(Index) Then Set getRowForProjectQuarter = Range("t_Projets").ListObject.ListRows(Index) End Function

---

Comme le fait de doubler les guillemets alourdit la saisie et la lecture du code, j'ai depuis longtemps dans mon module Tools une fonction qui insère des valeurs dans une chaine

Code:

---

1 2 3 4 5 6 7 8

Public Function ReplaceStrings(Source As String, Parameters) As String Dim i As Long   ReplaceStrings = Source For i = LBound(Parameters) To UBound(Parameters) Step 2 ReplaceStrings = Replace(ReplaceStrings, Parameters(i), Parameters(i + 1), 1, -1, vbTextCompare) Next i End Function

---

Voici la fonction "corrigée" qui utilise cet fonction/outil

Code:

---

1 2 3 4 5 6 7 8 9

Function getRowForProjectQuarter(Project As String, Quarter As String) As ListRow Dim Formula As String Dim Index As Variant   Formula = "MATCH(1,(t_Projets[Trimestre]=""{quarter}"")*(t_Projets[Projet]=""{project}""),0)" Formula = ReplaceStrings(Formula, Array("{quarter}", Quarter, "{project}", Project)) Index = Evaluate(Formula) If Not IsError(Index) Then Set getRowForProjectQuarter = Range("t_Projets").ListObject.ListRows(Index) End Function

---

Pierre, merci pour tes retours.

Tout est bien noté. Je comprends bien maintenant avec tes explications.

J'ai réussi à obtenir ce que je voulais du coup encore un grand merci ! :D

Je marque le sujet comme résolu j'imagine.