Automatisation calcul date Marco VBA

[babybyme]

Bonjour,

Je me dirige vers vous pour m'aider à résoudre un problème.

Je doit Mettre en place une macro qui calcul des dates à partir du dernier jour du mois affiché sur le nom du fichier: Par exemple si le nom du ficher est le 022013, prendre 28/02/2013.

J'ai mis plus d'explications dans le fichier joint avec des exemples ainsi que le résultat souhaité.

Par avance merci pour votre aide.

Cordialement

 

[kjin]

Re : Automatisation calcul date Marco VBA

Bonjour,
Pour trouver le dernier jour du mois en fonction du fichier (brut de décoffrage !)...

t = Split(ActiveWorkbook.Name, " ")
mois = CInt(Left(t(2), 2))
annee = CInt(Mid(t(2), 3, 4))
datefinmois = DateSerial(annee, mois + 1, 1) - 1

....mais pour le reste, je n'ai rien compris
A+
kjin

 

[JHA]

Re : Automatisation calcul date Marco VBA

Bonjour à tous,

Un essai par formule

JHA

 

[Dranreb]

Re : Automatisation calcul date Marco VBA

Bonjour
Comme ça peut-être:

Sub Macro1()
Dim NomFic As String
NomFic = ThisWorkbook.Name
ThisWorkbook.Names.Add "DateRef", "=DATE(" & Mid$(NomFic, Len(NomFic) - 7, 4) & "," & Mid$(NomFic, Len(NomFic) - 9, 2) + 1 & ",0)" ' …022013.xls
Feuil1.[B6:B76].FormulaR1C1 = "=IF(RC1<>"""",SERIE.JOUR.OUVRE(DateRef,SUBSTITUTE(RC1,""J"",""""),),"""")"
End Sub

À +

 

[babybyme]

Re : Automatisation calcul date Marco VBA

Bonjour,

Je vous remercie infiniment.

Dranreb, ton travail est génial, par contre je ne comprends pas pourquoi il y a un petit souci avec le J-10 qui n'affiche pas la bonne date (JEUDI 14 février 2013 au lieu de vendredi 15 février 2013) sachant que toutes les autres dates sont bonnes.

Merci encore une fois pour votre aide.

Cordialement

 

[Dranreb]

Re : Automatisation calcul date Marco VBA

Je ne vois pas d'anomalie, pas de trou ou autre anomalie entre 0 et -10, vendredi 15 février 2013 correspond à -9, pas à -10:
[TABLE="width: 345"]
[TR]
[TD]0[/TD]
[TD]jeudi 28 février 2013[/TD]
[/TR]
[TR]
[TD]-1[/TD]
[TD]mercredi 27 février 2013[/TD]
[/TR]
[TR]
[TD]-2[/TD]
[TD]mardi 26 février 2013[/TD]
[/TR]
[TR]
[TD]-3[/TD]
[TD]lundi 25 février 2013[/TD]
[/TR]
[TR]
[TD]-4[/TD]
[TD]vendredi 22 février 2013[/TD]
[/TR]
[TR]
[TD]-5[/TD]
[TD]jeudi 21 février 2013[/TD]
[/TR]
[TR]
[TD]-6[/TD]
[TD]mercredi 20 février 2013[/TD]
[/TR]
[TR]
[TD]-7[/TD]
[TD]mardi 19 février 2013[/TD]
[/TR]
[TR]
[TD]-8[/TD]
[TD]lundi 18 février 2013[/TD]
[/TR]
[TR]
[TD]-9[/TD]
[TD]vendredi 15 février 2013[/TD]
[/TR]
[TR]
[TD]-10[/TD]
[TD]jeudi 14 février 2013[/TD]
[/TR]
[/TABLE]
Mais s'il y a une histoire de signification conventionnelle des nombres négatif considérant que le 0 n'existe pas, on devrait pouvoir modifier la formule pour qu'elle en tienne compte.
À +

 

[babybyme]

Re : Automatisation calcul date Marco VBA

Re,

Effectivement, c'est moi qui s'est trompé.

Mais j'ai une dernière question SVP, par rapport à J (ou J+0 si J rend les choses plus compliqués) ne fonctionne pas.

Je préçise que quand je met J+0, le résultat est : Dimanche 31 mars 2013 (Il prend en compte même les jours non ouvrables!!!)

Est ce normal ? ou il y'a une condition à rajouter ?

Merci pour votre aide préçieuse.

Cordialement

 

[Dranreb]

Re : Automatisation calcul date Marco VBA

Exact, je n'avais pas vu parce que ça le fait sur 032013.xls et non sur 022013.xls
Conclusion: la date de référence ne doit pas être le dernier jour du mois, mais le dernier jour ouvré du mois.
On peut y arriver comme ça:

Sub Macro1()
Dim NomFic As String
NomFic = ThisWorkbook.Name
ThisWorkbook.Names.Add "DateRef", "=SERIE.JOUR.OUVRE(SERIE.JOUR.OUVRE(DATE(" _
   & Mid$(NomFic, Len(NomFic) - 7, 4) & "," & Mid$(NomFic, Len(NomFic) - 9, 2) + 1 & ",0),1),-1)"
Feuil1.[B6:B76].FormulaR1C1 = "=IF(RC1<>"""",SERIE.JOUR.OUVRE(DateRef,SUBSTITUTE(RC1,""J"",""""),),"""")"
End Sub

