Boucle avec formule et variables

Bonjour,

Débutante en VBA, j'essai de faire une macro pour avoir le résultat d'une formule et la glisser.

Je ne peux pas le faire directement sans macro car tout simplement excel bug en glissant la formule sur 2220 ligne et 6 colonnes.

Pour résumer ma formule est

SIERREUR(INDEX(Feuil1!D:D;EQUIV(Feuil4!A2&Feuil4!D1;Feuil1!C:C&Feuil1!I:I;0));"")

Avant de mettre des variables pour les ligne et colonnes, j'ai essayé de coder la formule de base mais ca ne marche pas

Mon programme VBA simplifié de base pour la formule est

Sub essai1()

For i = 2 To 15

Dim i As variable

Sheets("feuil4").Range("D" & i).FormulaArray = _

"=IFERROR(INDEX(Feuil1!C,MATCH(Feuil4!RC["&-i&"]& Feuil4!R[-1]C,Feuil1!C[-1]&Feuil1!C[5],0)),"""")"

Next

End Sub

J'ai comme message "Erreur de compilation attente : fin d'instruction " et il me surligne mon code en rouge

Est ce que quelqu'un pourrait me dire ce que j'ai oublié ?

Merci d'avance

M

Bonjour

A tester

Sub essai1()
Dim iAs Integer

  For i = 2 To 15
    Sheets("feuil4").Range("D" & i).FormulaArray = _
    "=IFERROR(INDEX(Feuil1!C,MATCH(Feuil4!RC["& -i &"]& Feuil4!R[-1]C,Feuil1!C[-1]&Feuil1!C[5],0)),"""")"
  Next

End Sub

Bonsoir,

Es-tu sûre que la formule fonctionne ?

L'as-tu testée manuellement ?

Je suis un peu surpris de voir une matrice définie par une concaténation : C:C&I:I

Cordialement

Ferrand

Bonjour ,

Merci pour vos retour,

Oui ca marche mais je n'ai pas le résultat souhaité. Je n'ai pas le même résultat qu' avec la formule

Cordialement

M

Si tu n'as pas le résultat attendu, ne serait-ce pas un problème ?

Oui en effet j'essaie de voir sur les publications des autres comment je peux fixer la colonne de sélection.

En gros : il faut que je trouve comment fixer une cellule en VBA (l'équivalent des $ ) car

SIERREUR(INDEX(Feuil1!D:D;EQUIV(Feuil4!A2&Feuil4!D1;Feuil1!C:C&Feuil1!I:I;0));"")

la cellule que j'ai surligné en vert je n'arrive pas à la fixer

la cellule en bleu je n'arrive pas à l'avoir avec mon code VBA : pour la première ligne ca me donne la cellule de la colonne b, pour la deuxième logne c'est bon mais pour la troisième ligne c'est la colonne AZ !!!

Du coup je cherche encore....

Merci pour votre Aide

M

Je ne sais pas ce que calcule ta formule, du moins ce qu'elle veut calculer. Chaque chose en son temps. Je la lis et je m'arrête sur 2 points : les arguments de la fonction utilisée. Il s'agit d'EQUIV.

J'ai quelque doute sur le premier argument qui concatène 2 cellules mais il est ma foi tout à fait possible si la recherche concerne une chaîne ou un type apparenté.

Mais sur le second, la fonction attend une matrice ou une plage, et j'ai beau tourner la chose dans tous les sens, je ne vois pas comment l'expression de ta formule pourrait renvoyer une matrice dans laquelle EQUIV opèrerait sa recherche.

Je vous ai mis un fichier simplifié en PJ

Je cherche a récupérer pour chaque emplacement la référence correspondante et ceci pour chaque mois

pour commencer je l'ai fait pour le premier mois et les 14 premiers emplacements...

La formule sans VBA fonctionne parfaitement c'est juste que le fichier bug du coup on m'a dit que la solution était de faire une macro avec une boucle pour alléger le fichier.

J'espère que j'ai été clair

et merci beaucoup pour votre aide

Cordialement

M

Le fichier était trop grand du coup j'ai du supprimer le code VBA

23ocp-simplifie.xlsx (36.21 Ko)

Il me semble que la formule que tu cherches à faire est celle-ci :

=INDEX(Feuil1!$D:$D;EQUIV(1;(Feuil1!$C:$C=A2)*(Feuil1!$I:$I=D$1);0))

Matricielle.

A toi de confirmer.

Ferrand


Petit ajustement pour l'étendre vers la droite (et le bas, c'était déjà fait)

=INDEX(Feuil1!$D:$D;EQUIV(1;(Feuil1!$C:$C=$A2)*(Feuil1!$I:$I=D$1);0))

Merci pour la formule

Les deux formules fonctionnent mais le problème est que j'ai un fichier avec 2220 lignes (emplacements) et 12 colonnes (mois) quand je fais glisser ma formule Excel bug...

On m'a dit que la solution à mon problème est de faire un programme VBA avec une boucle pour alléger le fichier et c'est la ou je bloque car je suis encore débutante en VBA

Et encore merci beaucoup pour ton aide

M

Le problème des formules est que cela alourdit assez vite un fichier et le ralentit dans son utilisation. Dans ce cas on a intérêt à utiliser VBA pour conserver un fichier réactif. Mais utiliser VBA pour insérer des formules ne règle pas ce problème, tu auras toujours des formules.

A mon sens, VBA peut faire le travail sans insérer de formule et il n'y a aucune raison de s'en priver.

Ton fichier avec macro de ce type.

NB- ligne 53-54 (à une ligne près), tu as 2 lignes avec des valeurs identiques en C et I. Je ne sais si cela est normal ou non, mais que ce soit formule ou macro, la 2e ligne n'a aucune chance d'être extraite et donc la valeur de D sur cette ligne ne se retrouvera jamais en Feuil4.

Cela m'a fait planter Excel à la première version de la macro qui ne prévoyait pas ce cas, la boucle principale balayant systématiquement la colonne C, j'ai donc dû rajouter en cas d'égalité non trouvée une condition vérifiant la valeur de la ligne précédente pour incrémenter sur C sans incrémenter sur A(Feuil4) dans ce cas.

Merci beaucoup M Ferrand

Oui effectivement je peux avoir pour le même mois deux références pour le même emplacement. Du coup je pense que je dois généraliser ma condition d'inégalité car quand j'ai essayé le programme sur plusieurs mois, Excel m'indique un problème au niveau du code

Rechercher des sujets similaires à "boucle formule variables"