Microsoft 365 match() en vba et multi-critères sur zones nommées

[C.Laurent]

Bonjour à tous

Je tourne en rond sur un problème, en fait j'ai le même problème avec xlookup() mais j'essaye de faire simple avec match() pour l'exemple de cette question, j'imagine que le problème est le même ...

J'ai un tableau (non structuré) dans lequel j'ai nommé les colonnes avec des formules
(pour la colonne nommée OpéCompte) =DECALER(Opérations!$A$5;;;VarNombreOpé;1)
(pour la colonne nommée OpéTitre) =DECALER(Opérations!$C$5;;;VarNombreOpé;1)

Dans le code plus bas, j'utilise la ligne de la cellule active comme référence.
Je cherche dans les lignes au dessus de cette ligne de référence, la première ligne qui a la même valeur que OpéCompte et OpéTitre de cette ligne de référence.

Sub test()

Dim FeuilleOpé As Worksheet
Dim LeCompte As String
Dim LeTitre As String
Dim RgCompte As Range
Dim RgTitre As Range

Set FeuilleOpé = ThisWorkbook.Worksheets("Opérations")
LeCompte = Intersect(FeuilleOpé.Range("OpéCompte"), FeuilleOpé.Rows(ActiveCell.Row)).Text
LeTitre = Intersect(FeuilleOpé.Range("OpéTitre"), FeuilleOpé.Rows(ActiveCell.Row)).Text

