Macro - Création de mois selon formule INDEX

[Antoine_F]

Bonjour à tous,

Je travaille actuellement sur l'automatisation d'un fichier assez lourd au sein de l'entreprise m'accueillant en stage.
J'ai pu trouver pas mal de réponses à mes questions sur le forum, mais je bloque sur un tout petit élément...

Voilà ce que je voudrais que ma macro exécute :
- Création d'une colonne "01/01/2011" au format date
- Création de nouvelles colonnes jusqu'au mois M+1 de l'année en cours, selon l'index mis à jour dans l'onglet "INDEX"

Je pense que la réponse doit être toute bête, mais je bute là-dessus depuis le début de l'après-midi et je n'ai trouvé aucune discussion répondant à ma question.

Peut-être pourriez-vous m'aider ?

Merci d'avance,
Antoine


EDIT : Avec en pièce jointe mon fichier "déconfidentialisé" -> Il faudrait que la formule index s'applique dans les colonnes tout à droite, jusqu'au mois M+1.

 

[Modeste]

Re : Macro - Création de mois selon formule INDEX

Bonjour Antoine ... et bienvenue (avec un peu de retard),

La discussion que tu as ouverte a déjà été consultée près de 30 fois, sans qu'une réponse ait été donnée. Si tu repasses par ici (voyons déjà ça ) il faudrait que tu complètes tes explications. Pourrais-tu préciser, par exemple:
- pourquoi tu souhaites une macro?
- à quelle occasion se déclencherait cette macro?
- quand tu évoques la création d'une "colonne" 01/01/2011 ... qu'est-ce que tu entends par là (et pourquoi 2011!?)
- ce que tu as en tête quand tu écris: selon l'index mis à jour dans l'onglet "INDEX" Tu parles de la cellule N2? La mise à jour se fait de quelle manière ... tu changes la valeur "manuellement"?
- les "colonnes" (plutôt des cellules??) à créer correspondraient aux données que tu as encodées en feuille "INDEX" de A3 à L3?
- Le mois M+1 de l'année en cours devrait correspondre à quoi (M serait le mois en N2 et tu voudrais donc générer les dates du premier jour de chaque mois entre janvier et septembre, dans le cas présent?)

Bref, je ne sais pas si la réponse est toute bête ... et visiblement, je ne suis pas le seul

 

[Antoine_F]

Re : Macro - Création de mois selon formule INDEX

Bonjour Modeste (& merci pour ta réponse !)

Pour répondre à tes questions :
- pourquoi tu souhaites une macro?
Je travaille actuellement en contrôle de gestion et j'essaye d'automatiser intégralement un fichier issu d'une extraction SAP, qui prend actuellement plusieurs jours à compléter manuellement.
J'ai déjà créé plusieurs macro effectuant le travail demandé et je souhaiterais en avoir une afin de pouvoir automatiser l'intégralité du fichier.

- à quelle occasion se déclencherait cette macro?
Après avoir retraité l'extraction (ajout/suppression de colonnes, réorganisation du tableau, etc.), cette macro se déclencherait à la suite des précédentes.
Pour faire bref :
• L'extraction comprend des données clients, avec notamment les dates de début/fin de période de leur plan de paiement
• Mon fichier comporte donc des centaines de milliers de lignes, ce qui explique qu'il est long à traiter manuellement
• Néanmoins, certains clients peuvent être regroupés selon des caractéristiques
• Le but de ce tableau est de calculer les "portions" mensuelles des plans de paiement (en gros, est-ce que le client sera là un mois complet (1 portion), partiel (0,5 portion) ou pas du tout (0 portion)), pour chaque grand type de clients

- quand tu évoques la création d'une "colonne" 01/01/2011 ... qu'est-ce que tu entends par là (et pourquoi 2011!?)
J'entends par là un tableau, dont les intitulés de colonne débutent 13 mois avant (ou au 01/01/2011 si la macro est difficile à coder).
• Pourquoi 12 mois avant ? Il faut que je puisse suivre le plan de paiement annuel de chacun des clients
• Pourquoi 13 mois avant ? Simplement pour avoir un mois de contrôle (puisque, a priori, il ne doit y avoir aucun plan de paiement ouvert en M-13).

