VBA : Saisir formules renvoyant vers d'autres feuilles

Question

Bonjour, 

voici ma problématiques

je dispose d'un fichier excel représentant le suivi des heures des salariés sur chantier sur plusieurs mois.

Le suivi se fait sur un tableau mensuel :
- en colonne A le nom des slariés
- en colonne B le totale des heures du mois pour chaque salarié (somme de la ligne)
- ensuite chaque colonne est une journée.

Chaque moi est stocké dans une feuille différente.

J'ai stocké chaque feuille de mon fichier dans un tableau en VBA

tWs () as worksheet

J'ai une première feuille qui s'appelle "Synthèse". Son but est de reprendre pour chaque salarié, le total des heures du chantier. Donc le total pour chaque salarié, de la case lui correspondant dans la deuxième colonne de chaque élement du tableau tWs.

Je pas que la valeur arrive dans la case, mais que la formule soit visible.

Pourriez-vous m'aider sur le codage de la création de cette formule. Je suppose qu'il faut utiliser la Range("Synthèse").formula ou quelque chose de similaire. Mais comme jeux veux que ce soit les formules qui attérissent dans les cases je ne sais pas trop comment m'en sortir.

Merci

Configuration: Windows 7 / Internet Explorer 9.0

Le Pingou · Answer

Bonjour,
J'ai stocké chaque feuille de mon fichier dans un tableau en VBA....
Que doit-on comprendre... ?
Pas simple de déchiffrer... le mélange entre VBA  et des formules ...!
Pouvez-vous reformuler la question.... ?

xuorel · Answer

mon fichier sert à suivre les heures des ouvriers d'une société qu'ils effectuent sur un chantier donné. 

Pour cela je créer une feuille par mois, contenant une grille ou je note les heures. La colonne B fait la somme dans chaque feuille des heures du mois de chaque salarié.

Comme le nombre de mois d'un chantier est variable, pour créer et gérer mes mois je passe par un tableau 
dim Tmois () as worksheet

Par ailleurs, j'ai une feuille "synthèse qui reprend la liste des ouvrier et fait la somme de leurs heures pour chaque mois.

j'aimerais automatiser ce calcul.

