evaluate >index>match multicolonne (ligne et non le resultat )

**patricktoulon** · 24/08/2019, 13h58

bonjour a tous
je reviens un peu sur ma recherche multicritères et multi colonne

je sais obtenir le résultat de 3 valeurs trouvée sur une même ligne

mais je souhaiterais obtenir le numéros de cette ligne correspondante au 3 critères

celle ci donne la valeur de la colonne "D" correspondant au critères en colonne "A,B,C"

Code :

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

1
2
3
4
5
Sub test()
    criteres = Array("""truc""", """machin""", """chose""")
    ColonS = Array("A2:A20", "B2:B20", "C2:C20")
    MsgBox Evaluate("INDEX(D2:D20,MATCH(1,(" & ColonS(0) & "=" & criteres(0) & ")*(" & ColonS(1) & "=" & criteres(1) & ")*(" & ColonS(2) & "=" & criteres(2) & "),0))")
End Sub

des idées ?
edit:
j'ai bien celle ci qui me donne l'index par raport a la plage mais ca n'est pas l'index de ligne sauf si ma plage commence en A1

Code :

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

MsgBox Evaluate("MATCH(1,(" & ColonS(0) & "=" & criteres(0) & ")*(" & ColonS(1) & "=" & criteres(1) & ")*(" & ColonS(2) & "=" & criteres(2) & "),0)")

edit 2:
celle la est juste mais
y a t il quelque chose de plus simple ou le moyen d'intégrer l'offset du départ de la plage dans la formule

Code :

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

MsgBox Evaluate("MATCH(1,(" & colons(0) & "=" & criteres(0) & ")*(" & colons(1) & "=" & criteres(1) & ")*(" & colons(2) & "=" & criteres(2) & "),0)") + Range(colons(1)).Row - 1

**Pierre Fauconnier** · 24/08/2019, 17h02

Salut

EVALUATE ne peut pas fonctionner avec MATCH en matricielle en incluant l'addition de la ligne de départ. Par contre, une matricielle avec le max de la ligne, ça Evaluate Sait faire... Et là, comme tu travailles avec la fonction LIGNE, l'emplacement de la table n'a pas d'importance. Si tu n'es pas en tableau structuré, cela ne change rien, il faut juste utiliser tes références comme tu l'as fait dans tes essais.

En Excel
Nom : 2019-08-24_165919.png
Affichages : 582
Taille : 39,7 Ko

Nom : 2019-08-24_165919.png
Affichages : 582
Taille : 39,7 Ko

Et en VBA avec EVALUATE
Nom : 2019-08-24_165740.png
Affichages : 615
Taille : 36,0 Ko

**patricktoulon** · 25/08/2019, 09h57

re
Bonjour Pierre

ca fonctionne merci
mais en concaténation je n'arrive pas a la faire fonctionner

celle ci fonctionne

Code :

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

1
2
3
4
5
6
7
8
9
Sub Macro6()
'trouver la ligne correspondant au critères en colonne "A.B.C"
'exemple de base  Evaluate("=MAX(ROW(A1:A10)*(A1:A10=""truc"")*(B1:B10=""machin"")*(C1:C10=""chose""))")
  Dim  feuille$, criteres, ColonS
 feuille = "Feuil1"    'nom de la feuille
    criteres = Array("""truc""", """machin""", """chose""")    'critères a retenir dans le même ordre que les colonnes
    ColonS = Array("A2:A9", "B2:B9", "C2:C9")    ' colonne a observer pour les critères dans le même ordre
    MsgBox Evaluate("=MAX(ROW(" & feuille & "!" & ColonS(0) & ")*(" & feuille & "!" & ColonS(0) & "=" & criteres(0) & ")*(" & feuille & "!" & ColonS(1) & "=" & criteres(1) & ")*(" & feuille & "!" & ColonS(2) & "=" & criteres(2) & "))")
End Sub

