Problème de formule

[DAVIDRUN]

Bonjour à toutes et à tous,

Voici une procédure qui insère la formule suivante dans une cellule (E17) en Feuil2

Sub Formule()
Dim a As String
Dim b As String

b = Feuil1.Range("B65536").End(xlUp).Row
a = Feuil1.Range("A65536").End(xlUp).Row
Feuil2.Range("F15").Formula = "=INDEX(Feuil1!A2:B" & b & ",MATCH(Feuil2!E15,Feuil1!A2:A" & a & ",0),MATCH(""Nb"",Feuil1!A1:B1,0))"

End Sub

La formule qui en résulte est la suivante:
=INDEX(Feuil1!A2:B33;EQUIV(Feuil2!E15;Feuil1!A2:A33;0);EQUIV("Nb";Feuil1!A1:B1;0))


On voit que la plage de recherche en Feuil1 a été définie à (A2:B33) et (A2:A33) grace aux 2 variables a et b qui définissent la dernière ligne de la colonne correspondante.
Jusque là tout va bien, je n'ai plus qu'à saisir manuellement une valeur en Feuil2 cellule E15 pour
que la formule me renvoi une autre valeur en F15.

Cependant, mon problème est le suivant:
Sachant que cette formule est insérée une fois et que je ne souhaite pas y revenir, quelle fonction dois-je ajouter
dans ma formule pour que la plage(Feuil1!A2:B33 et Feuil1!A2:A33) varie en fonction des ajout de lignes?

Sauf erreur, il me semble que la fonction DECALER serait adaptée à cette problèmatique....
Hélas, je ne sais pas comment ajouter cette nouvelle fonction dans ma formule.

D'autant plus que c'est VBA qui ecrira la formule.
J'ai joint un fichier pour plus de précision.

D'avance merci de votre aide.


à +
DAVIDRUN

 

[Minick]

Salut,

C'est a verifier mais ca doit etre quelque chose dans ce genre

INDEX(INDIRECT(""Feuil1!A2:B""&COUNTA(Feuil1!B:B)),MATCH(Feuil2!RC[-1],INDIRECT(""Feuil1!A2:A""&COUNTA(Feuil1!A:A)),0),MATCH(""Nb"",Feuil1!R[-14]C[-5]:R[-14]C[-4],0))


@+ Minick

 

[DAVIDRUN]

Bonsoir Minick,

je te remercie pour ta réponse, qui de plus a été rapide.
J'ai modifié un peu ta formule sur la fin car je dois faire référence aux cellules
A1:B1 sur la FEUIL1 (A1 intitulé de colonne A et B1 intitulé de colonne B)

Donc

Feuil2.Range("F19").Formula = "=INDEX(INDIRECT(""Feuil1!A2:B""&COUNTA(Feuil1!B:B)),MATCH(Feuil2!RC[-1],INDIRECT(""Feuil1!A2:A""&COUNTA(Feuil1!A:A)),0),MATCH(""Nb"",Feuil1!A1:B1,0))"

me donne la formule suivante:

=INDEX(INDIRECT("Feuil1!A2:B"&NBVAL(Feuil1!B:B));EQUIV(Feuil2!E19;INDIRECT("Feuil1!A2:A"&NBVAL(Feuil1!A:A));0);EQUIV("Nb";Feuil1!'A1':'B1';0))

Cependant, je me rends compte que cette formule ne fonctionne pas.

Elle ne fonctionne que sous cette syntaxe:

=INDEX(INDIRECT("Feuil1!A2:B"&NBVAL(Feuil1!B:B));EQUIV(Feuil2!E19;INDIRECT("Feuil1!A2:A"&NBVAL(Feuil1!A:A));0);EQUIV("Nb";Feuil1!A1:B1;0))

Ma question est simple à présent,
que dois-je modifier dans ma sub pour qu'elle me donne ce résultat?
J'ai éssayé pas mal de trucs sans aucuns résultat.
Je suis tout prêt du but.
Encore merci pour ton aide.

à+

DAVIDRUN

 

[Minick]

Salut,

Je ne comprend pas ceci:
A1:B1 sur la FEUIL1 (A1 intitulé de colonne A et B1 intitulé de colonne B)

Tu ne peux pas nommer la colonne A A1 car A1 est un mot reservé pour excel,
tout comme B1, B2,A2, etc... quand tu ne travailles pas en type de reference L1C1

De plus dans un EQUIV tu ne peux rechercher une valeur que sur
1 ligne x colonnes ou 1 colonne x lignes et pas
sur x lignes et x colonnes comme tu voudrais le faire si j'ai bien compris


J'ai donc besoin de plus d'information pour mieu comprendre
avec une pj ca serait parfait


@+ Minick

 

[DAVIDRUN]

Bonjour Minick,

Désolé j'ai oublié la pièce-jointe, tu verras ce sera plus clair en voyant
le résultat que je veux obtenir avec les formules et le tableau dans lequel se fait la recherche.

Merci encore de t'interesser à mon problème.

à +

DAVIDRUN

 

[DAVIDRUN]

Bonjour Minick,
Désolé j'ai oublié la pièce-jointe, tu verras ce sera plus clair en voyant
le résultat que je veux obtenir avec les formules et le tableau dans lequel se fait la recherche.

Merci encore de t'interesser à mon problème.

à +

DAVIDRUN

 

[Minick]

Salut,

Effectivement avec l'exemple c'est beaucoup plus clair

Voila le resultat:
Feuil2.Range("F19").FormulaR1C1 = "=INDEX(INDIRECT(""Feuil1!A2:B""&COUNTA(Feuil1!C2:C2)),MATCH(Feuil2!RC[-1],INDIRECT(""Feuil1!A2:A""&COUNTA(Feuil1!C1:C1)),0),MATCH(""Nb"",Feuil1!R1C1:R1C2,0))"


@+ Minick

 

[DAVIDRUN]

Salut Minick,

Super, j'ai enfin ma formule.
Merci milles fois pour ton aide.

Bon reveillon

à + sur XLD

DAVIDRUN