Excel: variable dans une formule Résolu/Fermé

Question

Bonjour,

Dans un répertoire, j'ai un certain nombre de fichiers avec un total dans une cellule (colonne L  Ligne 31) et un fichier récap qui reprend tous les noms de ces fichiers. Je veux avoir ce total devant chaque nom de fichiers qui se met à jour automatiquement.
Pas de problème pour récupérer la valeur en faisant référence à la cellule du fichier en question.
voici un exemple de la formule:
='Z:\STOCK\Stock 2013\[MXX 01 - Licence 1964.xls]Feuil1'!$L$31

Simplement vu le nombre de fichiers, je voudrais pouvoir dupliquer cette formule dans mon tableau récap sans avoir à changer manuellement le nom de la feuille.

J'ai essayé avec des concaténations, la formule me parait bonne mais elle n'est plus prise comme formule mais comme du texte.

J'ai pensé à déclarer ma première colonne de mon tableau récap (qui contient tous les noms des fichiers du répertoires) comme variable et faire référence à cette variable dans la formule (à la place du nom en dur) mais je ne sais pas comment le faire.
Est-ce que quelqu'un a une idée pour m'aider. 
Ce serait sympa et m'éviterait un bon bout de manip.
D'avance merci

Patrice33740 · Answer

Bonjour, 

« J'ai essayé avec des concaténations, la formule me parait bonne mais elle n'est plus prise comme formule mais comme du texte »

Tu y étais presque, il suffit de faire :
=INDIRECT("texte")

BOURICOT91 · Answer

Voici mon essai:
=INDIRECT("CONCATENER($B$1;A7;$B$2)")
et voici malheureusement le résultat #REF!

Patrice33740 · Answer

Désolé, ça ne fonctionne pas si les classeurs sont fermés !

Regarde cet article :
https://excel.developpez.com/faq/?page=Recherche#IndirectClasseurFerme

BOURICOT91 · Answer

Donc ce n'est pas la solution car je dois pouvoir sortir la liste récap à jour sans avoir à ouvrir tous les fichiers.
Moi je veux juste insérer une fois pour toute les formules (à la place de les faire manuellement).
C'est pour cela que j'avais penser à la notion de variable dans la cellule pour avoir le même texte dans la colonne qui récupère tous les totaux.

As-tu déjà travaillé avec une variable dans une formule?

Merci

BOURICOT91 · Answer

Bonjour,

Je n'ai pas pu retravailler sur ce sujet depuis une semaine (d'autres priorités!).

Mais je suis au même stade càd que je voudrais dupliquer la formule dans la feuille récap mais la formule n'est plus reconnue.
C'est pour cela que j'avais pensé à faire référence à une variable dans la formule pour récupérer le nom des feuilles qui se trouverait dans une colonne.

Merci pour vos commentaires.

foo · Answer

Bonjour

test avec des varriables
sub test
Chemin = "Z:\STOCK\Stock 2013\"
Fichier = "MXX 01 - Licence 1964.xls"
Test = "'" & Chemin & "[" & Fichier & "]Feuil1'!$L$31"
Debug.Print Test
end sub

A+
Maurice

BOURICOT91 · Answer

Bonjour,

