Application Vlookup pour multi-recherches

Bonjour à tous,

Je cherche depuis un moment sur les forums, s'il est possible d'utiliser Application.vlookup pour plusieurs valeurs cherchées. Je m'explique :

Dans le fichier joint, sur la première feuille on choisit ce qu'on met dans un caddie de supermarché, en l’occurrence ici ce sont des paniers de fruits.

La composition des paniers de fruit est définie dans la 2ème feuille du classeur.

En fait j'aimerais créer une fonction pouvant compter le nombre d'un fruit spécifique dans le caddie. La fonction "regarde" la composition du caddie et compte le nombre d'un fruit donné dans chaque panier. Je souhaite m'affranchir d'une boucle for. Et je me demande si vlookup peut faire ça.

Au lieu de faire la recherche pour une cellule donnée dans une matrice et renvoyé un résultat, il cherche pour plusieurs cellules dans une liste et me renvoie tous les résultats.

Mon code VBA est le suivant :

Public Function FruitsCount(ByVal fruit As String)
'Range("Caddie_Compo") est la liste des paniers dans le caddie (plage nommée dans la feuille 1 du classeur, coloriée en orange)
'Range("Paniers_Compo") est la composition des paniers (plage nommée dans la feuille 2 du classeur, coloriée en violet) 
    Select Case fruit

        Case "Pommes"

            FruitsCount = WorksheetFunction.VLookup(Range("Caddie_Compo"), Range("Paniers_Compo"), 2, False)

        Case "Poires"

            FruitsCount = WorksheetFunction.VLookup(Range("Caddie_Compo"), Range("Paniers_Compo"), 3, False)

        Case "Oranges"

            FruitsCount = WorksheetFunction.VLookup(Range("Caddie_Compo"), Range("Paniers_Compo"), 4, False)

        Case "Kiwis"

            FruitsCount = WorksheetFunction.VLookup(Range("Caddie_Compo"), Range("Paniers_Compo"), 5, False)

    End Select

End Function

Je souhaite éviter si possible que pour chaque cas je fasse quelque chose comme ça (le code ci-dessous n'est pas exact):

For each c in Paniers_Compo
compteur= WorksheetFunction.VLookup(c, Range("Paniers_Compo"), 5, False)
nbtotal =nbtotal +compteur
compteur=0
Loop 

Merci beaucoup pour votre aide, je peux clarifier si ce n'est pas clair.

Bonjour Hubert41

Je ne comprends pas très bien ce que tu cherches à faire en définitif !?

un=somme.si(Plage;Critère;Somme_Plage) ne pourrais pas te convenir !?

A+

Bonsoir,

Je crois qu'il souhaite obtenir le total des fruits (qu'il choisit dans une cellule) par caddie.

Un essai avec une macro, en G2 tu choisis ton fruit et H2 tu as le total par caddie.

A tester.

J'ai le sentiment que tu peux obtenir le résultat en une seul formule avec la fonction sommeprod, mais je n'en suis pas certain, donc j'ai choisi une première méthode par macro.

En espérant que cela te convienne. J'aimerais bien connaître la formule (sans colonnes intermédiaires, cela doit être possible).

Le code de la fonction :

Option Explicit

Function total(fruit As Range)

Dim wsCal As Worksheet, wsPan As Worksheet
Dim add_fruit As Range, add_panier As Range
Dim i As Long

Set wsCal = Worksheets("Calculs")
Set wsPan = Worksheets("Paniers")
Set add_fruit = wsPan.Range("B1", wsPan.Cells(1, Application.WorksheetFunction.CountA(wsPan.Rows("1:1")))).Find(fruit, LookIn:=xlValues, lookat:=xlWhole)

If Not add_fruit Is Nothing Then
    For i = 2 To wsCal.Range("B65000").End(xlUp).Row
    Set add_panier = wsPan.Range("A2:A" & wsPan.Range("A65000").End(xlUp).Row).Find(wsCal.Cells(i, 2), LookIn:=xlValues, lookat:=xlWhole)
            If Not add_panier Is Nothing Then
                total = total + (wsPan.Cells(add_panier.Row, add_fruit.Column).Value * wsCal.Cells(i, 3).Value)
            End If
    Next i
Else
    MsgBox "fruit non présent dans la liste des paniers"
End If

End Function

Salut ketamacanna

ketamacanna a écrit :

Bonsoir,

Je crois qu'il souhaite obtenir le total des fruits (qu'il choisit dans une cellule) par caddie.

Attends je vais te dire ça

Je vois ... je vois ... que c'est bien possible

A+

bonjour

dans la feuille calcul ;on voit bien panier 1 ou2 ....au choix mais la colonne quantite correspond a quoi ????? ;vraisemblablement il manque queq chose; je ne sais pas comment tu esperes a partir d'une seule quté (pour 3articles sensés correspondre a un type de panier ) trouver 1 poids puis un prix

a moins que ce soit des lots

cordialement

pas besoin d'être voyant :

En fait j'aimerais créer une fonction pouvant compter le nombre d'un fruit spécifique dans le caddie.

