calendrier excel

[Laetitia L]

Bonjour à tous,
Je travaille toujours sur un calendrier automatisé sur Excel.
Ma question est assez complexe, j'espère que quelqu'un pourra m'aider
Sur l'onglet calendrier, un calendrier automatisé renvoi aux dates de l'onglet "instructions" avec une liste de lieux sur chaque dates.
Je souhaiterai que si on renseigne un nombre de jours dans les colonnes D du calendrier, ce nombre apparaisse sur la bonne cellule de l'onglet instruction en fonction du lieux et de la date. Ce qui permettrait alors de décaler automatiquement le lieux dans le calendrier.
D'autre part, certains lieux ne peuvent être fixés que sur certains jours de la semaine. Il faudrait alors que ce décalage ne puisse se faire que sur les jours indiqués. Et certains lieux n'ont pas de particularité, je peux les décaler du nombre de jours que je souhaite.

J'espère que je ne vous ai pas perdu avec mes explications
Le fichier est en PJ

 

[Laetitia L]

Je comprends votre réaction mais je n'arrive pas à comprendre la macro, je ne pensais pas que les modifications que j'ai apporté à l'outil avaient des conséquences sur la macro. J'ai testé différentes modifications, sans succès car je ne maitrise pas le codage.

 

[Laetitia L]

For i = 1 To nbcol Step 4 'on parcourt les dates de la zone "TabCalend" qui sont une colonne sur 4 --> en mettant un 6 puisque j'ai rajouté 2 colonnes
IndJour = (Int(j / 9) Mod 9) * 9 --> à quoi correspond la ligne 9 ?
.Range("P" & ici.Row) = decalage : Doit-on mettre le décalage dans la colonne O

 

[vgendron]

For i = 1 To nbcol Step 4 'on parcourt les dates de la zone "TabCalend" qui sont une colonne sur 4 --> en mettant un 6 puisque j'ai rajouté 2 colonnes
==> c'est exact

IndJour = (Int(j / 9) Mod 9) * 9 --> à quoi correspond la ligne 9 ?
==> comme on parcourt le calendrier ligne par ligne, et qu'il y a plusieurs dates sur une colonne (et 7 villes par date), il faut lui dire à quelle ligne récupérer la date
Cette ligne change toutes les 9 lignes....

.Range("P" & ici.Row) = decalage : Doit-on mettre le décalage dans la colonne O
==> effectivement. si le décalage est en colonne O au lieu de P.. il faut lui dire: La macro n'est pas capable de détecter toute seule le changement de place d'une colonne (sauf dans le cas de table Excel et de zones nommées)

Pour t'aider à comprendre la macro (je ne vois pas quel commentaire je peux ajouter vu que toutes les lignes sont commentées), utilise le mode pas à pas pour executer la macro:
les lignes de code seront executées une par une (à chaque touche F8) tu pourra ainsi suivre ce qu'elle fait..

 

[Laetitia L]

Bonjour,
J'ai compris la macro, je n'aurai plus besoin de vous embêter avec ça.
Je tiens vraiment à vous remercier pour votre aide, je n'y serais pas arrivé autrement.

Si ça ne vous ennuie pas j'aurai une autre petite problématique :
Dans les formules des dates, je prends en considération un seul jour

pour la première collecte :
=SIERREUR(M2+EQUIV(F2;TEXTE(M2+{1;2;3;4;5;6;7};"jjjj");0);M2)
pour les collectes suivantes :
=SIERREUR(N2+K2+O2+EQUIV(F2;TEXTE(N2+K2+O2+{1;2;3;4;5;6;7};"jjjj");0);N2+K2+O2)

Dans ces 2 formules, la cellule F2 correspond à un mardi. Dans les colonnes suivant F, il peut y avoir d'autres colonnes avec d'autres jours.
J'aimerai savoir comment il est possible de les intégrer dans la formule, afin que la date ne soit pas bloqué à un seul jour de la semaine dans le calendrier.
J'ai essayé avec un OU et un ET sans succès.

 

[vgendron]

