Renvoi de valeur dans formule pour choix de worksheet

Bonjour,

Après avoir pas mal cherché à droite à gauche sans vraiment trouver ce qui me satisfait, je m'en remets à vous

Mon fichier est composé de 3 feuilles, deux sont nommées avec juste un numéro de différence (ex: XXXXXXX #005 et XXXXXXX #106), elles ont la même structure avec juste des valeurs qui changent (même liste de pièces). La dernière a pour but de reprendre des valeurs dans une des deux feuilles. Pour cela, une cellule avec un menu déroulant à deux choix (005 et 106) est supposée faire changer d'une feuille à l'autre.

Une macro a ensuite été créée pour faire en sorte que j'ai juste à rentrer la valeur de ligne (variable rownbr) correspondante à la pièce sur une des deux premières feuilles et la ligne est créée sur la troisième feuille avec les infos qui m'intéressent. Le problème est que j'aimerais que les valeurs se mettent à jour en fonction de la valeur de la cellule choisissant le numéro de feuille et que les cellules se décalent si je rajoute une ligne sur les deux premières feuilles.

J'ai utilisé jusque là deux solutions:

- Soit écrire directement le nom de la feuille :

Cells(2, 15).FormulaR1C1 = "='XXXXXXX #" & R2C4 & "'!R" & rownbr & "C2"

Il y a deux problèmes, le premier est que pour une raison que je ne connais pas la cellule R2C4 renvoie quelque chose de vide, et la deuxième est que cela renvoie la valeur de la cellule et non sa référence, et je ne trouve pas de moyen d'envoyer la référence (c'est à dire la cellule D2).

- Soit utiliser la formule INDIRECT :

partdsc = Chr(34) & "$B$" & rownbr & Chr(34)
ActiveCell.Formula = "=INDIRECT(""'XXXXXXX #""&" & "$D$2" & "&""'!""&" & partdsc & ")"

Le problème maintenant est que le numéro de ligne ne se met pas à jour si j'insère une ligne sur les feuilles, or ayant un certain nombre de pièces je ne veux pas avoir à refaire la manip à chaque fois Par contre, grâce à la formule Indirect le numéro de feuille se met bien à jour

Voilà, j'ai essayé d'être le plus clair possible et j'attends vos idées avec impatience !

Merci d'avance !

Salut,

La prochaine fois, fournis-nous un fichier modèle, s'il-te-plait.

Dans le fichier ci-joint, la démonstration d'une solution. Probablement pas utilisable dans ton fichier réel

Tu dois choisir au préalable ta feuille en A1 de la feuille ''Base".

Amicalement.

Bonjour Yvouille, merci de ta réponse !

Ayant quelques infos confidentielles je n'y avais pas pensé, mais j'ai refait un fichier sans toutes les données qui fonctionne de la même manière.

Dans cette configuration le fichier marche mais je ne peux pas insérer de ligne sans avoir à refaire la procédure.