Ma contrainte, je ne veux pas que ce soit la valeur de la somme qui arrive dans chaque case (par exemple 143h pour Monsieur X).
je veux que la formule apparaissent, comme si j'avais fait à la main dans la base B3 de la feuille Synthèse, la somme de la case B3 des autres feuilles.
(quelque chose du style B3 = 'mois1'!B3+'mois2'!B3 etc

j'espère que j'ai été plus clair

Le Pingou · Answer

Bonjour,
Merci pour les informations.
Pour aider à la compréhension, ne serait-il pas plus simple d'avoir un extrait de votre fichier avec les notes directement sur la feuille... ? Le mettre sur https://www.cjoint.com/  et poster le lien dans cette discussion !

xuorel · Answer

voici : https://www.cjoint.com/?DGAlfgkeZQ8

ce fichier est le que mon client m'a soumis. Il à lui même créé chaque mois un par un. Et dans la feuille SYNTHESE, dans la colonne B il à lui même fait toutes les sommes à la main.

Pour lui éviter ce travail lourd dans le temps (quand il faudra ajouter les mois nouveaux au fur et a mesure), j'ai déjà automatisé la création de tous les mois (avec du code plus ou moins optimal, mais ca marche bien). Je me suis basé sur la création de 12 mois pour un chantier (mon client m'a dit que ça n'allait jamais au dela).

L'objet de notre échange concerne donc la manière de compléter les cases B7:B23 de la feuille Synthèse (qui est pour chaque case la somme de cette même case sur les autres feuilles)

Comme je le disais précédemment, je ne veux pas que les calcul se fassent de manière invisible au travers du code et que les cases ne récupère que le montant d'heures totales. Je veux qu'il final, les cases de la colonne B fassent apparaître les formules comme c'est le cas pour l'instant.

Iama · Answer

bonjour xuorel 

As-tu pensé à l'enregistreur de macro qui dans ce cas devrait de donner des idées pour compléter ta macro?

Cordialement

eriiic · Answer

Bonjour,

Et si tu donnais suite à tes autres questions ?
Comme ici : https://forums.commentcamarche.net/forum/affich-30555713-vba-probleme-avec-la-methode-range-find

eric

Le Pingou · Answer

Bonjour xuorel,
J'ai regardé le fichier, concernant : manière de compléter les cases B7:B23 de la feuille Synthèse
Vous le faite une fois et c'est tout. De cette manière :
Pour chaque feuille mensuelle vous nommez la plage [B9 :B26] par exemple pour janvier 2014 : [cumul_janvier] pour février [cumul_fevrier] etc. jusqu'à, décembre. 
Dans la feuille synthèse sélectionnez la plage [B9 :B26] et entrer la formule : =cumul_janvier+cumul_fevrier+.......+cumul_decembre et vous valider comme formule matricielle : par  Ctrl+Shift+Enter
Pour l'année suivante vous copier le classeur et vous changer uniquement l'année des noms des feuilles et c'est tout.
Pas besoin de procédure VBA.
Note : la remarque d'eriiic est très pertinente .... (amicales salutations à toi eriiic)

xuorel · Answer

Merci pingouin pour votre piste.
Cependant certaines contraintes me poussent à prendre du recul sur votre proposition :

- le fait que les feuilles correspondent au 12 mois de 2014 est un hasard pour l'exemple. Dans les faites les chantiers ont une durée variable, et le temps passant, ils vont progressivement avancer sur 201. Mon idée est donc de demander à l'utilisateur le mosi de début du chantier, et de créer de manière automatique 12 mois à partir de la.

- De même le nombre d'ouvriers sur un chantier est variable. Au moment de la création du chantier, je compte donc demander à l'utilisateur de valider une liste sur une feuille ad'hoc. Les mois se completeront alors automatiquement avec cette liste d'ouvrier.

Dans l'idée de tout automatiser, je pense ne pas avoir d'autre choix que de recourir au VBA. 

Dans cette optique, pourriez-vous me dire comment utiliser en VBA les formules matricielles ? 

merci

Iama · Answer

Rebonjour xuorel 

Bien que apparemment, tu as négliger la piste que je te conseillai,
je te propose de tester ceci:

Private Sub Charger_Formule()
'Réalisation de la formule de cumul

Dim caL As String
Dim f As Worksheet
Dim nB As Single 'nombre de noms

    Sheets("SYNTHESE").Select
    nB = Cells(9, 1).End(xlDown).Row
    
    Cells(9, 2).Select ' Première cellule des cumuls
    caL = "="
    For Each f In ActiveWorkbook.Sheets
      If f.Name <> "SYNTHESE" Then
      caL = caL & "+'" & f.Name & "'!RC"
      End If
    Next f
    
      ActiveCell.FormulaR1C1 = caL
      Selection.AutoFill Destination:=Range(Cells(9, 2), Cells(nB, 2)), Type:=xlFillDefault
End Sub

Patrice33740 · Answer

Bonjour à tous

Excusez l'incrust, 

Je voulais simplement rappeler d'Excel met à disposition une commande parfaitement adaptée à ce problème et relativement simple à mettre en oeuvre (et sans VBA) : La Consolidation des données.

Le Pingou · Answer

Bonjour xuorel,
Ma proposition est indépendante des mois effectifs mentionnés dans l'onglet des feuilles ce qui veut dire que vous commencez au mois que vous voulez. Concernant le nombre de personnes par projet, elle est valable de 1 à 17 personnes sans modification si que 4 personnes vous aurez les totaux et les autres seront [0].pour ces) 
Avez-vous simplement essayé la proposition ..... ?

xuorel · Answer

Pingou,

peux tu préciser ton propos lorsque tu dis "Ma proposition est indépendante des mois effectifs mentionnés dans l'onglet des feuilles ce qui veut dire que vous commencez au mois que vous voulez. " ?

merci

xuorel · Answer

Iama, deux remarques :

- dans la ligne suivante 
caL = caL & "+'" & f.Name & "'!RC"
à quoi sert le "RC" en fin de ligne ?

- dans ton autofill
Selection.AutoFill Destination:=Range(Cells(9, 2), Cells(nB, 2)), Type:=xlFillDefault 

n'est-ce pas plutôt ceci qu'il faudrait mettre 
Selection.AutoFill Destination:=Range(Cells(9, 2), Cells(nB+9, 2)), Type:=xlFillDefault

xuorel · Answer

Comment on déclare des formules matricielles en VBA (faire l'équivalent de ctrl+maj+entrer) ? Est-ce bien en utilisant formulaarray ?

VBA : Saisir formules renvoyant vers d'autres feuilles

14 réponses

Discussions similaires

Newsletters