Set RgCompte = Intersect(Rows(Range("5:" & ActiveCell.Row - 1), Range("OpéCompte"))
Set RgTitre = Intersect(Rows(Range("5:" & ActiveCell.Row - 1), Range("OpéTitre"))

MsgBox Application.WorksheetFunction.Match(1, (RgCompte = LeCompte) * (RgTitre = LeTitre), 0)

=> erreur 13, incompatibilité de type :-(

Je crois comprendre que Excel ne comprend pas mes 2 critères.
J'ai lu des suggestions qui proposent de concaténer les champs avec & mais je trouve le code final peu lisible (je n'ai de tout façon pas réussi à mettre en œuvre), je préfère avec ...Match(1,... si c'est possible.

Comment éviter cette erreur ?
Je vous remercie de l'aide que vous m'apporterez.

@+Laurent

 

[patricktoulon]

bonjour
un classeur en exemple serait le bien venu
en l'etat pour le code que tu présente
il n'y a aucunes gestion d'erreur sur intersect
comment veux tu faire un match si on est pas sur que le range existe
je ne parle pas du (double match en un) en vba je ne sais pas si c'est possible (j'en doute en tout cas)

 

[C.Laurent]

Ok, (désolé)

On est certain que le Range existe au moment ou le code le demande, la gestion d'erreur ne semble pas être la priorité.

Dans le fichier ci joint, lorsque je sélectionne la cellule B20, la macro test devrait renvoyer 2 et B26 devrait renvoyer 5.

Merci

 

[patricktoulon]

rer
juste pour le fun (adapter de ma fonction perso (match multi critère et multi colonne

Sub justePourLeFun()
'on cherche la ligne ou se trouve (truc , machin , chose) en colonne (A,B et C)
'récuperation de l'index de ligne dans le sheets correspondant au criteres en colonnes "A.B.C"

    Dim Feuille$, LeCompte$, LeTitre$, RgCompte As Range, RgTitre As Range, FeuilleOpé As Worksheet

    Feuille = "Opérations"

    Set FeuilleOpé = ThisWorkbook.Worksheets("Opérations")

    LeCompte = Intersect(FeuilleOpé.Range("OpéCompte"), FeuilleOpé.Rows(ActiveCell.Row)).Text

    LeTitre = Intersect(FeuilleOpé.Range("OpéTitre"), FeuilleOpé.Rows(ActiveCell.Row)).Text

    ColonS = Array("OpéCompte", "OpéTitre")    ' colonne a observer pour les criteres dans le meme ordre

    ligne = Evaluate("=MATCH(1,(" & Feuille & "!" & ColonS(0) & "=""" & LeCompte & """)*(" & Feuille & "!" & ColonS(1) & "=""" & LeTitre & """),0)")

    MsgBox ligne
End Sub

 

[mapomme]

Bonjour à tous,

Pourquoi pas ? :

Sub test1()
Dim LeCompte, LeTitre, cpt, tit, i&
   Sheets("Opérations").Select
   LeCompte = Cells(ActiveCell.Row, Range("OpéCompte").Column)
   LeTitre = Cells(ActiveCell.Row, Range("OpéTitre").Column)
   cpt = Range("OpéCompte"): tit = Range("OpéTitre")
   For i = 1 To UBound(cpt)
      If tit(i, 1) = LeTitre Then If cpt(i, 1) = LeCompte Then MsgBox i: Exit Sub
   Next i
End Sub

 

[C.Laurent]

Bonjour

Merci pour ta suggestion mapomme, mais l'idée de boucler sur beaucoup de lignes ne me plait pas trop.

Pour l'idée de patricktoulon, ton exemple ne filtre pas sur "seulement les lignes au dessus" mais je vais regarder cette idée de range désigné dans un array() avec evaluate() ...

Merci pour votre aide
@+Laurent

​

 

[patricktoulon]

d'accords
purée t'es dur en affaire toi

Sub justePourLeFun()

    Dim Feuille$, LeCompte$, LeTitre$, RgCompte As Range, RgTitre As Range, _
        FeuilleOpé As Worksheet, opc$, opt$

    Feuille = "Opérations"

    Set FeuilleOpé = ThisWorkbook.Worksheets("Opérations")

    LeCompte = Intersect(FeuilleOpé.Range("OpéCompte"), FeuilleOpé.Rows(ActiveCell.Row)).Text

    LeTitre = Intersect(FeuilleOpé.Range("OpéTitre"), FeuilleOpé.Rows(ActiveCell.Row)).Text

    opc = Range("OpéCompte").Resize(ActiveCell.Row - Range("OpéCompte").Row + 1).Address(0, 0)

    opt = Range("OpéTitre").Resize(ActiveCell.Row - Range("OpéTitre").Row + 1).Address(0, 0)

    ColonS = Array(opc, opt)    ' colonne a observer pour les criteres dans le meme ordre

    ligne = Evaluate("=MATCH(1,(" & Feuille & "!" & ColonS(0) & "=""" & LeCompte & """)*(" & Feuille & "!" & ColonS(1) & "=""" & LeTitre & """),0)")

    MsgBox ligne
End Sub

 

[C.Laurent]

purée t'es dur en affaire toi

gros merci à toi !
Tout ceci me semble excellent, je regarde, l'adapte à ma sauce et reviens si j'ai du soucis.

Encore merci
@+Laurent

 

[mapomme]

Re @C.Laurent ,
Bonjour @patricktoulon,

Merci pour ta suggestion mapomme, mais l'idée de boucler sur beaucoup de lignes ne me plait pas trop.

J'aimerais bien savoir pourquoi (un à priori peut-être )

Une petite expérience dans le classeur joint :

• Il faut d'abord cliquer sur le bouton orange "Init..." qui initialise des données jusqu'à la ligne 200 000 et sur cette ligne Init place une société unique "XXX123". Par la suite, on se placera sur cette ligne 200 000 pour les tests (les macros le feront).
• Juste pour le fun, exécuter les macros "Boucle" et "Equiv".
• Comparer les durées d'exécution.

nota : on se retrouve dans le pire des cas où on doit parcourir la boucle (ou Equiv) jusqu'à la ligne 200 000.

 

[C.Laurent]

Excellent, voilà une réponse argumentée, j'adore

Oui, j'avais un gros à priori sur les boucles de long tableau (je profite de ces lignes pour m'excuser au près de tous les fans de boucles infernales ...), je reconsidèrerai peut être ce point de vue un jour.

J'ai aussi pour excuse que la formule finale envisagée il y a beaucoup plus que 2 critères : c'est avec xlookup() avec des ranges comme dans mon exemple match(). Je vais tenter de mettre dans des array() dans mon xlookup.

A plus tard
@+Laurent

 

[mapomme]

J'ai aussi pour excuse

Il n'y a pas à s'excuser. C'est un forum où règne la liberté.

J'ai bien aimé :

tous les fans de boucles infernales ...)

 

[patricktoulon]

nota : on se retrouve dans le pire des cas où on doit parcourir la boucle (ou Equiv) jusqu'à la ligne 200 000.

et c'est l à ou match prends la main
car il n'y a pas besoins de parcourir 200 000 ligne pour trouver the first

 

[mapomme]

et c'est l à ou match prends la main
car il n'y a pas besoins de parcourir 200 000 ligne pour trouver the first

Alors comment expliques tu les durées ? Chez moi, la boucle est toujours plus rapide... Et chez toi ?
Soit dit en passant, on sort de la boucle dès qu'une ligne correspond, non ?

 

[patricktoulon]

re
par ce que vous fonctionnez en 64 bits et le L.A.A c'est de la daube en 64 bits il n'est même pas paramétrable
tandis qu'en 32 oui par la base de registre

alors après
oui on sort de la boucle des l’occurrence
tout dépend de la position de l'occurence en fait
plus l’occurrence est vers la fin plus match prendra la main

ca avait fait l’objet a l’époque d'un kb en mise a jour pour 2013
le kb3115162

 

[mapomme]

alors après
oui on sort de la boucle des l’occurrence
tout dépend de la position de l'occurence en fait
plus l’occurrence est vers la fin plus match prendra la main

Si l'occurence est sur la 1ère ligne, la boucle est plus rapide aussi (on ne va même pas jusqu'au premier Next).
Si l'occurence est sur la ligne n°200 000, la boucle est plus rapide aussi.