Discussion :

[draynaud]

Bonjour,

Apres plusieurs essais infructueux avec evaluate(Indice(Match())), je cherche á dévellopper une fonction VBA qui permette de faire une recherche v á deux criteres.

Sans plus tarde, voici mon code:

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
Function CusVlookup(lookupval As String, columna1 As Range, columna2 As Range, indexcol As Long)
 
Dim x, y As Range
 
Dim result As Long
 
For Each x In columna1
    For Each y In columna2
        If x.Value & y.Value = lookupval Then
        result = Range(x.Row, indexcol).Value
        End If
    Next y
Next x
CusVlookup = result
End Function

Bien entendu, si je suis ici, c'est que ca ne marche !

Pensez vous que ce soit au niveau de la déclaration STRING de ce que je recherche?
De la comparaison? De la concaténation?

Pour vous donner un exmple, en columna1 j ai comme valeur CAM, Pacific, Carribbean, et en columna2 je veux matcher avec Total

Tout morceaux de codes, de réponses est le bienvenu!

merci á ceux qui me liront et plus encore á ceux qui maideront!

[78chris]

Bonjour

Je trouve plus simple de prévoir les deux données et la colonne associée plutôt qu'une string unique d'autant qu'on peut rechercher autre chose que des textes

Je ne sais comment tu imaginais indexcolumn : j'ai pris la colonne Excel mais on pourrait adapter pour garder la logique relative de RECHERCHEV.

Personnellement je préfèrerais faire une fonction assimilable à EQUIV plutôt qu'à RECHERCHEV...

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
Function CusVlookup(lookupval1, columna1 As Range, lookupval2, columna2 As Range, indexcol As Long)
 
Dim x As Range, y As Long
 
Dim result
y = columna2.Column - columna1.Column
 
For Each x In columna1
        If x.Value = lookupval1 And x.Offset(0, y).Value = lookupval2 Then
            result = Cells(x.Row, indexcol).Value
            Exit For
        End If
Next x
If Not IsEmpty(result) Then CusVlookup = result Else CusVlookup = [na()]
End Function

[RyuAutodidacte]

Bonsoir,
As tu pensé as SOMMEPROD ?
Car cela permet de sélectionner plusieurs critères afin d'obtenir la ligne concernée.

Edit : Exemple tout simple en formule (il faudra l'imaginé ensuite transposé en vba afin de faire la fonction) :

Le résultat est 4, on récupère donc le n° de ligne que l'on peut ensuite appliquer à la Range voulu avec le décalage pour récupérer les valeurs

1	1
2	2
3	3
TITI	TOTO
5	5
6	6
7	7

La formule :

Code :

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

=SOMMEPROD((C1:C7="TITI")*(D1:D7="TOTO")*LIGNE(C1:C7))

[78chris]

BOnjour

Il existe des tas de possibilités par formule mais draynaud voulait une fonction personnalisée proche de RECHERCHEV...

De plus ta formule ne marche qu'en cas d'unicité du couple

[RyuAutodidacte]

Bonjour draynaud,

Chris : il y a peut être qq chose qui m'a échappé, mais je crois bien que ma formule fait (entre guillemet) la même chose (sans le if) que ton

Code :

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

If x.Value = lookupval1 And x.Offset(0, y).Value = lookupval2 Then

c-à-d quand on match sur les 2 données, cela permet d'obtenir la ligne, il suffit alors de lui rajouter l'index de colonne …

Exemples (toutes les données commence à partir de A1 en-tête compris) :
PS : les codes sont à adapter à ses besoins bien sur

Recherche via formule :

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
Sub RechParFormule()
    MsgBox RechV3("TOT0", "B", "TITI", "E", "G")
End Sub
 
 
Function RechV3(V1Look, Col1, V2Look, Col2, MyIndex)
    Ligne = Application.Evaluate("SUMPRODUCT(((" & Col1 & ":" & Col1 & "=" & """" & V1Look & """" & ")*(" & Col2 & ":" & Col2 & "=" & """" & V2Look & """" & "))*ROW(A:A))")
    'Sur la base de cette formule : =SOMMEPROD(((B:B="TOTO")*(E:E="TITI"))*LIGNE(A:A))
    If Ligne = 0 Then
        RechV3 = "Aucune donnée trouvée"
    Else
        indexcol = Columns(MyIndex).Column
        RechV3 = Cells(Ligne, indexcol)
    End If
End Function

le tableau de test (faire différents essais afin de voir si tout est ok)