- ce que tu as en tête quand tu écris: selon l'index mis à jour dans l'onglet "INDEX" Tu parles de la cellule N2? La mise à jour se fait de quelle manière ... tu changes la valeur "manuellement"?
Mise à jour manuelle de l'onglet index (j'ai pensé à une automatisation, mais si l'on travaille en septembre sur le mois d'août par exemple, ce qui est généralement le cas, l'index mettra septembre et non août, d'où la MAJ manuelle)

- les "colonnes" (plutôt des cellules??) à créer correspondraient aux données que tu as encodées en feuille "INDEX" de A3 à L3?
En gros, ma formule index servirait simplement à "limiter" ma macro de création de colonnes (avec les mois en en-têtes).
• Pour un index "8" (pour août), je voudrais :
- Première date "01/07/2011" (soit M-13), ou "01/01/2011" si ce n'est pas possible
- Dernière date "01/09/2012" (soit M+1), car j'ai besoin d'un mois supplémentaire pour que mes formules fonctionnent

- Le mois M+1 de l'année en cours devrait correspondre à quoi (M serait le mois en N2 et tu voudrais donc générer les dates du premier jour de chaque mois entre janvier et septembre, dans le cas présent?)
Oui, exactement. Ou plutôt entre juillet 2011 et septembre 2012, sur une base d'index "8" août 2012.


J'espère avoir été clair et vous remercie grandement par avance de m'aider là-dessus !
Je pense que la solution ne doit pas être sorcière à trouver, mais je bute vraiment sur le sujet donc vous pourriez m'être d'une très grande aide.

Merci, et bonne journée !

 

[Modeste]

Re : Macro - Création de mois selon formule INDEX

Re-bonjour,

Tu as répondu plus vite que je ne l'aurais imaginé et de manière fort complète (je n'aurai donc plus d'excuse, si je me "plante", mince!!)

En pièce jointe, donc, avec ce que j'ai compris, une proposition qui s'exécutera à chaque modification de la valeur en N2 de la feuille INDEX ... à tester donc (et ne pas frapper, si je me suis viandé )

 

[Antoine_F]

Re : Macro - Création de mois selon formule INDEX

Génial, ce n'était pas exactement ce que je cherchais mais avec une macro de renvoi des cellules ligne 1 de l'onglet "Test" vers les cellules ligne 1 de l'index, ça devrait fonctionner parfaitement !

Je te dérange pour encore deux petites choses !
- Pourrais-tu me donner le code de la macro que tu as mise dans l'onglet index, afin que je l'incorpore dans mon fichier de base ?
- Pourrais-je modifier l'année de référence depuis la macro (lorsque nous serons en 2013) ?

Encore merci, je vais pouvoir de nouveau avancer !!

A très vite,
Antoine

 

[Modeste]

Re : Macro - Création de mois selon formule INDEX

Re²,

A très vite, à très vite, comme tu y vas (je suis au boulot, tout de même )

Pour ta première remarque, j'avoue ne pas avoir lu toutes tes explications ... cette partie-là y figurait??

Le code est dans le module de la feuille "INDEX", puisqu'il s'agit d'une macro événementielle (Sub Worksheet_Change)

