Concaténation de formule en VBA

ticlaou

XLDnaute Nouveau
Bonjour,
j'ai la formule suivante (sur Excel) :
='03-10'!J7+'04-10'!J7+'05-10'!J7+.........+'28-10'!J7+'31-10'!J7
que je veux reproduire en VBA tout en sachant que les Feuilles du classeur représentent les jours ouvrables du mois qui changent d'un mois a l'autre.
je veux concaténer cette formule pas à pas dans une boucle (i allant du début du mois jusqu'à la fin)
j'ai essayé avec deux variables, une pour recevoir le résultat de la concaténation et une pour recevoir le bout de formule suivant. je sais que c'est un peu tiré par les cheveux car je suis totalement novice en VBA mais je voudrais comme même faire quelque chose pour alléger mes tâches mensuelles au travail.
(je n'arrive même pas a charger la variable sans lui montrer tout le chemin)
voici la boucle :

If Weekday(Premier, vbMonday) = 6 Then
i = Premier + 2
ElseIf Weekday(Premier, vbMonday) = 7 Then
i = Premier + 1
Else
i = Premier
End If
For i = i To m
If Weekday(i, vbMonday) = 6 Then
i = i + 2
ElseIf Weekday(i, vbMonday) = 7 Then
i = i + 1
End If
var1 = "='Z:\STATISTIQUES MANUELLES\" & CetteAnnee & "\[HDT " & MAJ & ".xlsx]" & Format(i, "dd-mm") & "'!C" & k
i = i + 1
If Weekday(i, vbMonday) = 6 Then
i = i + 2
ElseIf Weekday(i, vbMonday) = 7 Then
i = i + 1
End If
var2 = "=CONCATENATE(var2, var1)"
i = i + 1
Next

Merci
 

ticlaou

XLDnaute Nouveau
Bonjour,
plutôt qu'un long discours et un code (auquel il faudrait reproduire déjà les valeurs sur des feuilles, mais on ne sait où et à quelle cellule commencer),
un classeur modèle serait plus conseillé
Boujour

D'autant qu'une simple formule 3D ferait surement l'affaire :
Code:
 =SOMME('03-10:31-10'!J7)
Bonjour Chris, oui mais je voudrais que ce soit variable selon le jour et par conséquent selon le nom de la feuille que je paramétrerais par i et puis il faut que ce soit dynamique car le classeur se remplira au fur et à mesure des jours.
 

Pièces jointes

  • HDT 1 OCTOBRE.xlsx
    246.1 KB · Affichages: 38

ticlaou

XLDnaute Nouveau
Je m'excuse, je crois que je n'ai pas mentionné la dernière feuille qui s'appelle Mois de Novembre, c'est dans laquelle le calcul de la somme s'effectue
donc la cellule J7 de cette feuille doit recevoir les valeurs des cellules J7 de toutes les autres feuilles.
 

ticlaou

XLDnaute Nouveau
Re

La dernière feuille de ton exemple s'appelle mois d'octobre et en J7 de cette feuille il n'y a rien...
Tu n'as pas répondu à mes questions...
C'est parce que j'arrive pas à mettre la formule indiquée au début, je veux mettre la formule non pas manuellement mais à l'aide de VBA, puisque tous le classeur sera créer en VBA au début de chaque mois. par exemple aujourd'hui j'aurais voulu que mon programme crée le classeur nommé mois de Novembre, il crée toutes les feuilles avec leur cellules prêtes à recevoir les données chaque jours, puis il crée la dernière feuille qui elle recevra au fur et à mesure les données copiées chaque jours. Je ne sais pas si j'ai répondu ou non à vos questions.
 

chris

XLDnaute Barbatruc
Re

Si tu compte mettre toutes les formules des +- 20 onglets, cela va un peu être une usine à gaz.
Je ferais plutôt un modèle de feuille à dupliquer avec des formules qui se réfèrent quand nécessaire à l'onglet précédent.

Il faut commencer part créer une liste des jours fériés.

Pour la recap mois, puisque tu crées tous les onglets, utilise une formule 3D au lieu des additions.
Il y a sans doute des cas également dans les feuilles journalières (je n'ai pas détaillé)

Pour se référer à un onglet précédent on peut utiliser du XL4 dans des noms :
ListeOnglets
Code:
=DROITE(LIRE.CLASSEUR(1+0*MAINTENANT());NBCAR(LIRE.CLASSEUR(1))-TROUVE("]"; LIRE.CLASSEUR(1)))
CetOnglet
Code:
=DROITE(LIRE.CELLULE(32+0*MAINTENANT();INDIRECT("LC";FAUX));NBCAR(LIRE.CELLULE(32;INDIRECT("LC";FAUX)))-TROUVE("]"; LIRE.CELLULE(32;INDIRECT("LC";FAUX))))
OngletP (pour précédent)
Code:
=INDEX(ListeOnglets;EQUIV(CetOnglet;ListeOnglets;0)-1+0*MAINTENANT())

Et des formules à base d'INDIRECT, par exemple en B7 des feuilles
Code:
=INDIRECT("'"&OngletP&"'!E"&LIGNE())+INDIRECT("'"&OngletP&"'!T"&LIGNE())

Pour le VBA
Code:
Mois = CDate("2016/09") '09 a adapter en fonction du mois permet ici de calculer octobre
debut = Application.WorksheetFunction.EoMonth(Mois, 0)
debut = Application.WorksheetFunction.WorkDay(debut, 1, Range("JF")) 'la liste des jours fériés est nommée JF dans le classeur
fin = Application.WorksheetFunction.EoMonth(debut, 0)
'Boucle sur chaque feuille pour dupliquer, nommer ou formuler
For i = Day(debut) To Day(fin)
    Jour = debut + i - Day(debut)
    If Application.WorksheetFunction.CountIf(Range("JF"), Jour) = 0 And _
    Application.WorksheetFunction.Weekday(Jour, 2) < 6 Then
        code....
        'Exemple pour une formule 3D du début à ce jour
        ActiveSheet.Cells(1, 1).FormulaLocal = "=somme('" & Format(debut, "dd-mm") & ":" & Format(Jour, "dd-mm") & "'!j7)"
  Else
        Fin2 = Jour - 1
  End If
Next i
If Fin2 < Fin Then Fin = Fin2

'Exemple pour une formule 3D du début à la fin
ActiveSheet.Cells(1, 1).FormulaLocal = "=somme('" & Format(debut, "dd-mm") & ":" & Format(Fin, "dd-mm") & "'!j7)"

Edit : correction du calcul de la fin de mois
 
Dernière édition:

ticlaou

XLDnaute Nouveau
Re

Si tu compte mettre toutes les formules des +- 20 onglets, cela va un peu être une usine à gaz.
Je ferais plutôt un modèle de feuille à dupliquer avec des formules qui se réfèrent quand nécessaire à l'onglet précédent.

Il faut commencer part créer une liste des jours fériés.

Pour la recap mois, puisque tu crées tous les onglets, utilise une formule 3D au lieu des additions.
Il y a sans doute des cas également dans les feuilles journalières (je n'ai pas détaillé)

Pour se référer à un onglet précédent on peut utiliser du XL4 dans des noms :
ListeOnglets
Code:
=DROITE(LIRE.CLASSEUR(1+0*MAINTENANT());NBCAR(LIRE.CLASSEUR(1))-TROUVE("]"; LIRE.CLASSEUR(1)))
CetOnglet
Code:
=DROITE(LIRE.CELLULE(32+0*MAINTENANT();INDIRECT("LC";FAUX));NBCAR(LIRE.CELLULE(32;INDIRECT("LC";FAUX)))-TROUVE("]"; LIRE.CELLULE(32;INDIRECT("LC";FAUX))))
OngletP (pour précédent)
Code:
=INDEX(ListeOnglets;EQUIV(CetOnglet;ListeOnglets;0)-1+0*MAINTENANT())

Et des formules à base d'INDIRECT, par exemple en B7 des feuilles
Code:
=INDIRECT("'"&OngletP&"'!E"&LIGNE())+INDIRECT("'"&OngletP&"'!T"&LIGNE())

Pour le VBA
Code:
Mois = CDate("2016/09") '09 a adapter en fonction du mois permet ici de calculer octobre
debut = Application.WorksheetFunction.EoMonth(Mois, 0)
debut = Application.WorksheetFunction.WorkDay(debut, 1, Range("JF")) 'la liste des jours fériés est nommée JF dans le classeur
fin = Application.WorksheetFunction.EoMonth(debut, 0)
'Boucle sur chaque feuille pour dupliquer, nommer ou formuler
For i = Day(debut) To Day(fin)
    Jour = debut + i - Day(debut)
    If Application.WorksheetFunction.CountIf(Range("JF"), Jour) = 0 And _
    Application.WorksheetFunction.Weekday(Jour, 2) < 6 Then
        code....
        'Exemple pour une formule 3D du début à ce jour
        ActiveSheet.Cells(1, 1).FormulaLocal = "=somme('" & Format(debut, "dd-mm") & ":" & Format(Jour, "dd-mm") & "'!j7)"
  Else
        Fin2 = Jour - 1
  End If
Next i
If Fin2 < Fin Then Fin = Fin2

'Exemple pour une formule 3D du début à la fin
ActiveSheet.Cells(1, 1).FormulaLocal = "=somme('" & Format(debut, "dd-mm") & ":" & Format(Fin, "dd-mm") & "'!j7)"

Edit : correction du calcul de la fin de mois
Merci pour votre patience et votre réponse, et désolé que je n'ai pas répondu rapidement, aujourd'hui presque journée entière entre médecin et radiologie, je vais essayer votre solution et je vous tiendrais au courant, merci encore.
 

Discussions similaires

Réponses
7
Affichages
462

Statistiques des forums

Discussions
312 895
Messages
2 093 382
Membres
105 713
dernier inscrit
Karimov