Entete1	Entete2	Entete3	Entete4	Entete5	Entete6	Entete7	Entete8
Donnée1.1	Donnée2.1	Donnée3.1	Donnée4.1	Donnée5.1	Donnée6.1	Donnée7.1	Donnée8.1
Donnée1.2	Donnée2.2	Donnée3.2	Donnée4.2	Donnée5.2	Donnée6.2	Donnée7.2	Donnée8.2
Donnée1.3	TOTO	Donnée3.3	Donnée4.3	TITI	Donnée6.3	Donnée7.3	Donnée8.3
Donnée1.4	Donnée2.4	Donnée3.4	Donnée4.4	Donnée5.4	Donnée6.4	Donnée7.4	Donnée8.4
Donnée1.5	Donnée2.5	Donnée3.5	Donnée4.5	Donnée5.5	Donnée6.5	Donnée7.5	Donnée8.5
Donnée1.6	Donnée2.6	Donnée3.6	Donnée4.6	Donnée5.6	Donnée6.6	Donnée7.6	Donnée8.6

Par tableau avec les numéros des colonnes :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub RechParNumCol()
    MsgBox RechV("TOTOTITI", 2, 5, 8)
End Sub
 
Function RechV(VLook, Col1, Col2, MyIndex)
    VA = ActiveSheet.UsedRange.Value
        For i = 2 To UBound(VA)
            If VA(i, Col1) & VA(i, Col2) = VLook Then
                RechV = VA(i, MyIndex)
                Exit For
            End If
        Next
        If RechV = "" Then MsgBox VLook & " ne se trouve pas dans la base": RechV = "Aucune donnée trouvée"
End Function

Par tableau avec les lettres des colonnes :

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
Sub RechParLettre()
    MsgBox RechV2("TOTOTITI", "B", "E", "A")
End Sub
 
Function RechV2(VLook, Col1, Col2, MyIndex)
    VA = ActiveSheet.UsedRange.Value
    C1 = Columns(Col1).Column: C2 = Columns(Col2).Column: Ind = Columns(MyIndex).Column
        For i = 2 To UBound(VA)
            If VA(i, C1) & VA(i, C2) = VLook Then
                RechV2 = VA(i, Ind)
                Exit For
            End If
        Next
        If RechV2 = "" Then MsgBox VLook & " ne se trouve pas dans la base": RechV2 = "Aucune donnée trouvée"
End Function

[78chris]

Bonjour

Ce que je disais c'est, qu'en cas de non unicité du couple recherché, le SOMMEPROD renvoie la somme des numéros de lignes correspondants et donc une info erronée.

[RyuAutodidacte]

Bonjour Chris,

Ce que je disais c'est, qu'en cas de non unicité du couple recherché, le SOMMEPROD renvoie la somme des numéros de lignes correspondants et donc une info erronée.

Heuu … non,si il ne trouve pas le couple il renvoie 0 et que l'on pourrait faire correspondre à NA
As tu un exemple concret du cas dont tu parles avec ma formule ?
je vais refaire le test par acquis de conscience …

Edit : j'ai re testé avec TOTO et TITI sur des lignes différentes et la formule me renvoie bien 0 ce qui veut dire que la non unicité du couple recherché ne fais pas la somme des lignes dans le SOMMEPROD.
Donc la formule marche parfaitement.
Quand la formule renvoie un numéro de ligne, l'unicité du couple recherché a été trouvé, quand la formule renvoie 0, l'unicité du couple recherché n'a pas été trouvé

[78chris]

RE

j'ai re testé avec TOTO et TITI sur des lignes différentes et la formule me renvoie bien 0 ce qui veut dire que la non unicité du couple recherché ne fais pas la somme des lignes dans le SOMMEPROD.
Donc la formule marche parfaitement.
Quand la formule renvoie un numéro de ligne, l'unicité du couple recherché a été trouvé, quand la formule renvoie 0, l'unicité du couple recherché n'a pas été trouvé

Quelques chose m'échape alors


J'ai toujours eu ce souci avec SOMMEPROD... donc si tu as la solution ou plutôt l'explication... je prends

Pour en revenir à la demande initiale, d'un équivalent de RECHERCHEV, en cas de doublon, RECHERCHEV renvoie le 1er trouvé pas NA donc je me suis calé la-dessus.

[RyuAutodidacte]

Re Chris,

honte à moi ce test là je ne l'ai pa fait. je me suis jamais retrouvé dans le cas où j'utilisais cette formule en présence de doublons.
Donc tu as tout à fait raison, mea culpa.
Dans le cas où il n'y a pas de doublons ça ne posera pas de problème.

J'ai toujours eu ce souci avec SOMMEPROD... donc si tu as la solution ou plutôt l'explication... je prends