Dans l'idée, la macro me permet juste de créer la liste sur la feuille "Liste_piece" mais ne sera pas réutilisée par la suite, et l'idéal serait que tout se mette à jour directement (grâce au fait qu'excel puisse déplacer "tout seul" les cellules si on insère des cellules)

Par exemple, en sélectionnant la ligne 6 de "Liste_piece", on rentre le numéro de la ligne correspondante sur les deux feuilles (ici la ligne 5 qui correspond à la "PART 1", et la ligne se remplit automatiquement. Même chose pour les 6 lignes suivantes.

En espérant avoir été assez clair, merci par avance !

Salut,

Je présume que tu es parti dans une direction assez incorrecte et au lieu de tenter de corriger ce qui pèche, il vaudrait peut-être mieux chercher une nouvelle solution.

De plus, tes explications sont assez imprécises.

Par exemple :

thomasglu a écrit :

Dans cette configuration le fichier marche mais je ne peux pas insérer de ligne sans avoir à refaire la procédure.

thomasglu a écrit :

la macro me permet juste de créer la liste sur la feuille "Liste_piece" mais ne sera pas réutilisée par la suite

thomasglu a écrit :

Par exemple, en sélectionnant la ligne 6 de "Liste_piece", on rentre le numéro de la ligne correspondante sur les deux feuilles (ici la ligne 5 qui correspond à la "PART 1", et la ligne se remplit automatiquement.

Ensuite, montre ton fichier comme il devrait se présenter avant que la macro souhaitée se déclenche, afin que l’on voit que devrait s’inscrire où (sans feuille inutile ou provisoire). Tout au plus tu pourrais inscrire manuellement les données souhaitées aux endroits souhaités et les marquer en rouge afin que l’on voit que ce sont des données indicatives.

J’ai bien compris que tu veux déclencher ce code en sélectionnant une ligne ou sélectionnes-tu la ligne et lances-tu ensuite le code d’une autre manière (par exemple par un bouton) ?

A te relire

Salut,

Désolé je n'ai pas trop l'habitude de faire ce genre de post, j'arrive en général à trouver par moi même mais je pense que comme tu dis je n'arrive pas à chercher une autre solution à mon problème, n'étant pas assez calé sur vba.

Pour avoir le fichier avant application des macros, il suffit de supprimer les lignes 7 à 12 de la feuille "Liste_piece". La ligne 6 (du moins la sélection A6:J6) est le format type que je souhaite dupliquer à l'ensemble des pièces (dans le fichier envoyé, il y en a 7, mais sur mon fichier de base on est plus aux alentours de 300).

Ensuite, je procède de la manière suivante (exemple pour la "PART 2"):

• Je sélectionne une cellule de la ligne où je souhaite affecter les données de la "PART 2". Je sélectionne la cellule A7 de la feuille "Liste_piece".
• J'ai assigné le raccourci clavier de la macro sur Ctrl+M, alors je fais ce raccourci.
• La macro me demande de rentrer le numéro de ligne où se trouve la "PART 2" dans les feuilles "XXXXXXX #005" et "XXXXXXX #106". Ici, la pièce se trouve sur la ligne 12, je renseigne donc ce numéro dans la boite de dialogue. Ce numéro est stocké dans la variable rownbr.
• La macro copie la sélection A6:J6 de la feuille "Liste_piece" et la colle sur la cellule A7 que j'avais sélectionnée et modifie le numéro de ligne présent dans les formules collées grâce à la variable rownbr.
• Ensuite, je répète l'opération pour les 5 pièces suivantes: je sélectionne la cellule A8 et renseigne la ligne 18 pour avoir la "PART 3", cellule A9 et ligne 25 pour avoir la "PART 4", etc... jusqu'à la "PART 7".

Mon problème est le suivant :

Imaginons que j'ai à rajouter une pièce entre "PART 3" et "PART 4", je vais insérer 6 lignes sur les feuilles "XXXXXXX #005" et "XXXXXXX #106" au dessus de la ligne 24 de chaque feuille.

De retour sur la feuille "Liste_piece", les valeurs affichées ne sont plus les bonnes, car les numéros de ligne que j'avais renseigné avant ne sont plus valables puisque tout a été décalé.

Il me faudrait donc refaire la macro sur les pièces après "PART 3" en renseignant le nouveau numéro de ligne correspondant. C'est cette tâche là que j'aimerais éviter

J'aurais pu ne pas faire de macro et copier-coller manuellement les cellules en changeant à chaque fois le numéro de ligne manuellement dans la formule, mais je pensais que ce serait plus simple d'automatiser la chose en demandant seulement le numéro de ligne correspondant (d'où la procédure expliquée plus haut).

J'espère avoir répondu à toutes vos questions et m'excuse de mon manque de clarté

Merci !

Pour résumer, mon principal problème ne provient pas de la macro mais de la formule que je mets dedans, je m'explique:

Voici la formule présente en A6 de la feuille "Liste_piece":

=INDIRECT("'XXXXXXX #"&$D$2&"'!"&"$B$5")

En D2 de cette feuille "Liste_piece" se trouve le numéro que je veux pouvoir modifier (soit 005, soit 106). La référence B5 souhaitée est par contre sur la feuille "XXXXXXX #005" ou "XXXXXXX 106", suivant la valeur de A2 de la feuille "Liste_piece".

Le problème est qu'il faut que je mette cette valeur entre guillemets pour utiliser la formule INDIRECT() mais ça implique que la référence B5 ne bouge pas si j'insère une ligne au dessus par exemple.

Au contraire, si je n'utilise pas INDIRECT(), il m'est impossible d'extraire la valeur de la cellule D2 de la feuille "Liste_piece" qui permet de renvoyer à la bonne page.

Voici un exemple qui fonctionne mais qui ne se met pas à jour si je change la valeur de D2 :

test = "'2020 RC F #" & Cells(2, 4) & "'!$B$" & rownbr
Cells(slct, 15).Formula = "=" & test

Mon but serait de combiner les 2 manières mais c'est là où je bloque

Merci d'avance !

Salut,

Je ne suis pas très à l’aise avec les formules hyper compliquées du genre que tu utilises dans ce fichier - il faut chercher des heures ou a été placé faux le 84ème apostrophe - et je suis d’autant moins décidé à trop chercher – là, après 40 minutes, je calle – que je suis persuadé qu’il y a des solutions beaucoup plus simples par macro, quelque chose du genre que je t’ai démontré dans le premier fichier joint.

Je ne saisis toujours pas ton but final – d’ailleurs, contrairement à ce que je t’ai demandé, tu ne l’expliques pas, tu buttes sur ton idée de formules – mais j’imagine par exemple une solution où toutes des données de la feuille ‘’ Liste_piece’’ s’actualiserait d’un seul coup après que tu aies modifié la donnée dans la cellule D2 de cette feuille, en allant chercher les bonnes informations sur les deux autres feuilles, quelles que soient les positions de tes données ‘’PART 1’’, ‘’PART 2’’, etc. sur l’une ou l’autre des feuilles source concernées.

Je vois donc deux possibilités de continuer :

• Soit tu trouves mon idée intéressante, tu relis mes précédentes instructions et tu y donnes suite
• soit tu indiques ce fil comme ‘’Résolu’’ – car peu de membres s’intéressent encore à un fil déjà bien entamé – et tu en recommences un autre sur de meilleures bases. Par meilleures bases je veux dire entre autres que tu dois déjà placer ton fichier démo dès le départ, que tu dois informer les membres que ton code se déclenche par Ctrl + m, que tu dois expliquer avec des références aux objets Excel de quelle manière tu travailles et ce que tu attends comme résultats.

Fais bien attention à ne pas écrire trop de textes bidon qui prolongent la lecture inutilement et qui découragent bien vite.

Amicalement.

En complément de mon message d'hier, je veux quand même t'expliquer un ou deux essais que j'ai tentés :

Avec ta formule, tu obtiens le résultat ci-dessous, avec des références absolues (il y a des $ à chaque référence aux cellules). Il semble donc normal que ces références ne suivent pas le déplacement des lignes concernées.

J'ai alors tenté de supprimer tous tes $ dans ton code et j'ai obtenu le nouveau résultat ci-dessous. Mais là encore, le déplacement des lignes concernées est impossible.

Je me suis alors intéressé aux apostrophes et guillemets de la formule et me suis dit qu'il y en avait quand même certains de trop. tu vois qu'en en effaçant une partie inutiles, selon les marques en jaune, la formule fonctionne toujours. Mais toujours pas le déplacement des lignes

Je pense donc que cette manière de faire est impossible, mais n'en suis pas certain.

Persuadé que des solutions plus simples par macro sont possibles, j'ai abandonné.

Amicalement.

Bonjour Yvouille,

Merci énormément pour tout le temps passé à essayer de résoudre mon problème !

Effectivement j'avais passé beaucoup de temps à chercher quelle apostrophe était mal placée...

Le but est exactement ce que tu avais compris, en sachant que les 2 feuilles auront toujours la même structure (si j'insère une ligne pour ajouter une pièce, cela sera fait sur les deux feuilles).

J'avais une autre piste en utilisant une condition SI qui selon la valeur en D2 renvoie une feuille ou l'autre, comme ceci :

ActiveCell.FormulaR1C1 = _
        "=IF(R2C4=""005"",'XXXXXXX #005'!R" & rownbr & "C2,'XXXXXXX #106'!R" & rownbr & "C2)"

On m'avait alors dit d'essayer d'utiliser INDIRECT au cas-où on viendrait à rajouter une troisième référence en D2, mais ce cas a très peu de chances d'arriver, donc je vais repartir dans cette direction.

Je vais donc mettre le sujet en résolu.

Merci encore pour tout cela !

Amicalement,

Thomas