mais celle ci je n'y arrive pas pourtant dans le Debug la syntaxe est correcte
en concaténation

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
Sub Macro7()
'trouver la ligne correspondant au critères en colonne "A.B.C"
'exemple de base  Evaluate("=MAX(ROW(Feuil1!A1:A10)*(Feuil1!A1:A10=""truc"")*(Feuil1!B1:B10=""machin"")*(Feuil1!C1:C10=""chose""))")
    Dim G$, feuille$, criteres, ColonS, I&, Formula$, debut$
    G = String(2, Chr(34)) 'les guillemets
    feuille = "Feuil1"    'nom de la feuille
    criteres = Array("truc", "machin", "chose")    'criteres a retenir dans le meme ordre que les colonnes
    ColonS = Array("A2:A10", "B2:B10", "C2:C10")    ' colonne a observer pour les criteres dans le meme ordre
    debut = "(" & feuille & "!" & ColonS(0) & ")*"
    For I = 0 To UBound(criteres)
        ColonS(I) = "(" & feuille & "!" & ColonS(I) & "=" & G & criteres(I) & G & ")"
    Next
    Formula = "=MAX(ROW" & debut & Join(ColonS, "*") & ")"
    Debug.Print Formula
    MsgBox Evaluate(Formula)
End Sub

**Pierre Fauconnier** · 25/08/2019, 13h47

Il faut afficher la valeur de Formula pour pouvoir l'examiner... Je suppose que c'est une question de , ou de ; ou une bêtise de ce genre. Pratiquer comme tu l'as fait permet de vérifier la reconstitution du texte. Donc, ça ne devrait pas poser beaucoup de problèmes de voir où le bât blesse.

Ce que je fais encore bien dans ces cas-là, c'est créer la formule en Excel avec les valeurs utilisées en VBA, de façon à comparer la chaine créée en VBA avec Activecell.Formula. Méthode imparable pour voir où le grain de sable s'est logé.

**patricktoulon** · 25/08/2019, 21h53

re
c'est une enigme
je n'y comprends plus rien
donc pour que ca fonctionne dans EVALUATE , il ne faut plus les double guillemets
c'est la première fois que je vois ca
alors quen hardcodé il les faut

version hardcodée

Code :

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

 MsgBox Evaluate("=MAX(ROW(Feuil1!A1:A10)*(Feuil1!A1:A10=""truc"")*(Feuil1!B1:B10=""machin"")*(Feuil1!C1:C10=""chose""))")

maintenant la version qui concatène la formule (regarde le debug)

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
22
23
24
25
26
27
Sub Macro7()
'trouver la ligne correspondant au critères en colonne "A.B.C"
'exemple de base  Evaluate("=MAX(ROW(Feuil1!A1:A10)*(Feuil1!A1:A10=""truc"")*(Feuil1!B1:B10=""machin"")*(Feuil1!C1:C10=""chose""))")
    Dim feuille$, criteres, ColonS, I&, Formule$, debut$
     feuille = "Feuil1"    'nom de la feuille
 
    criteres = Array("truc", "machin", "chose")    'criteres a retenir dans le meme ordre que les colonnes(test string)
    'criteres = Array(218, 345, 500)  'criteres a retenir dans le meme ordre que les colonnes(test numerique)
 
    ColonS = Array("A2:A10", "B2:B10", "C2:C10")    ' colonne a observer pour les criteres dans le meme ordre
 
    debut = "(" & feuille & "!" & ColonS(0) & ")*"
 
 
    For I = 0 To UBound(criteres)
        criteres(I) = IIf(IsNumeric(criteres(I)), criteres(I), Chr(34) & criteres(I) & Chr(34))
        ColonS(I) = "(" & feuille & "!" & ColonS(I) & "=" & criteres(I) & ")"
    Next
 
    Formule = "=MAX(ROW" & debut & Join(ColonS, "*") & ")"
    Debug.Print "pour Evaluate " & vbCrLf & Formule & vbCrLf & " - --------------------------------------------------------" & vbCrLf
    MsgBox Evaluate(Formule)
 
    'Formule = Replace(Formule, Chr(34), String(2, Chr(34)))
   ' Debug.Print "formule dans cellule  " & vbCrLf & Formule & vbCrLf & " - --------------------------------------------------------" & vbCrLf
   ' [G1].Formula = Formule