Si je trouve la solution, j'y manquerai pas

draynaud peux tu nous dire si tu as des doublons ?

[draynaud]

draynaud peux tu nous dire si tu as des doublons ?

Salut les amis!

Non il n y a pas de doublons, sommeprod fonnctionne dans ce cas la!
1000 merci!
Desolé pour le temps de reaction, je me suis tellement cassé la tete sur mes problemes VBA que je me suis mis bien en retard sur mes autres projets!

Cependant je cherche toujours á avoir une fonction VBA pour un "double rechercheV", c est souvent utile.
Malheureusement je n ai pas acces au bon ordinateur, mais cela fonctionnerait ainsi:

Code :

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

1
2
3
4
5
6
7
8
9
10
CusVlookup(criteria1 as Range1, criteria2 as Range2, col1 as Range3, col2 as Range4, numcol as Double)
dim i,j as integer
For i = 1 to col1.size
For j = 1 to col2.size
If col1(i).Value = criteria1.value & col2(j).value = criteria2.value Then
CusVlookup = worksheet.evaluate(match(--la bonne expression))
ExitSub
End if
Next j, next i
End Sub

Mais j ai des problemes notamment dans la ligne de "evaluate" et sur le "exit SUB"


A bientot!

[78chris]

RE

Le code VBA que j'ai donné au #2 fonctionne il me semble !

[78chris]

Re

Re Chris,

honte à moi ce test là je ne l'ai pa fait. je me suis jamais retrouvé dans le cas où j'utilisais cette formule en présence de doublons.
Donc tu as tout à fait raison, mea culpa.
Dans le cas où il n'y a pas de doublons ça ne posera pas de problème.
...

D'un côté c'est rassurant, je n'avais pas fumé mais de l'autre dommage

J'ai déjà tenté des min, max dans le SOMMEPROD mais à part un micmac comme résultat... donc à suivre...

[RyuAutodidacte]

Je confirme je l'ai testé (pas à fond), il fonctionne

draynaud as tu testé ce fait via tableau ? (2 codes en post #5)
en num colonne ou en lettre colonne au choix, basé sur les explications du post #1 si je les ai bien compris (à adapter bien sur selon le contexte)

[draynaud]

RE

Le code VBA que j'ai donné au #2 fonctionne il me semble !

Alors il est sans doute plus proche du succes pour le moi, mais il ne fonctionne pas tout á fait pour moi, je pense que c'est lié au fait que les differentes RANGE ne sont pas dans les memes feuilles..

Je suis sous l'eau mais je compte bien recreuser ca vendredi !

merci pour votre aide précieuse, j'espere pouvoir en faire de meme un jour!
Dav

[RyuAutodidacte]

je pense qu'un exemple concret en xlsx donc sans macro avec la bonne structure des données sur les feuilles concernées et une feuille "résultat" permettant de savoir le résultat à obtenir serait nécessaire …

Edit : comme il ne doit pas y avoir de doublons,
pour le RECHERCHEV sur 2 feuilles différentes, on est sensé avoir les 2 données à trouver sur la même ligne des 2 feuilles, donc pour trouver la ligne cette formule le fait :

Code :

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

=SOMMEPROD(((Feuil1!B:B="TOTO")*(Feuil2!E:E="TITI"))*LIGNE(A:A))

donc en modifiant le code fait par formule on pourrait avoir le résultat escompté
mais y a t il une 3ème feuille ou l'lon va cherché le résultat, genre :

Code :

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

 RechV3 = Feuil3.Cells(Ligne, indexcol)

[RyuAutodidacte]

Voilà le code vite fait un peu bâclé mais pour le principe faisant intervenir la recherche sur 2 feuilles en choisissant les 2 colonnes voulus et tirant le résultat sur une 3ème feuilles :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub RechParFormule()
    MsgBox RechV3("TOTO", "Feuil1!B:B", "TITI", "Feuil2!E:E", "Donnees", "G")
End Sub
 
 
Function RechV3(V1Look, Col1, V2Look, Col2, FeuilleDonnes, MyIndex)
    Ligne = Application.Evaluate("SUMPRODUCT(((" & Col1 & "=" & """" & V1Look & """" & ")*(" & Col2 & "=" & """" & V2Look & """" & "))*ROW(A:A))")
    If Ligne = 0 Then
        RechV3 = "Aucune donnée trouvée"
    Else
        indexcol = Columns(MyIndex).Column
        RechV3 = Sheets(FeuilleDonnes).Cells(Ligne, indexcol)
    End If
End Function