Hello
pas sur de comprendre ce que tu souhaites afficher..
un essai pour la fomrule en P6

=TEXTE(SIERREUR(N6+K6+O6+EQUIV($F$6;TEXTE(N6+K6+O6+{1;2;3;4;5;6;7};"jjjj");0);N6+K6+O6);"[$-F800]jjjj, mmmm jj, aaaa")&CAR(10) &TEXTE(SIERREUR(N6+K6+O6+EQUIV($G$6;TEXTE(N6+K6+O6+{1;2;3;4;5;6;7};"jjjj");0);N6+K6+O6);"[$-F800]jjjj, mmmm jj, aaaa")

à noter que ca va te faire des formules à rallonge avec gros risque d'erreur...
peut etre une solution par fonction personalisée serait plus adaptée.?

 

[Laetitia L]

Ce n'est pas exactement le résultat que je cherche
La les 2 résultats apparaissent dans la cellule
Ce que je souhaiterai, c'est que dans le calendrier, si un lieux peut être pris un mardi, un mercredi et un jeudi (comme indiqué en colonne F G et H). Il faudrait que la cellule qui calcule les dates des collectes prennent en compte ces 3 possibilités.
De sorte à ce que si on veut décaler un lieux du mardi au mercredi on puisse le faire. Et qu'on ne soit pas obligé de décaler le lieux une semaine après car on a pris en compte qu'une seule possibilité de jour.
J'espère que j'ai été assez claire ...

 

[vgendron]

bon. je viens de jeter un oeil aux formules..
déjà. un souci à régler avant..
expliquer le fonctionnement
1) Colonnes de E à I
*Colonne E : numéro du jour de préférence
* Colonnes FGH = les jours séparés identifiés en colonne E
*Colonne I = particularités pour certaines journées: soir matin, ou autre...
Correct jusque la?

2) Colonne J
Toutes les lignes sont rentrées manuellement

3) Colonne K: Ccalcul pour le nombre de jours entre 2 collectes
4) Colonne L: Décalage rempli par la macro
5) Colonne M: pourquoi le Nbr de jour entre 2 collectes est parfois rempli à la main, plutot qu'une référence à la colonne K?
==> en gros. toutes les lignes n'ont pas la meme formule..
Exemple De M1 à M5: le nombre 56 est rentré à la main dans la formule alors qu'il est dispo en colonne K
en M6: tu fais encore: J6+56+L6 alors que en K6, tu as calculé 82.5..?
et en M9: tu mets la formule qui (à mon avis) devrait apparaitre sur TOUTES les lignes: =J9+K9+L9
en M228: tu ne mets carrement plus le nbre de jours entre 2 collectes: =J228+L228

6) Colonne N
On cherche le jour de la colonne F situé juste après la date de première collecte (colonne M)
ex: Colonne M = mardi 15 sept 2018
Colonne F= jeudi
==> Colonne N = Jeudi 17 sept 2018
au pire. on se retrouve avec une date à +7 jours

7) Colonne O: Décalage
8) Colonne P: c'est comme la colonne N, SAUF que le calcul part de la date col N + le nbr de jour entre 2 collectes + le décalage..

etc etc..
ca, c'est dans le cas ou il n'y a QU'UN seul jour "préférence"

maintenant. je ne comprend pas le besoin du cas ou il y en a 2.. ou 3: Exemple Ligne 16

Sont préférés Vendredi OU samedi
on est au samedi 20 Oct en col J
==> on arrive à une 1ere collecte le sam 15 dec (colonne M)
==> en N: on arrive au vendredi 21 dec 2018 (on a tenu compte QUE du vendredi autorisé)
MAIS si on avait tenu aussi compte du Samedi... tu aurais voulu avoir quoi en colonne N??

à priori. quand tu spécifies plusieurs jours autorisés.. ils sont classés dans l'ordre..
et donc.. tes formules te decalent au jour le plus proche..