End Sub

seul problème quand je débloque les 3 dernières ligne pour redoubler les guillemets et l'injecter dans une cellule ca plante
si je les triples ca plante pas mais j'ai j'obtiens "#VALEUR!"

c'est fou non?

edit:
ben en fait je pense avoir compris pour les simple guillemets dans evaluate
en fait dans le hardcodé il sont doublés par ce qu'il font parti de la chaine(formule) englobée dans un guillemet

par contre pour l'injection de la formule je pige pas

**Pierre Fauconnier** · 26/08/2019, 07h53

Si il faut les doubles guillemets

Supprime tes trois lignes commentées et à la place, envoie simplement ta variable Formule sans rien y changer, MAIS!! en matricielle ( => FormulaArray)

Code VBA :

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

Range("g1").FormulaArray = Formule

**patricktoulon** · 26/08/2019, 09h57

Bonjour Pierre
non ca ne fonctionne pas
la formule est montée en simple guillemet pour evaluate je suis donc obligé de garder 2 lignes
pour l'injection j'ai essayé avec 2 guillemets ca plante "impossibilité la propriété formulearray de la classe range"

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
22
23
24
25
26
27
Sub Macro7()
'trouver la ligne correspondant au critères en colonne "A.B.C"
'exemple de base  Evaluate("=MAX(ROW(Feuil1!A1:A10)*(Feuil1!A1:A10=""truc"")*(Feuil1!B1:B10=""machin"")*(Feuil1!C1:C10=""chose""))")
    Dim feuille$, criteres, ColonS, I&, Formule$, debut$
     feuille = "Feuil1"    'nom de la feuille
 
    criteres = Array("truc", "machin", "chose")    'criteres a retenir dans le meme ordre que les colonnes(test string)
    'criteres = Array(218, 345, 500)  'criteres a retenir dans le meme ordre que les colonnes(test numerique)
 
    ColonS = Array("A2:A10", "B2:B10", "C2:C10")    ' colonne a observer pour les criteres dans le meme ordre
 
    debut = "(" & feuille & "!" & ColonS(0) & ")*"
 
 
    For I = 0 To UBound(criteres)
        criteres(I) = IIf(IsNumeric(criteres(I)), criteres(I), Chr(34) & criteres(I) & Chr(34))
        ColonS(I) = "(" & feuille & "!" & ColonS(I) & "=" & criteres(I) & ")"
    Next
 
    Formule = "=MAX(ROW" & debut & Join(ColonS, "*") & ")"
    Debug.Print "pour Evaluate " & vbCrLf & Formule & vbCrLf & " - --------------------------------------------------------" & vbCrLf
    MsgBox Evaluate(Formule)
 
    Formule = Replace(Formule, Chr(34), """""")
    Debug.Print "formule dans cellule  " & vbCrLf & Formule & vbCrLf & " - --------------------------------------------------------" & vbCrLf
    Range("g1").FormulaArray = Formule'plante pourtant c'est bon dans le Debug 
End Sub

ensuite je viens de me rendre compte que la méthode "MAX(ROWS" donnait la dernière occurrence
et dans le cadre de mon projet ,a savoir (recursive en dégrevant la plage déjà visite) pour obtenir toutes les lignes

**Pierre Fauconnier** · 26/08/2019, 10h14

Sur base du code que tu as fourni, ça fonctionne très bien chez moi

Nom : 2019-08-26_101157.png
Affichages : 564
Taille : 71,4 Ko

Cela dit, le besoin que tu exprimes maintenant n'est pas le même qu'initialement => il ne peut donc se résoudre avec les mêmes méthodes

Formule clairement ce que tu as actuellement et ce que tu veux obtenir, avec des éclatés d'écran si nécessaire, et il sera plus aisé de t'aider

**patricktoulon** · 26/08/2019, 11h31

re
autant pour moi Pierre
je travaillais sans prendre en compte l'autocorrection de la formule