Avec une seule formule (sans macro) je pense que c'est également possible (et qui tienne compte du nombre de panier par caddie sans avoir une formule ou dès qu'il y a un nouveau panier il faut modifier la formule.)

cdt.

re

a moins que : (il arrive souvant que les questions soient mal formulées)

faut voir

Oui effectivement tulipe, c'est peut être le résultat qu'il attend. En attendant sa réponse bonne soirée à vous

salut keta

a la limite j'aurai pu eventuellement tout mettre dans la meme cel pour que ça ressemble a sa piece jointe mais pour en faire quoi?

wait and see

cordialement

Salut à tous,

merci d'avoir apporté vos solutions à ce problème.

ketamacanna, tulipe_4, oui en effet c'est exactement ce dont je souhaite faire. Par contre le nombre de fruits ne doit pas apparaître sur la feuille (car calcul intermédiaire). Auquel cas je suis en train de voir si je peux utiliser la méthode de tulipe_4.

J'ai le sentiment que tu peux obtenir le résultat en une seul formule avec la fonction sommeprod, mais je n'en suis pas certain, donc j'ai choisi une première méthode par macro.

Oui en effet, c'est ce qu'idéalement je recherche, mais je n'ai pas réussi. Je pensais que c'était un produit scalaire ou calcul matriciel simple. Mais je n'ai pas réussi à le faire tourner.

Je pense que sinon j'utiliserai une de vos deux techniques à défaut de pouvoir utiliser une sommeprod.

Merci de votre contribution.

Cordialement,

Hubert41

Je vais essayer de trouver la formule sans vba, je te tiens au courant si je la trouve. Mais au final, tu souhaites bien avoir le total de fruit par caddie pour un type de fruit ? mes calculs avec les quantités sont bonnes ou pas ?

cdt

cdt

Oui ketamacanna, tout au fait. Selon la composition du caddie, je veux obtenir le nombre de fruits (par type de fruit).

J'essaye de faire quelque chose comme :

FruitsCount = WorksheetFunction.SumProduct(WorksheetFunction.VLookup(Range("Caddie_Compo"), Range("Paniers_Compo"), 2, False);Range("occurence_panier_in_caddie") )

Mais bon le vlookup ne marche déjà pas.

Merci

Pourquoi tu cherches une autre fonction par macro ? celle que je t'ai donné ne te convient pas ? pourtant elle donne bien le nombre de fruit pour tout le caddie ??

cdt

Bonjour à tous,

Je pense que notre ami Hubert devrait nous dire ce qu'il veut faire exactement

et surtout ce qu'il veut obtenir comme résultat

A+

Re-Bonjour à vous, en attendant plus de précision, j'ai trouvé l'équivalent de ma formule vba :

=SOMMEPROD(SOMME.SI(B2:B11;Paniers!A2:A11;C2:C11)*(Paniers!B1:E1=G3)*(Paniers!B2:E11))

avec G3 la cellule qui comporte le nom du fruit.

cdt

ketamacanna, je viens de voir ta dernière solution.

Je pense que je m'explique très mal, et désolé pour ça. J'ai beaucoup de mal à exprimer ce que je souhaite faire et comme je code très ma l, ça n'arrange pas les choses.

Tes deux méthodes marchent et c'est ce que je souhaite obtenir. Je veux sous VBA le coder en évitant les boucles "for". Donc la formule que tu proposes sans VBA c'est celle que j'imaginais pouvoir coder dans mon module VBA, car beaucoup plus simple.

En tout cas merci beaucoup, je vais tenter de la transcrire sous VBA, si cela ne marche pas, je m'inspirerai de ta macro (avec les boucles for).

Désolé pour les malentendus.

Merci du coup de main et bon dimanche!

Hubert41

Juste une petite question ? pourquoi tu ne veux pas de boucle for ? sinon pour connaître l'équivalent en vba de la fonction excel,

tu lances l'enregistreur de macro, tu te places dans une cellule, tu tapes la formule, après tu interromps l'enregistreur de macro, et tu obtiens la formule dans un module :

    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(SUMIF(R[-3]C[-6]:R[6]C[-6],Paniers!R[-3]C[-7]:R[6]C[-7],R[-3]C[-5]:R[6]C[-5])*(Paniers!R[-4]C[-6]:R[-4]C[-3]=R[-2]C[-1])*(Paniers!R[-3]C[-6]:R[6]C[-3]))"

tu peux après t'en inspirer pour retranscrire sans les références R1C1.

Si tu as des soucis, reviens vers nous.

Cdt

J'ai pas de problèmes particuliers avec les boucles "for" mais généralement les profs n'aiment pas trop ça. En tout pas pour l'utilisation que j'en fais. Donc si je peux m'en passer et le remplacer par une formule j'essaie de le faire.

Là en l'occurrence, je pense que le for n'est pas nécessaire car ta formule avec le sumif marche très bien également.

Merci pour la technique, c'est effectivement beaucoup plus rapide comme ça.

J'apprécie l'aide apportée.

A bientôt

Hubert41