Pour la dernière question, la macro considère l'année en cours pour créer la série de dates ... donc tu ne devrais rien avoir à modifier l'an prochain (si j'ai bien compris)

Je n'aurai plus l'occasion de passer par ici avant ce soir, mais d'autres prendront le relais si besoin (ou amélioreront la proposition, le cas échéant)

Bon travail,

 

[Antoine_F]

Re : Macro - Création de mois selon formule INDEX

Pas de soucis, je peux patienter !

J'ai bien repéré la macro évènementielle mais j'ai un dernier souci : mon onglet "INDEX" est créé via une macro. Or, j'aimerais que ta formule "PrivateSub Worksheet_Change" soit opérationnelle à la création de la feuille portant le nom "INDEX".

J'ai essayé de trouvé par moi-même et cherché un peu partout, mais impossible de trouver (je débute tout juste sur VBA).
-> Comment pourrais-je appeler la Private_Sub depuis ma macro de création de l'onglet "INDEX" (pour qu'elle ne s'exécute que sur cet onglet précisément) ?

Sub °2_INDEX()
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "INDEX"
    
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> "$A$2" Then Exit Sub
    For D = 0 To 14
    Cells(1, c + 1) = DateSerial(Year(Date) - 1, [A2] - 1 + D, 1)
    c = c + 1
    Next D

End Sub

Merci d'avance pour vos réponses !

 

[Modeste]

Re : Macro - Création de mois selon formule INDEX

Bonsoir,

Désolé, mais ta question dépasse (de loin) mon niveau de compétences ... je ne peux donc absolument pas t'aider sur ce coup-là (mais la feuille ne peut-elle être présente, dès le départ, dans ton fichier ?)

 

[Antoine_F]

Re : Macro - Création de mois selon formule INDEX

En fait, je pourrai insérer ladite macro dès le premier onglet (sans en créer de nouveau) mais c'était pour plus de clarté.

En essayant d'être clair, votre formule me convient parfaitement mais il faudrait qu'elle ne soit active que sur une seule feuille, de telle façon à ce 100% des cellules des autres feuilles soient libres en écriture (sans déclencher ladite macro évènementielle).

Pensez-vous que cela soit possible ?

Excusez-moi avec toutes mes questions, mais je débute tout juste sur VBA !
Merci d'avance !

 

[Modeste]

Re : Macro - Création de mois selon formule INDEX

Re-bonsoir Antoine,

Quoi, on se vouvoie le soir et on se tutoie la journée!?
En fait, le code que je t'ai proposé, s'il est inséré uniquement dans le module d'une feuille particulière, ne s'exécute que pour la feuille en question (sauf dans le cas où tu ferais une copie de cette feuille dans le même classeur: le code associé est copié automatiquement dans cette nouvelle copie de la feuille)

Pour le reste, comme nous n'avons aucune idée de ce que tu fais comme manipulations (via macro ou "manuellement"), il sera difficile de dire si ce que tu as en tête sera possible ... ou non (et je t'assure que ce n'est en aucun cas de la mauvaise volonté)

Si quelqu'un d'autre a une idée, je présume qu'il est inutile de préciser qu'il(elle) est le(la) bienvenu(e)

Edit: Salut David ... merci pour la proposition!

 

[david84]

Re : Macro - Création de mois selon formule INDEX

Bonsoir à tous, salut Modeste,

J'ai bien repéré la macro évènementielle mais j'ai un dernier souci : mon onglet "INDEX" est créé via une macro. Or, j'aimerais que ta formule "PrivateSub Worksheet_Change" soit opérationnelle à la création de la feuille portant le nom "INDEX".

pas tout suivi mais place ce code de Modeste légèrement modifié dans ThisWorkbook et efface-le de la feuille INDEX :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = "INDEX" And Target.Address = "$N$2" Then
    For d = 0 To 14
        Cells(1, c + 1) = DateSerial(Year(Date) - 1, [N2] - 1 + d, 1)
        c = c + 1
    Next d
End If
End Sub

A+

 

[Antoine_F]

Re : Macro - Création de mois selon formule INDEX

Bonjour David, salut Modeste,

Merci à tous les deux pour votre aide : la version de ton code, améliorée par David, marche à la perfection Modeste !
Merci mille fois, vous m'avez vraiment bien aidé sur le sujet !

Ayant beaucoup aimé passer ces deux jours à travailler sur des macros, je vais essayer de m'y mettre donc à bientôt sûrement, & merci encore !


PS : Comment fait-on pour statuer le fil comme "Résolu" ?

PS Bis : Grâce à tout le forum ainsi qu'à vous deux, j'ai beaucoup avancé et du coup, je cherche maintenant à optimiser ma macro.
Aussi, une nouvelle problématique ici