Utiliser Index & Match avec 2 critères et une boucle en VBA

ReBonjour forum,

D'abord merci à tous de vos précieux posts, toujours très utiles!

Je reviens demander de l'aide car je bloque sur un truc qui j'en suis sure doit être tout bête.

J'ai posté un fichier exemple.

Je souhaite transcire en vba (pour ensuite l'associer à un bouton ) l'équivalent d'une formule index&Match avec 2 critères, donc en validation matriceille qui marche nickel sur excel.

Je bloque sur la partiee vba, j'ai regardé un peu partout et je ne trouve pas... quelqu'un pourrait-il m'aider svp ?

J'ai mis dans le fichier excel la formule que je veux utiliser.

le code est le suivant pour la partie vba:

Sub Filing2Criteria()

Dim LiCountry, LiServices, LiCurency, LiFees As Range

Dim F1, F2 As Worksheet

Dim i As Integer

Set F1 = Worksheets("AL")

Set F2 = Worksheets("TBFees")

Set LiCountry = F2.Range("A2:A13")

Set LiServices = F2.Range("B2:B13")

Set LiCurrency = F2.Range("C2:C13")

Set LiFees = F2.Range("D2:D13")

For i = 2 To 11

F1.Cells(i, 7).FormulaArray = WorksheetFunction.Index(LiCurrency, WorksheetFunction.Match(LiCountry = F1.Cells(i, 3) * (LiServices = F1.Cells(i, 4)), 0))

Next i

End Sub

Par avance, merci beaucoup

cdlt,

Toli.

Bonjour,

Voici un exemple pour inscrire dans la feuille "AL" la devise et le prix trouvés dans la feuille "TBFees" en fonction du pays et du service.

A+

Merci beaucoup je vais tester, enfin d'abord essayer de comprendre le code parce que mon fichier exemple est très simplifié par rapport à mon fichier réel.

J'ai vu que la macro ne reprend plus du toutIndex+Equiv. Juste pour savoir, qu'est ce qui coince dans ma macro initiale, parce que quand je clique sur exécuter, j'ai toujours un message d'erreur.

J'utilise très souvent Index+Equiv sur excel, mais comme le fichier devient vite très lourd et lent à l'utilisation s'il y a beaucoup de lignes (2000 par exemple...) ou si je dois utiliser cette formule sur plusieurs collones.

Je voudrais pouvoir passer sur vba pour pouvoir faire ce genre de mise à jour 'sur demande' (le fameux bouton).

Est-ce possible ou pas du tout ?

Merci par avance.

Toli.

ReBonjour,

est-ce qu'il serait possible de mettre des lignes d'explications pour les différentes instructions, il y a plein de d'éléments que je ne ma'îtrise pas du tout, alors je suis un peu perdue...

merci beaucoup par avance.

Toli.

Bonsoir,

frangy a déjà résolu le problème, donc sans autre ambition que proposer un autre code pour le même résultat.

Hello,

Merci à vous 2, les 2 solutions proposées fonctionnent nickel. Mais ... (et désolée d'insister ) je voulais pouvoir utiliser la formule index+equiv en vba, et comprendre ce qui clochait avec ma macro d'origine.

J'avais prévu de l'utiliser sur des fichiers beaucoup plus complexes, et je voulais essayer sur le fichier exmeple tout simple pour ensuite pouvoir l'adapter selon mes besoins sur les fichiers réels.

Est-ce que l'un de vous pourrait m'aider ou alors est-ce qu'il est impossible d'utiliser cette formule dans ce but là en vba ?

Merci bcp par avance.

Toli.

Personnellement, je suis moins à l'aise avec les formules Excel et donc leur intégration dans du code VBA, j'ai essayé de retranscrire ta formule VBA dans la feuille Excel... sans succès

pas grave, merci de ton retour rapide.

La formule excel que je souhaite retranscrire en vba est dans les colonnesE et F du fichier exemple.

Je laisse la question ouverte si jamais quelqu'un veut bien prendre le temps de regarder

Bonjour tout le monde,

Salut Toli et merci d’avoir conclu ton autre fil …. 5 mois après

Pour écrire une formule en VBA comme tu le désires, je sais que tu dois utiliser l’instruction Evaluate.

J’ai bien essayé de l’utiliser dans ton fichier – voir mes essais ci-joints – mais ça ne fonctionne pas non plus.

Je te propose alors que tu écrives la fonction désirée dans la cellule G2 et que sur cette base je continue à chercher également (mais sans garantie).

A tout hasard, tu peux aller voir un fil qui traite du sujet (voir surtout la toute première réponse de Galopin) : https://forum.excel-pratique.com/excel/formule-matricielle-en-code-vba-t38296.html

Cordialement.

Salut à tous,

La formule excel que je souhaite retranscrire en vba est dans les colonnes E et F du fichier exemple.

Tu peux effectivement reproduire la formule Excel :

F1.Cells(i, 7).FormulaArray = "=INDEX(LiCurrency,MATCH(1,(LiCountry=AL!" & F1.Cells(i, 3).Address(0) & ")*(LiServices=AL!" & F1.Cells(i, 4).Address(0) & "),0))"

Ou afficher le résultat de cette même formule

F1.Cells(i, 7) = Application.Evaluate("INDEX(LiCurrency,MATCH(1,(LiCountry=AL!" & F1.Cells(i, 3).Address(0) & ")*(LiServices=AL!" & F1.Cells(i, 4).Address(0) & "),0))")

Mais très sincèrement, je pense que tu ne choisis pas le chemin le plus facile car VBA t’offre la possibilité de traduire "simplement" l’action à réaliser.

Voici une explication de mon code

Dans la feuille "AL", on balaye la colonne "Pays" et pour chaque cellule renseignée, on cherche une correspondance dans la colonne "Pays" de la feuille " TBFees".

Si une correspondance est trouvée, on vérifie si le service associé au pays correspond également.

Si c’est le cas, on copie la devise et le prix associés.

Dans le cas contraire, on continue la recherche et ainsi de suite jusqu’à la fin de la liste.

 Sub Filing2Criteria()
Dim F1, F2 As Worksheet
Dim i As Integer
Dim Cel As Range, C As Range
Dim firstAddress As String
    Set F1 = Worksheets("AL")
    Set F2 = Worksheets("TBFees")
    Application.ScreenUpdating = False
    'On balaye toutes les cellules renseignées de la colonne C (Pays) de la feuille "AL"
    For Each Cel In F1.Range("C2:C" & F1.Range("C" & Rows.Count).End(xlUp).Row)
        'On cherche une correspondance dans la colonne A de la feuille "TBFees"
        Set C = F2.Columns(1).Find(Cel, , xlValues, xlWhole)
        'Si cette correspondance est trouvée
        If Not C Is Nothing Then
            'On mémorise l'adresse de la première cellule qui contient cette correspondance
            firstAddress = C.Address
            'puis on fait une boucle afin de trouver toutes les autres cellules qui contiennent la correspondance
            Do
                'Si les Services correspondent également
                If C.Offset(0, 1) = Cel.Offset(0, 1) Then
                    'Alors, on copie la devise et le prix
                    C.Offset(0, 2).Resize(, 2).Copy Cel.Offset(0, 2).Resize(, 2)
                    'Et on sort de la boucle
                    Exit Do
                End If
                'Si les Services ne correspondent pas, on reprend la recherche
                Set C = F2.Columns(1).FindNext(C)
            'Et ainsi de suite jusqu'à la fin de la liste (tant que l'on ne retrouve pas la première adresse mémorisée)
            Loop While Not C Is Nothing And C.Address <> firstAddress
        End If
    Next Cel
    Set C = Nothing: Set F2 = Nothing: Set F1 = Nothing
End Sub

A+

Salut à tous,

Merci beaucoup pour les réponses détaillées. Vais regarder tout ça plus en détail (histoire de m'assurer que je comprends tout bien) pour tester avant de clôturer le post (pas dans 5 mois... )

Je vous tiens au courant!

@+

Toli.