si tu autorises jeudi ou vendredi.. forcément le premier sera trouvé..(sauf si'l y a une autre condition que je ne vois pas)
et du coup. ca ne sert à rien de préciser aussi le vendredi...

Bref. dans quel cas (quelle ligne) as tu un souci et pour lequel tu veux une modif de formule..?


je viens de relire ton post

De sorte à ce que si on veut décaler un lieux du mardi au mercredi on puisse le faire.

ok, je pense avoir compris..
maintenant.. comment faire.....bonne question....j'essaie d'y refléchir.. ...

 

[Laetitia L]

1) Colonnes de E à I
*Colonne E : numéro du jour de préférence
* Colonnes FGH = les jours séparés identifiés en colonne E
*Colonne I = particularités pour certaines journées: soir matin, ou autre...
Correct jusque la? oui

2) Colonne J
Toutes les lignes sont rentrées manuellement oui

3) Colonne K: Ccalcul pour le nombre de jours entre 2 collectes oui
4) Colonne L: Décalage rempli par la macro oui
5) oui j'ai refais des modifications sur le fichier, j'y suis allée étape par étape donc il y a des coquilles, mais je suis d'accord avec vous j'ai modifié le fichier

6) Colonne N
On cherche le jour de la colonne F situé juste après la date de première collecte (colonne M)
ex: Colonne M = mardi 15 sept 2018
Colonne F= jeudi
==> Colonne N = Jeudi 17 sept 2018
au pire. on se retrouve avec une date à +7 jours
oui, je n'ai pas compris pourquoi il y avait un décalage d'une semaine alors que ça tombe un bon jour, je n'ai pas trouvé de moyens pour éviter ça.

7) Colonne O: Décalage (de la 1ere collecte)
8) Colonne P: c'est comme la colonne N, SAUF que le calcul part de la date col N + le nbr de jour entre 2 collectes + le décalage..

etc etc..
ca, c'est dans le cas ou il n'y a QU'UN seul jour "préférence" Exactement

maintenant. je ne comprend pas le besoin du cas ou il y en a 2.. ou 3: Exemple Ligne 16

Sont préférés Vendredi OU samedi
on est au samedi 20 Oct en col J
==> on arrive à une 1ere collecte le sam 15 dec (colonne M)
==> en N: on arrive au vendredi 21 dec 2018 (on a tenu compte QUE du vendredi autorisé)
MAIS si on avait tenu aussi compte du Samedi... tu aurais voulu avoir quoi en colonne N??

Justement, ce n'est pas un problème que la formule prenne en compte le premier jour spécifié, mais il faudrait qu'il prenne en compte le 2eme jour également, au cas ou on voudrait décaler dans le calendrier une date au 2eme jour spécifié.

 

[vgendron]

ok, j'ai donc à peu près compris..
encore une question.. ou deux...