en effet j'injecte au final
la meme formule que pour evaluate(sans double guillemets)

=MAX(ROW(Feuil1!A2:A10)*(Feuil1!A2:A10="truc")*(Feuil1!B2:B10="machin")*(Feuil1!C2:C10="chose"))

et je me retrouve avec cette formule dans la cellule

{=MAX(LIGNE(Feuil1!A2:A10)*(Feuil1!A2:A10="truc")*(Feuil1!B2:B10="machin")*(Feuil1!C2:C10="chose"))}

je suppose que c'est formulearray qui met les"{}"

et quand je sélectionne ou me positionne dans le texte de la formule dans la cellule les "{}" disparaissent

pour répondre a ta question
mon projet :
c'est de faire une fonction "FindWithCriteres" avec argument dynamique x colonnes, x critères
et mettre toute la partie construction de la formule dans un do loop dégrevant la plage des lignes déjà trouvées

en gros une sorte de find/findnext avec x critères sur plusieurs colonnes

**Pierre Fauconnier** · 26/08/2019, 12h02

L'ajout des accolades est le fait d'Excel lorsqu'il travaille en matricielle, ce qui est le cas ici vu que l'on utilise la propriété FormulaArray.

En Excel, valider en matricielle s'effectue par CTRL+SHIFT+ENTER. il faut toutefois noter que cette validation n'est pas acquise "ad vitam aeternam" , car si tu édites la formule, les accolades disparaissent et tu dois valider à nouveau en matricielle pour qu'Excel comprenne qu'il doit travailler en boucle sur tes lignes de données.

**patricktoulon** · 26/08/2019, 12h25

re
ok pigé
merci

me reste plus qu'a transformer cela en fonction

**patricktoulon** · 26/08/2019, 13h15

re
en gros voila la formule en boucle qui trouve toutes les lignes valides pour les critères
le résultât est exprimé dans le debug
ca fonctionne pour du texte ou des nombres

ca fonctionne pour 1 critere et une colonnes ou plusieurs

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
22
23
24
25
26
27
28
29
30
31
 
Sub Macro9()
'trouver la ligne correspondant au critères en colonne "A.B.C"
'exemple de base  Evaluate("=MAX(ROW(Feuil1!A1:A10)*(Feuil1!A1:A10=""truc"")*(Feuil1!B1:B10=""machin"")*(Feuil1!C1:C10=""chose""))")
    Dim feuille$, criteres, ColonS, I&, Formule$, debut$, Lig&,xx&
    feuille = "Feuil1"    'nom de la feuille
     Lig = 10
    Do While Lig > 0
        'criteres = Array("truc", "machin", "chose")    'criteres a retenir dans le meme ordre que les colonnes(test string)
        criteres = Array(218, 345, 500)  'criteres a retenir dans le meme ordre que les colonnes(test numerique)
        ColonS = Array("A2:A" & Lig, "B2:B" & Lig, "C2:C" & Lig)      ' colonne a observer pour les criteres dans le meme ordre
        debut = "(" & feuille & "!" & ColonS(0) & ")*"
 
        For I = 0 To UBound(criteres)
            criteres(I) = IIf(IsNumeric(criteres(I)), criteres(I), Chr(34) & criteres(I) & Chr(34))
            ColonS(I) = "(" & feuille & "!" & ColonS(I) & "=" & criteres(I) & ")"
        Next
        Formule = "=MAX(ROW" & debut & Join(ColonS, "*") & ")"
        'Formule = Replace(Formule, Chr(34) & Chr(34), Chr(34))
        xx = Evaluate(Formule)
        If xx > 0 Then
            Debug.Print Join(ColonS, ";")
            Debug.Print "pour Evaluate " & vbCrLf & Formule
            Debug.Print "ligne " & xx
            Debug.Print vbCrLf & " - --------------------------------------------------------" & vbCrLf
        End If
        Lig = xx - 1
        'MsgBox xx
    Loop
    ' Range("g1").FormulaArray = Formule
End Sub

evaluate >index>match multicolonne (ligne et non le resultat ) [Toutes versions]

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager