INDEX-MATCH entre Userform et Tableau structuré

Version imprimable

Bonjour à tous,
Dans Excel, j'ai créé une formule qui fonctionne comme attendu : Selon deux infos saisies dans deux cellules, la formule recherche la correspondance (merci Index et Equiv) dans un tableau et me retourne si elle a trouvé ou non. Parfait.

Là où ça ne fonctionne pas, c'est quand je mets en place un formulaire avec des Combobox (pour gérer la saisie des infos).
Ci dessous les deux formules utilisées.
La formule excel :

Code:

=SI(ESTNA(INDEX(t_visites;SOMMEPROD(EQUIV([@Pays] & [@Ville];t_visites[Pays] & t_visites[Ville];0)*1);2));"Non trouvé";"Trouvé !")

La formule VBA :
Code:

1 2 3 4 5 If WorksheetFunction.IsNA(WorksheetFunction.Index(t_visites, WorksheetFunction.SumProduct(WorksheetFunction.Match(UserForm1.cmbx_pays.Value & UserForm1.cmbx_ville.Value, t_visites.ListColumns(1).DataBodyRange & t_visites.ListColumns(2).DataBodyRange, 0) * 1), 1)) Then MsgBox ("Non trouvé") Else MsgBox ("Trouvé !") End If
Systématiquement, alors que les valeurs saisies dans les combobox sont existantes, j'obtiens le message "Non trouvé".
:aie:

Qui saurait me dire pourquoi la formule VBA ne fonctionne pas, que je cherche la solution, avant de me donner la solution (si je n'y arrive vraiment pas) ?

Merci d'avance !

20/08/2019, 16h29
Pierre Fauconnier

Salut.

Comme tu demandes à être mis sur la voie ;) (j'apprécie...)

Piste 1: Comment as-tu validé la formule dans Excel? A ma connaissance, Index/Match ne permettent pas la matricielle lorsque utilisés par Worksheetfunction.
Piste 2 (éventuellement): Que vaut t_Visites dans ton code VBA? Est-ce une variable déclarée plus haut dans le code? (on ne voit pas tout le code, donc je ne sais pas s'il y a Option Excplicit et donc si t_Visites a été déclaré ou pas)...

1 pièce(s) jointe(s)

Merci Pierre pour l'intérêt que tu portes à ma demande.
Je me permets de joindre mon fichier Excel qui me sert d'exercice pour ce cas précis.

Piste 1 : dans Excel, je n'ai pas eu à valider par Ctrl Maj Entrée. La touche Entrée a suffit.

Piste 2 : t_visites a été déclaré comme un ListObject. Voici le code complet de ma procédure.

Code:

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
32
 
Sub recherchevisite()
   Dim recherchenulle As Boolean
   recherchenulle = False
   Dim t_visites As ListObject
   Set t_visites = ThisWorkbook.Worksheets("Feuil1").ListObjects("t_visites")
   Dim msg As String
   Dim newvisite As ListRow
   Dim j As Integer
 
   If UserForm1.cmbx_pays.Value <> "" And UserForm1.cmbx_ville.Value <> "" Then
      On Error Resume Next
      Dim varNewFlowCountry As Variant, varNewFlowRange As Variant, varWaveNumber As Variant, varNewFlowNbCR As Variant
      If WorksheetFunction.IsNA(WorksheetFunction.Index(t_visites, WorksheetFunction.SumProduct(WorksheetFunction.Match(UserForm1.cmbx_pays.Value & UserForm1.cmbx_ville.Value, t_visites.ListColumns(1).DataBodyRange & t_visites.ListColumns(2).DataBodyRange, 0) * 1), 1)) Then
         MsgBox ("Non trouvé")
         recherchenulle = True
      Else
         MsgBox ("Trouvé !")
      End If
   Unload UserForm1
   ElseIf UserForm1.cmbx_pays.Value = "" Then
       MsgBox ("Renseigner un pays")
   ElseIf UserForm1.cmbx_ville.Value = "" Then
       MsgBox ("Renseigner une ville")
   Else: MsgBox ("Ok Pip's")
   End If
 
   If recherchenulle Then
      UserForm1.cmdbtn_ajouter.Visible = True
      UserForm1.cmdbtn_recherche.Visible = False
   End If
End Sub

20/08/2019, 17h56
Pierre Fauconnier

Sorry, je n'avais pas fait attention au SOMMEPROD, et EQUIV avec des plages concaténées requiert normalement la matricielle.

Ceci dit, piste 2: Dans Excel, tu renseignes une plage, alors qu'en VBA, tu renseignes un ListObject. Or Listobject <> Range => pas bon... ;)

Désolé pour ma réponse tardive.

J'ai tenté avec plusieurs modifications (notamment le code ci-dessous) mais j'obtiens systématiquement le message "Non trouvé".
Code:

1 2 3 4 5 6 If WorksheetFunction.IsNA(WorksheetFunction.Index(Range("A3:B6"), WorksheetFunction.SumProduct(WorksheetFunction.Match(UserForm1.cmbx_pays.Value & UserForm1.cmbx_ville.Value, Range("A3:A6") & Range("B3:B6"), 0) * 1), 1)) Then MsgBox ("Non trouvé") Else MsgBox ("Trouvé !") End If
:koi:
Devrais-je donc transformer mon tableau structuré en tableau à 2 dimensions (unlist ?), puis modifier la formule pour qu'elle travaille sur le tableau à 2 dimensions ?
Ou alors utiliser une plage nommée ?

23/08/2019, 07h33
Pierre Fauconnier

Je reste dans l'idée que WorksheetFunction ne gère pas bien INDEX/MATCH en matricielle, or SUMPRODUCT et la concaténation dans MATCH induisent une matricielle. C'est à mon avis là que se situe le problème. Pour ma part, je ne suis jamais arrivé à utiliser Index/Equiv en "matricielle" avec WorksheetFunction.

Cela dit, pour trouver que le couple existe dans le tableau, tu pourrais utiliser NB.SI.ENS (COUNTIFS) qui fonctionnera sans soucis avec WorksheetFunction et qui est plus simple à rédiger que cet empilement de WorksheetFunction.

Plutôt que passer par les ListObject.ListColumns, je passerais directement par les références structurées: Range("t_Visites[Pays]") (Les références structurées peuvent être utilisées en VBA sans problème).

Je préfère travailler avec EVALUATE(...) et les références structurées qu'avec WorksheetFunction et le listobject. C'est plus proche de l'Excel, et Evaluate(...) comprend, en analysant la syntaxe de ce qu'elle reçoit, qu'elle doit travailler en matricielle.

Il ne faut pas transformer le tableau structuré en plage normale (unlist). Le problème ne se pose pas à ce niveau. Les solutions avec COUNTIFS, éventuellement dans un Evaluate(...), te permettent de réaliser ce que tu souhaites obtenir.
26/08/2019, 11h36
benoitdevries

Ok, j'essaie de mettre tout ça en application, et je publierai mes résultats.

Cette même méthode que tu proposes serait-elle compatible avec une comparaison sur 4 éléments (de trouver que la combinaison de A & B & C & D est la même que les infos saisies dans 4 cellules d'un Userform) ?

J'avance !
Le test suivant me retourne bien qu'il trouve ou pas une (ou plusieurs) occurrence.
Code:

1 2 3 4 5 6 If (WorksheetFunction.CountIfs(Range("t_visites[Pays]"), UserForm1.cmbx_pays.Value, Range("t_visites[ville]"), UserForm1.cmbx_ville.Value) = 0) Then MsgBox ("Non trouvé !") Else MsgBox ("Trouvé !") End If
Par curiosité, j'aimerai bien savoir comment récupérer le numéro de la ligne trouvée ...

26/08/2019, 14h04
Pierre Fauconnier

Salut.

Tu peux le faire avec uniquement MATCH qui récupère l'index de la ligne de la plage => pas forcément la ligne de la feuille. Si tu veux la ligne de la feuille, tu peux partir sur un Max (si une seule ligne pertinente).

Je donne la solution avec un MAX matriciel dans cette discussion. Si tu as des difficultés pour adapter à ton cas, précise-le ici, dans ta discussion...
26/08/2019, 17h24
benoitdevries

Citation:

Envoyé par Pierre Fauconnier

Je donne la solution avec un MAX matriciel dans cette discussion.

Je ne vois pas le lien vers la discussion que tu mentionnes. Ai-je un souci de navigateur ?
26/08/2019, 17h25
Pierre Fauconnier

Arf... J'ai oublié de le mettre :fou: :fessee:

https://www.developpez.net/forums/d1...-non-resultat/