1) Ligne 6: autorisés Jeudi ou Vendredi
colJ: vendredi 20 juillet
ColM: on ne s'occupe PAS des jours autorisés.. on fait juste +colK + Col L ==> Correct?
==> finalement la colonne L, ne sert pas à grand chose.. d'ailleurs.. elle est vide..
donc ColM=Mercredi 10 oct 2018 (Mercredi n'étant pas dans la liste des jours)

ColN: on S'OCCUPE des jours autorisés: on passe donc directemnent au jour autorisé le plus proche: ici. le Jeudi 11 oct 2018
(dans le cas ou on est déjà sur le jour autorisé.. il ne faut pas faire +1 semaine.. mais rester sur le jour: correct?)

Col P: On tient compte de la colK (NbrJourEntre Coll) ET col L (décalage) ET Jours précis..
THE question:
le décalage s'applique à quoi?
Toujours dans la ligne 6: on part du jeudi 11 Oct 2018 avec Décalage =0 ??:
* NbrJourEntre Coll =82.5 ===>on arrive donc au Mardi 1er Janvier 2019
* Decalage=0 ==> 01 Janvier 2019
*Jour Précis (jeudi ou vendredi)===> on passe au plus proche ==> Jeudi 03 Janvier 2019

Toujours dans la ligne 6: on part du jeudi 11 Oct 2018 avec Décalage =1 ??:
* NbrJourEntre Coll =82.5 ===>on arrive donc au Mardi 1er Janvier 2019
* Decalage=1 ==> mercredi 2 Janvier 2019 (ca revient juste à ajouter un jour entre deux collectes.)
*Jour Précis (jeudi ou vendredi)===> on passe au plus proche ==> Jeudi 03 Janvier 2019

OU ALORS.. le décalage s'applique APRES le jour précis
* NbrJourEntre Coll =82.5 ===>on arrive donc au Mardi 1er Janvier 2019
*Jour Précis (jeudi ou vendredi)===> on passe au plus proche ==> Jeudi 03 Janvier 2019
* Decalage=1 ==> Vendredi 4 Janvier 2019 (qui est un jour autorisé)
==> avec un décalage de 2: on serait donc arrivé à Jeudi 10 Janvier 2019...


Dernière question: as tu besoin de voir les formules dans ton tableau ou une macro qui mettrait directement le résultat ferait l'affaire?
j'ai peur qu'avec une fonction personalisée.. ce soit très long d'execution..

 

[vgendron]

autre question
Colonnes E, F G et H
laquelle remplis tu en premier??
==>Si tu remplis uniquement la colonne E (toujours sur le meme schéma : 2 OU 3) les colonnes FGH pourraient etre remplis automatiquement
et plutot que taper 2 OU 3, ce serait plus simple de taper 2;3

 

[Laetitia L]

1) Ligne 6: autorisés Jeudi ou Vendredi
colJ: vendredi 20 juillet
ColM: on ne s'occupe PAS des jours autorisés.. on fait juste +colK + Col L ==> Correct? oui, ol s'agissait juste de faire une étape entre les 2
==> finalement la colonne L, ne sert pas à grand chose.. d'ailleurs.. elle est vide. --> si le décalage en colonne L correspond au décalage pour la première collecte, mais elle n'est pas prise en compte dans la colonne N, ce qui est une erreur de ma part
donc ColM=Mercredi 10 oct 2018 (Mercredi n'étant pas dans la liste des jours)

ColN: on S'OCCUPE des jours autorisés: on passe donc directemnent au jour autorisé le plus proche: ici. le Jeudi 11 oct 2018
(dans le cas ou on est déjà sur le jour autorisé.. il ne faut pas faire +1 semaine.. mais rester sur le jour: correct?) oui c'est ça !

Col P: On tient compte de la colK (NbrJourEntre Coll) ET col L (décalage) ET Jours précis..
THE question:
le décalage s'applique à quoi? le décalage s'applique à la date de la colonne de gauche
Toujours dans la ligne 6: on part du jeudi 11 Oct 2018 avec Décalage =0 ??: le décalage est à 0 car j'ai fait des tests sur la macro, et pour que la collecte que j'ai déplacé dans le calendrier se remette à son emplacement initial, il faut mettre 0 dans la colonne D du calendrier et faire fonctionner la macro. Ducoup il reporte ce numéro dans la colonne O de l'onglet instruction
* NbrJourEntre Coll =82.5 ===>on arrive donc au Mardi 1er Janvier 2019
* Decalage=0 ==> 01 Janvier 2019
*Jour Précis (jeudi ou vendredi)===> on passe au plus proche ==> Jeudi 03 Janvier 2019 OUI

Toujours dans la ligne 6: on part du jeudi 11 Oct 2018 avec Décalage =1 ??:
* NbrJourEntre Coll =82.5 ===>on arrive donc au Mardi 1er Janvier 2019
* Decalage=1 ==> mercredi 2 Janvier 2019 (ca revient juste à ajouter un jour entre deux collectes.) OUI
*Jour Précis (jeudi ou vendredi)===> on passe au plus proche ==> Jeudi 03 Janvier 2019

OU ALORS.. le décalage s'applique APRES le jour précis
* NbrJourEntre Coll =82.5 ===>on arrive donc au Mardi 1er Janvier 2019
*Jour Précis (jeudi ou vendredi)===> on passe au plus proche ==> Jeudi 03 Janvier 2019
* Decalage=1 ==> Vendredi 4 Janvier 2019 (qui est un jour autorisé)
==> avec un décalage de 2: on serait donc arrivé à Jeudi 10 Janvier 2019... C EST CA


Dernière question: as tu besoin de voir les formules dans ton tableau ou une macro qui mettrait directement le résultat ferait l'affaire?
j'ai peur qu'avec une fonction personalisée.. ce soit très long d'execution..
ça serait très bien mais pour le moment je préfère que le tableau soit facilement modifiable, de plus une fois que ce tableau sera fini ce n'est pas moi qui vais l'utiliser, il faudrait donc que ce soit simplifier au maximum et qu'on puisse retrouver les calculs

 

[vgendron]

Bon..
alors.. je vais peut etre te décevoir.. (ou pas). mais avec formule.. j'y arrive pas.. du coup. suis passé à mon idée, de faire tout ca par macro

dans la PJ
la feuille Instruction a été vidée de toutes les infos qui doivent etre calculées.
reste UNIQUEMENT ce que tu saisis à la main
Colonnes B, C, D, E, I, et J
les colonne FGH sont remplies en fonction de la colonne E (qui doit contenir les jours séparés par un ";")
colonne K est calculée par la macro
Les décalages sont alimentées par l'autre macro quand tu modifies le calendrier
et les dates de collecte sont calculées par la macro (bouton "Calculer Dates de Collecte")
l'avantage, c'est que, c'est beaucoup plus rapide d'execution, et surtout beaucoup plus "simple" à coder...

Pour l'instant, cette macro est lancée par le bouton
mais elle peut aussi etre lancé à chaque modif des décalages.; ou date ou autre..

J'ai commenté un maximum le code, je te laisse regarder
et essayer, ne serait ce que pour vérifier qu'il n'y a pas d'erreur dans les dates...
genre; regarde les lignes
ligne 2 (UN jour spécifié),
ligne 6(Deux jours spécifiés)
et la ligne 17 (PAS de jour spécifié)

 

[Laetitia L]

Alors, c'est très bien ce que vous avez fait, le fait de laisser les cellules taper à la main c'est une bonne idée.
Par contre j'ai fait un test et un message d'erreur apparait sur la macro :

 

[vgendron]

A quel moment as tu ce message?
parce que je viens de re cliquer sur le bouton. et tout se passe bien..

 

[vgendron]

Et je viens de voir qu'il faut modifier la macro des décalages. car elle inscrit des "D" dans la feuille "Instructions"

Sub DécalageToInstructions()
 Application.ScreenUpdating = False
 Application.Calculation = xlCalculationManual

With Sheets("Calendrier")
     nbcol = .Range("TabCalend").Columns.Count 'nb de colonnes dans la zone TabCalend
     nblig = .Range("TabCalend").Rows.Count 'Nb de lignes à parcourir dans la zone TabCalend
     For i = 1 To nbcol Step 7 'on parcourt les dates de la zone "TabCalend" qui sont une colonne sur 4
         For j = 1 To nblig 'pour chaque ligne
             ville = .Range("TabCalend").Item(j + 2, i) 'on récupère la ville
             IndJour = (Int(j / 9) Mod 9) * 9 'numéro de ligne de la date à prendre en compte
             Jour = .Range("TabCalend").Item(IndJour + 2, i) 'la date
             Decalage = .Range("TabCalend").Item(j + 2, i + 2) 'on récupère le décalage
              If ville <> "" And Decalage <> "" And Decalage <> "D" Then 'si il y a une ville ET un décalage
                 With Sheets("instructions") 'dans la feuille Instructions
                     Set ici = .Range("TabInst").Find(ville, lookat:=xlWhole) 'on cherche le nom EXACT de la ville
                     If Not ici Is Nothing Then 'si on l'a trouvée
                         .Range("O" & ici.Row) = Decalage 'on met le décalage dans la colonne P de la meme ligne
                     End If
                 End With
                 .Range("TabCalend").Item(j + 1, i + 1).ClearContents 'on efface le décalage de la feuille Calendrier
             End If
         Next j
     Next i
End With

 Application.Calculation = xlCalculationAutomatic
 Application.ScreenUpdating = True

End Sub