Discussion :

[oracle7556]

Bonsoir à tous,

Voilà mon problème :

1 - lorsque sous EXCEL dans une cellule je place la formule suivante :
(sachant que CIPD_NoCAT et CIPD_RefFAB sont 2 plages nommées)

Code :

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

{=EQUIV(1; (CIPD_NoCAT = A4) * (CIPD_RefFAB = H4);0)}

Tout se passe bien et j'obtiens bien le résultat escompté à savoir l'index de la ligne répondant aux 2 critères contenus dans les cellules A4 et H4.

2 - Maintenant, je souhaite reproduire cette recherche d'index de ligne sur les mêmes critère mais cette fois en VBA.
Dans une macro j'ai donc une ligne du type :

Code :

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

1
2
3
4
On Error Resume Next
dPos = WorksheetFunction.Match(1, ([CIPD_NoCAT] = vNoCAT) * ([CIPD_RefFAB] = vRefFAB), 0)
If Err.Number = 1004 Then
    .... Si ligne non trouvée

Sachant que mes 2 critères de recherche sont stockés dans respectivement les variables 'vNoCAT' et 'vRefFAB' et que l'on a toujours les 2 mêmes plages nommées qu'en version EXCEL ci-dessus.

Dans ce cas la fonction 'WorksheetFunction.Match' me renvoie toujours une erreur (pas forcément N°1004) et donc je n'arrive pas à obtenir mon index de ligne. Je précise qu'il existe bien une ligne répondant aux 2 critères !

Aussi j'en fais appel à vous pour m'aider à corriger ce qui ne va pas dans mon code VBA car je tourne en rond depuis quelques temps maintenant.

Merci d'avance
Cordialement
oracle7556

[Gorfael]

Salut oracle7556 et le forum
Je n'ai pas regardé, n'utilisant que rarement les fonctions de feuille de calcul en VBA (il y a d'autres méthodes), mais
En feuille, Faux=0 et le reste = Vrai, alors que
En VBA Vrai=-1 et le reste = FAUX
Ton erreur vient peut-être de là. Une boucle avec un test et AND serait à mon sens plus profitable (ou un Find)
A+

[Pierre Fauconnier]

Salut.

Utilise plutôt EVALUATE, car je pense que Worsheetfunction n'accepte pas la validation matricielle, alors que EVALUATE interprétera la chaine passée comme matricielle si tu lui passes les plages

Code :

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

Evaluate("MATCH(1,(CIPD_NoCAT=""" & vNoCat & """)*(CIPD_RefFAB=""" & vRefFab & """),0)")

Cela te permettra d'éviter les boucles qui sont toujours plus lourdes que les fonctions natives et d'avoir un code plus concis, et donc plus maintenable.

[oracle7556]

Bonjour à tous,

@Gorfael
Merci de ta réponse mais je retiendrais plutôt celle de Pierre Fauconnier qui est plus adaptée à mon problème.
A+

@Pierre Fauconnier
Merci aussi pour ta réponse. en passant par 'evaluate' cela marche. j'avais bien essayé auparavant de poser ma question mais je n'écrivais pas correctement l'instruction du coup cela ne marchait pas.
Maintenant avec la bonne syntaxe c'est nickel.

Toutefois, il se pose un nouveau problème : comment gérer l'erreur si la fonction 'match' ne trouve pas la ligne voulue (voir mon exemple ci-dessus).
Lorsque j'utilise une recherche avec un seul critère tel que :

Code :

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

1
2
3
4
On Error Resume Next
dPos = WorksheetFunction.Match(vNoCAT, [CIPD_NoCAT], 0)
If Err.Number = 1004 Then
    .... si ligne non trouvée

pas de soucis l'erreur 1004 est bien levée et je peut traiter en conséquence.

Mais maintenant avec l'emploi de la fonction 'evaluate' sur ce type de recherche à 2 critères, je récupère dans ce cas à priori une erreur n°13 (de type) alors que normalement la fonction 'match' renvoie '#NA' si elle n'a pu trouver de correspondance du critère dans la plage indiquée.
Du coup je ne sais plus faire. Comment gérer alors ce cas de figure quand une ligne n'est pas trouvée ? As-tu une idée ?

Merci de ta réponse éclairée.
Cordialement
oracle7556

[Pierre Fauconnier]

En fait, EVALUATE permet d'évaluer une formule telle qu'elle serait passée en Excel version anglaise.

Dès lors, tu peux la rédiger dans une feuille Excel

=SI(ESTNA(EQUIV(1;(CIPD_NoCAT=A4)*(CIPD_RefFAB=H4);0));"";EQUIV(1;(CIPD_NoCAT=A4)*(CIPD_RefFAB=H4);0))

dans une cellule, puis tu récupères la formule (à l'anglaise) en VBA dans la fenêtre d'exécution (Ctrl+G) via par exemple

Code :

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

? activecell.formula

pour récupérer

=IF(ISNA(MATCH(1,(CIPD_NoCAT=A4)*(CIPD_RefFAB=H4),0)),"",MATCH(1,(CIPD_NoCAT=A4)*(CIPD_RefFAB=H4),0))

Tu recopies en paramètre de EVALUATE en modifiant ce qui doit l'être.

Dans ton cas, la ligne Evaluate(...) devient

Code :

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

Evaluate("IF(ISNA(MATCH(1,(CIPD_NoCAT=""" & vNoCat & """)*(CIPD_RefFAB=""" & vRefFab & """),0)),"""", Match(1, (CIPD_NoCAT = A4) * (CIPD_RefFAB = H4), 0))")

Donc, dans ton cas et avec la formule que je propose, EVALUATE() renvoie soit une valeur, soit une chaine vide, et tu testes la réponse avec un bloc IF... End If

[oracle7556]

Bonjour à tous,

@Pierre Fauconnier

MERCI, ta solution marche nickel.
Le problème est donc résolu.

MERCI encore de ton aide.
Cordialement
oracle7556