IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Macros et VBA Excel Discussion :

Trouver la ligne satisfaisant deux critères [XL-2007]


Sujet :

Macros et VBA Excel

  1. #1
    Candidat au Club
    Homme Profil pro
    Consultant en transformation digitale sécurisée
    Inscrit en
    Mai 2019
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Consultant en transformation digitale sécurisée
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2019
    Messages : 4
    Points : 2
    Points
    2
    Par défaut 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)
    Nom : formulaire_ajouter_livrable.PNG
Affichages : 210
Taille : 24,7 Ko

    Voici le format de ma datasheet :
    Nom : datasheet.PNG
Affichages : 281
Taille : 35,9 Ko

    Voici le fichier téléchargeable si besoin :
    template.xlsx

    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.

  2. #2
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    Novembre 2003
    Messages
    19 122
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 57
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : Novembre 2003
    Messages : 19 122
    Points : 55 955
    Points
    55 955
    Billets dans le blog
    131
    Par défaut
    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.

    Nom : 2019-05-21_210502.png
Affichages : 234
Taille : 28,4 Ko
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  3. #3
    Candidat au Club
    Homme Profil pro
    Consultant en transformation digitale sécurisée
    Inscrit en
    Mai 2019
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Consultant en transformation digitale sécurisée
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2019
    Messages : 4
    Points : 2
    Points
    2
    Par défaut Retours
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 ?

  4. #4
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    Novembre 2003
    Messages
    19 122
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 57
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : Novembre 2003
    Messages : 19 122
    Points : 55 955
    Points
    55 955
    Billets dans le blog
    131
    Par défaut
    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.

    Nom : 2019-05-24_111330.png
Affichages : 187
Taille : 22,7 Ko
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  5. #5
    Candidat au Club
    Homme Profil pro
    Consultant en transformation digitale sécurisée
    Inscrit en
    Mai 2019
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Consultant en transformation digitale sécurisée
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2019
    Messages : 4
    Points : 2
    Points
    2
    Par défaut Retour 2
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 ?

  6. #6
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    Novembre 2003
    Messages
    19 122
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 57
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : Novembre 2003
    Messages : 19 122
    Points : 55 955
    Points
    55 955
    Billets dans le blog
    131
    Par défaut
    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.

    Nom : 2019-05-21_210502.png
Affichages : 199
Taille : 42,6 Ko


    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.

    Nom : 2019-05-24_205913.png
Affichages : 214
Taille : 89,8 Ko
    Fichiers attachés Fichiers attachés
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  7. #7
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    Novembre 2003
    Messages
    19 122
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 57
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : Novembre 2003
    Messages : 19 122
    Points : 55 955
    Points
    55 955
    Billets dans le blog
    131
    Par défaut
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes billets de blog sur DVP
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  8. #8
    Candidat au Club
    Homme Profil pro
    Consultant en transformation digitale sécurisée
    Inscrit en
    Mai 2019
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Consultant en transformation digitale sécurisée
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2019
    Messages : 4
    Points : 2
    Points
    2
    Par défaut Retour 3
    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 !

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

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Trouver une colonne répondant à deux critères
    Par Coraline99 dans le forum Macros et VBA Excel
    Réponses: 5
    Dernier message: 16/02/2016, 15h30
  2. [XL-2010] Chercher date dont la ligne correspond à deux critères
    Par rider74230 dans le forum Macros et VBA Excel
    Réponses: 7
    Dernier message: 18/01/2015, 22h15
  3. [XL-2013] Trouver une cellule sur deux critères
    Par philmire dans le forum Excel
    Réponses: 2
    Dernier message: 14/11/2014, 09h28
  4. Chercher une ligne selon deux critère dans un formulaire
    Par abdelkarim_1987 dans le forum Excel
    Réponses: 0
    Dernier message: 16/09/2013, 12h05
  5. [XL-2003] Progress bar + rercherche d'une ligne selon deux critères (sur plusieurs feuilles)
    Par khroutchev dans le forum Macros et VBA Excel
    Réponses: 8
    Dernier message: 18/07/2013, 13h10

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo