INDEX-MATCH entre Userform et Tableau structuré

**benoitdevries** · 20/08/2019, 15h10

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 :

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

=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 :

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

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é".

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 !

**Pierre Fauconnier** · 20/08/2019, 16h29

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)...

**benoitdevries** · 20/08/2019, 17h36

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 :

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
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

**Pierre Fauconnier** · 20/08/2019, 17h56

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...

**benoitdevries** · 22/08/2019, 18h05

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 :

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

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

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 ?

**Pierre Fauconnier** · 23/08/2019, 07h33

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.

**benoitdevries** · 26/08/2019, 11h36

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) ?

**benoitdevries** · 26/08/2019, 14h01

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

Code :

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

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 ...

**Pierre Fauconnier** · 26/08/2019, 14h04

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...