Oui, le "J" ne sert à rien: pourrait on éviter d'avoir à le supprimer dans la formule ?…
En parlant de chose à ajouter possiblement: la fonction SERIE.JOUR.OUVRE supporte un 3ième paramètre qui serait une liste de jours fériés en plus des samedi dimanche. (Nom "Fériés" dans le classeur référençant la liste fortement recommandé)
À +

P.S. Suis-je bête… c'est plus simple que ça !

ThisWorkbook.Names.Add "DateRef", "=SERIE.JOUR.OUVRE(DATE(" _
   & Mid$(NomFic, Len(NomFic) - 7, 4) & "," & Mid$(NomFic, Len(NomFic) - 9, 2) + 1 & ",1),-1)"

À +

 

[babybyme]

Re : Automatisation calcul date Marco VBA

C'est juste GENIAL.

MERCI BCP BCP BCP pour votre aide préçieuse.

Une toute dernière question SVP, vu que je suis "nul" en language VBA, j'aimerai juste avoir une petite indication SVP par rapport au changement de colonne, c'est à dire dans votre code vous voulez prendre le J+1 dans la colonne A mais moi sur mon fichier c'est la colonne J par exemple, comment je peux faire pour le changer SVP ? J'ai essayé de comprendre votre code mais en vain.

Bien à vous

 

[Dranreb]

Re : Automatisation calcul date Marco VBA

Remplacez le "1" de "RC1" par le numéro d'ordre de la colonne. C'est de la notation R1C1, c'est normal que vous n'y retrouviez pas les lettres d'entêtes de colonnes de la notation A1.

 

[babybyme]

Re : Automatisation calcul date Marco VBA

Je vous remercie beaucoup pour votre gentillesse, votre patience, et votre temps.

Je vous sohaite un bon week end. 🙂

Cdlt

 

[babybyme]

Re : Automatisation calcul date Marco VBA

Dranreb, bonjour, 🙂

Je reviens encore vers vous pour une particularité que vous m'aviez d'ailleurs déja proposé.

Pouvez vous SVP, sur la base du code que vous m'aviez fourni, prendre en compte même les jours fériés ?

Je vous remercie d'avance.

Bien cordialement

 

[Dranreb]

Re : Automatisation calcul date Marco VBA

Bonjour.
Il suffit de réserver quelque part une plage contenant les dates de jours fériés, la nommer "Fériés" dans le classeur, et préciser ce nom comme 3ième paramètre à la fonction SERIE.JOUR.OUVRE
À +

 

[babybyme]

Re : Automatisation calcul date Marco VBA

Bonjour,

J'ai réservé dans le classeur 2 une plage contenant les dates de jours fériés que j'ai nommé "FERIES", mais j'ai pas su intégrer ce paramètre à la MACRO !!!!! 😕

Pouvez-vous m'aider SVP ?

Sub Macro1()
Dim NomFic As String
NomFic = ThisWorkbook.Name
ThisWorkbook.Names.Add "DateRef", "=SERIE.JOUR.OUVRE(SERIE.JOUR.OUVRE(DATE(" _
   & Mid$(NomFic, Len(NomFic) - 7, 4) & "," & Mid$(NomFic, Len(NomFic) - 9, 2) + 1 & ",0),1),-1)"
Feuil1.[K6:K83].FormulaR1C1 = "=IF(RC10<>"""",SERIE.JOUR.OUVRE(DateRef,SUBSTITUTE(RC10,""J"",""""),),"""")"
End Sub

Cordialement

 

[Dranreb]

Re : Automatisation calcul date Marco VBA

Bonjour.

Sub Macro1()
Dim NomFic As String
NomFic = ThisWorkbook.Name
ThisWorkbook.Names.Add "DateRef", "=SERIE.JOUR.OUVRE(DATE(" _
   & Mid$(NomFic, Len(NomFic) - 7, 4) & "," & Mid$(NomFic, Len(NomFic) - 9, 2) + 1 & ",1),-1,FERIES)"
Feuil1.[B6:B76].FormulaR1C1 = "=IF(RC1<>"""",SERIE.JOUR.OUVRE(DateRef,SUBSTITUTE(RC1,""J"",""""),FERIES),"""")"
End Sub

Après rectification de la référence à "FERIES" pour quelle ne contienne que des dates, donc =Fériés!$C$2:$C$14, à tester (votre projet VBA est protégé par un mot de passe).
À +