Je n'y arrive pas.
Pour faire simple (et étant donné que c'est juste pour une fois) y a t'il un moyen de  faire reconnaitre cette chaine de caractères en Formule?
='Z:\STOCK\Stock 2013\[MXX 01 - Licence 1964.xls]Feuil1'!$M$31.
C'est ce qui apparait lorsque j'insère la formule manuellement (en faisant le lien en recherchant la cellule en question dans le fichier ouvert).
Quand je concatène les différentes parties de la chaines à partir de 3 colonne par exemple, la cellule résultante fait apparaitre la formule en toute lettre mais n'est pas reconnue comme formule.
Merci

foo · Answer

bonjour 
donne au moin un model de ta feille et ta macro

car la je voie M$31 et un autre L$31

tu veux faire varier ta plage ?????

A+

Maurice

foo · Answer

Bonjour

voila avec toutes les variable

Chemin = ThisWorkbook.Path & "\"
Fichier = "CréerPack.xls"
Onglet = "Pack"
Plage = "$A$" & 2
Test = "='" & Chemin & "[" & Fichier & "]" & Onglet & "'!" & Plage
Debug.Print Test
Range("C1").Formula = Test
' ou
Range("C2").Formula = "='" & Chemin & "[" & Fichier & "]" & Onglet & "'!" & Plage

A+
Maurice

BOURICOT91 · Answer

Bonjour à tous,

Voici des fichiers tests pour que vous voyez bien ma problématique.
Dans un répertoire (ex c:\QTEMP\TEST\) j'ai des fichiers individuels (ici A.xls, B.xls et C.xls) et un fichier bilan.xls.
Dans ce fichier bilan.xls, j'ai 
- en colonne A le nom de chacun des fichiers individuels se trouvant dans le répertoire
- en colonne B la valeur de la cellule L31 du fichier individuel
- en colonne C la valeur de la cellule M31 du fichier individuel

Mon but est de pouvoir générer les formules des colonnes B et C du fichier bilan.xls automatiquement (sans avoir aller pointer les 2 cellules pour chaque fichier individuel manuellement) sachant que j'ai minimum 1500 fichiers individuels.

Je voudrais bien vous envoyer mes fichiers tests dans la discussion mais apparemment on ne peut pas joindre des fichiers.
Merci pour votre aide car il faut que je finisse ce boulot d'ici la fin de la semaine et je me vois mal rentrer dans chaque fiche individuelle pour compléter le tableau bilan.

Patrice33740 · Answer

Bonjour,

Essaies ce code :
Option Explicit
Sub GénérerLesFormules()
Dim c As Range        'Cellule
Dim r As String       'Répertoire

  'Définir le répertoire contenant les fichiers
  r = "C:\QTEMP\TEST\"
  'Analyser chaque valeur de la colonne A
  For Each c In Intersect(Columns("A:A"), ActiveSheet.UsedRange)
    'Si le fichier existe et si son nom est terminé par .xls ...
    If Dir(r & c.Value) > "" And _
       Mid(c.Value, InStrRev("." & c.Value, ".")) = "xls" Then
      ' ... écrire les formules en colonne B et C
      c.Offset(, 1).Formula = "='" & r & "[" & c.Value & "]Feuil1'!L31"
      c.Offset(, 2).Formula = "='" & r & "[" & c.Value & "]Feuil1'!M31"
    End If
  Next c
End Sub

BOURICOT91 · Answer

J'ai copié tel quel ton code dans une nouvelle macro.
et il ne se passe rien.
Je la lance pas à pas et la ligne Sub est en sur brillance en jaune et puis plus rien.
Je ne vois pas ce qui cloche.

Merci

BOURICOT91 · Answer

SUPER, cela fonctionne.
En fait, en lisant tes codes la première fois, j'avais l'impression qu'il fallait uniquement le nom du fichier dans la colonne A (sans l'extension). J'avais donc retirer le .xls.
Et donc il ne trouvait pas les fichiers.
J'ai modifié mon fichier bilan en rajoutant l'extension des fichiers.
et bingo j'obtiens les résultats avec les formules dans les cellules.
je vais adapter maintenant cette macro sur le bon fichier.

MERCI BEAUCOUP, tu me sauves la mise pour mon boulot.
Je ne voyais vraiment pas comment m'en sortir.

Je te tiendrai au courant sur le résultat "grandeur nature".

Trop cool Merci encore

BOURICOT91 · Answer

J'ai commencé sur les vrais fichiers et c'est OK.
J'interviens sur la macro pour changer ponctuellement le chemin (car j'ai un certain nombre de répertoires).
C'est parfait.

Merci encore

Très bonne fin de journée

foo · Answer

Bonjour

Voila une macro sans Function
et avec la lecture des fichier
http://cjoint.com/?3HtridVa7Om

A+

Maurice

Excel: variable dans une formule

15 réponses

Discussions similaires