Discussion :

[neirda17]

Bonjour à tous,

Pour mon travail j'ai besoin de créer une macro qui remplace une fonction index equiv pour éviter les calculs dès que je modifie une valeur de mon classeur.
J'ai un historique de 3 critères sur les colonnes B C et E. L'objectif de ma macro est d’afficher en Q37 la valeur de ma colonne C qui correspond à la combinaison Q7/I37 que l'on recherche dans les colonnes B et E.
Je ne sais pas si je suis très clair. Je joins mon classeur excel de base.

Merci à vous pour vos retours.

[ARTURO83]

Bonsoir,

Si j'ai bien compris, la macro crée les formules puis fige les résultats en les remplaçant par les valeurs obtenues pour éviter les mises à jour systématiques. La mise à jour se fait à la demande en cliquant sur un bouton affecté à la macro.

Code :

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

1
2
3
4
5
6
7
Sub Formules()
    Application.ScreenUpdating = False
    [Q37].FormulaArray = "=IFERROR(INDEX(C3,MATCH(1,(C2=R[-30]C)*(C5=RC[-8]),0)),0)"
    [Q37].AutoFill Destination:=Range("Q37:W37"), Type:=xlFillDefault
    Range("Q37:W37").AutoFill Destination:=Range("Q37:W65"), Type:=xlFillDefault
    Range("Q37:W65").Value = Range("Q37:W65").Value
End Sub

Cdlt

[neirda17]

Bonjour Arturo,

Merci pour ton retour.
La macro semble fonctionner. Par contre pourrais-tu m'expliquer un peu le code car je ne le comprend pas et j'aimerais pouvoir le réutiliser pour d'autres projets.

En te remerciant d'avance pour ton retour.

Cordialement,

[ARTURO83]

Bonjour

La ligne de code suivante:

Code :

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

[Q37].FormulaArray = "=IFERROR(INDEX(C3,MATCH(1,(C2=R[-30]C)*(C5=RC[-8]),0)),0)"

Equivaut à la formule Excel suivante en Q37 (validée avec CTRL + SHIFT + ENTREE)

Code :

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

=SIERREUR(INDEX($C:$C;EQUIV(1;($B:$B=Q7)*($E:$E=I37);0));0)

La ligne de code suivante recopie la formule sur la même ligne jusquà W37

Code :

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

[Q37].AutoFill Destination:=Range("Q37:W37"), Type:=xlFillDefault

La ligne de code suivante recopie la plage Q37:W37 sur toute la plage du tableau, ici Q37:W65

Code :

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

Range("Q37:W37").AutoFill Destination:=Range("Q37:W65"), Type:=xlFillDefault

La ligne suivante remplace les résultats obtenus par leurs propres valeurs, pour ne pas conserver les formules et évite les mises à jour à chaque changement de valeurs

Code :

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

Range("Q37:W65").Value = Range("Q37:W65").Value

Voilà, c'est tout

Cdlt

[neirda17]

Super merci beaucoup pour toutes ces précisions !

Bonne journée

[neirda17]

Re-bonjour,

Je ré ouvre le sujet car je n'arrive pas à faire fonctionner la macro sur un autre onglet. Je pense que le problème vient de la première ligne du code que je n'arrive pas à retranscrire pour un autre cas.
Pouvez-vous m'expliquer les chiffres à remplir sur cette première ligne et pourquoi vous choisissez ceux-ci (je pense que ça doit être en fonction des numéros de colonnes mais sans saisir exactement à quoi ils correspondent)
Je vous joins le fichier avec le deuxième onglet que j’aimerais retravailler.

Merci d'avance,

[ARTURO83]

Lorsqu'il y a une macro, il faut impérativement enregistrer le fichier en xlsm et non en xlsx.
Enregistrez votre fichier en Xlsm et redéposez le ici

[neirda17]

Oops désolé pour l'oubli. Ci joint le fichier au bon format.

[ARTURO83]

Pour comprendre les formules traduites en VBA

Code :

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

[Q37].FormulaArray = "=IFERROR(INDEX(C3,MATCH(1,(C2=R[-30]C)*(C5=RC[-8]),0)),0)"

C=column = colonne
R=row=ligne

C2=R[-30]C
Quand le chiffre qui suit la lettre C ou R est accolé directement à la lettre, cela correspond au n° de la ligne ou de la colonne, ici C2 = 2éme colonne soit colonne B. C'est ce qu'on appelle une référence absolue
Quand le chiffre qui suit est entre crochets, comme R[-30], cela signifie que l'on va chercher 30 lignes au-dessus (parce qu'il y a le signe -) de la cellule où l'on se trouve, s'il n'y avait pas de signe, ce serait 30 lignes au-dessous.
idem pour les colonnes C[3], ce qui signifie 3 colonnes après la cellule où l'on se trouve. C'est ce qu'on appelle des références relatives.

Dans votre fichier, il n'y pas le code, donc je, ne peux pas voir ce que vous avez fait par contre, avec ce que je viens d'expliquer ci-dessus vous devriez être capable de vous en sortir.

[neirda17]

J'ai tout compris et j'ai réussi à réadapter le code pour mon deuxième exemple.
Merci beaucoup pour ces éclaircissements

Bonne soirée,

Adrien.