XL 2016 Propagation de données

[halecs93]

Bonjour, j'ai en feuil1 un modèle de semaine (simplifié) pour l'exemple.

En feuil2, un modèle, tout aussi simplifié de calendrier mensuel. Dans les cellules de cette feuille j'ai des formules du type : =INDEX(Feuil1!$B$1:$L$7;JOURSEM($A1;2);COLONNE()-1)
qui me permettent de rapatrier les données de la feuil1 vers la feuil2.

Seulement, une formule par cellule multipliée par 10 colonnes pour 31 jours.... ça fait beaucoup et c'est bien lourd.

Je cherche une "solution" en vba.

Si quelqu'un a une idée. Grand merci.

 

[sylvanu]

Ca, sans votre PJ je n'aurais pas trouvé.

les dates ont même été supprimées.

Faux.
En colonne A d'Octobre les dates n'ont pas été supprimées mais remplacées par des zéros :
Apparence : ----------------------------En retirant l'option Afficher un zéro quand la valeur est nulle:

---------

Donc 0 conduit au résultat que vous voyez. Car JOURSEM("00/00/00";2)=6 donc considéré comme un samedi.

En PJ j'ai modifié la formule. Celle ci n'est calculée que si en colonne A il n'y a ni vide, ni 0 :

Formule = "=SI(OU($A1=0;$A1="""");"""";INDEX(Entete!B$1:L$7;
;COLONNE()-1))"

 

[sylvanu]

Ce qui est rigolo, c'est que vous soyez tombé dedans!
Car dans l'univers Microsoft le 00/00/0000 n'existe pas car il commence le 01/01/1900.
C'est donc en toute rigueur un "bug" de la fonction JOURSEM. Elle aurait du retournée #REF! ou #N/A.

D'autant que même dans le calendrier Grégorien l'année 0000 n'existe pas non plus, JC est né le 25/12/01 donc le calendrier commence en l'an 01. ( même si historiquement c'est faux, mais c'est ainsi )
Il n'y a que dans la frise chronologique des archéologues que cette année 0000 existe.

 

[halecs93]

Ca, sans votre PJ je n'aurais pas trouvé.

Faux.
En colonne A d'Octobre les dates n'ont pas été supprimées mais remplacées par des zéros :
Apparence : ----------------------------En retirant l'option Afficher un zéro quand la valeur est nulle:
Regarde la pièce jointe 1182704--------- Regarde la pièce jointe 1182705
Donc 0 conduit au résultat que vous voyez. Car JOURSEM("00/00/00";2)=6 donc considéré comme un samedi.

En PJ j'ai modifié la formule. Celle ci n'est calculée que si en colonne A il n'y a ni vide, ni 0 :

Formule = "=SI(OU($A1=0;$A1="""");"""";INDEX(Entete!B$1:L$7;
;COLONNE()-1))"

Afin de mieux tester, j'ai créé les 12 onglets.

J'ai protégé les feuilles en laissant les cellules B1:K1 modifiables.

En déclanchant la macro, je rencontre bien une erreur ici : .Range(.Cells(1, "B"), .Cells(DL, DC)).FormulaLocal = Formule

Je crois comprendre que la macro vient modifier les colonnes A (dates des mois). Dans la mesure ou ces cellules sont bloquées, ça plante.

 

[sylvanu]

Plus la PJ initiale est précise et représentative, plus c'est simple d'éviter les écueils.
La protection pose problème sur la plage à définir sur le nombre de colonnes.
Donc j'ai remplacé la recherche de la dernière colonne en me limitant à la colonne K.

 

[halecs93]

Plus la PJ initiale est précise et représentative, plus c'est simple d'éviter les écueils.
La protection pose problème sur la plage à définir sur le nombre de colonnes.
Donc j'ai remplacé la recherche de la dernière colonne en me limitant à la colonne K.

Je suis navré... mais je ne vois pas la différence... même code. Même erreur.

 

[sylvanu]

Vraiment désolé. La bonne version était restée dans Téléchargements. Sorry.

 

[halecs93]

Vraiment désolé. La bonne version était restée dans Téléchargements. Sorry.

Ceci explique cela... mais encore un petit souci, si je peux me permettre.... J'ai entré une donnée sur la feuille "entete" en colonne D et le report se fait dans les colonnes C des onglets "mois". J'ai l'impression que les celulles vides en entete ne sont pas pris en compte tel quel... (je mets le fichier)

 

[sylvanu]

Des "$" ont sautés malencontreusement quelque part dans une version ... $B est devenu B.

 

[halecs93]

Des "$" ont sautés malencontreusement quelque part dans une version ... $B est devenu B.

Bravo, et grand merci. Cela fonctionne. Je n'ai plus qu'à adapter tout ceci à mon fichier réel

 

[halecs93]

Des "$" ont sautés malencontreusement quelque part dans une version ... $B est devenu B.

Bonjour, je reviens vers vous avec mon fichier presque finalisée.

Cependant, je rencontre un souci avec la routine de propagation. Je l'ai légèrement adaptée... mais je ne comprends pas pourquoi elle efface systématiquement la plage B7:N8 des feuilles mensuelles.

Si vous avez une idée... grand merci.

 

[sylvanu]

Bonjour Halecs,
Vous n'avez pas adapté les macros en fonction de votre nouveau contexte.
Dans la PJ initiale la plage où coller la formule commençait en B1.
Comme vous ne l'avez pas modifié il continue à le faire.
Donc je pense qu'il faut modifier :

.Range(.Cells(1, "B"), .Cells(DL, "N")).FormulaLocal = Formule
.Range(.Cells(1, "B"), .Cells(DL, "N")) = .Range(.Cells(1, "B"), .Cells(DL, "N")).Value

Pour initialiser la plage en ligne 9 :

.Range(.Cells(9, "B"), .Cells(DL, "N")).FormulaLocal = Formule
.Range(.Cells(9, "B"), .Cells(DL, "N")) = .Range(.Cells(9, "B"), .Cells(DL, "N")).Value

Par contre je ne comprends pas votre DL = .Cells(39, "B").Row pourquoi pas DL=39 si vous arrêtez votre plage à la ligne 39 ?
Et si vous voulez figer votre plage aux lignes 9:39 pourquoi ne pas utiliser Range("B9:N39") au lieu de truc comme Range(.Cells(9, "B"), .Cells(DL, "N")) ?
Un essai en PJ juste avec cette modif, mais je n'ai rien testé d'autre.

 

[sylvanu]

Re,
De plus je pense que la formule est évidemment fausse à cause du changement de plage.
Cependant je suis un peu largué par votre nouveau fichier car les macros ne sont pas commentées, et je n'arrive pas à suivre le fil.
Donc en PJ le fichier type initial avec les feuilles mois de votre nouvelle PJ, avec la macro :

Sub Propager()
Application.ScreenUpdating = False
For Each F In Worksheets
    If F.Name <> "Entete" Then
        With Sheets(F.Name)
            Formule = "=SI(OU($A9=0;$A9="""");"""";INDEX(Entete!B$1:K$7;JOURSEM($A9;2);COLONNE()-1))"   ' Formule à mettre à jour
            .[B9:N39].ClearContents
            .[B9:K39].FormulaLocal = Formule ' Adapter colonne K ( mise à K car idem plage Entete )
            .[B9:K39] = .[B9:K39].Value
        End With
    End If
Next F
End Sub

Inspirez vous de ça pour recoller les morceaux sur votre nouveau fichier.

 

[halecs93]

Bonjour Halecs,
Vous n'avez pas adapté les macros en fonction de votre nouveau contexte.
Dans la PJ initiale la plage où coller la formule commençait en B1.
Comme vous ne l'avez pas modifié il continue à le faire.
Donc je pense qu'il faut modifier :

.Range(.Cells(1, "B"), .Cells(DL, "N")).FormulaLocal = Formule
.Range(.Cells(1, "B"), .Cells(DL, "N")) = .Range(.Cells(1, "B"), .Cells(DL, "N")).Value

Pour initialiser la plage en ligne 9 :

.Range(.Cells(9, "B"), .Cells(DL, "N")).FormulaLocal = Formule
.Range(.Cells(9, "B"), .Cells(DL, "N")) = .Range(.Cells(9, "B"), .Cells(DL, "N")).Value

Par contre je ne comprends pas votre DL = .Cells(39, "B").Row pourquoi pas DL=39 si vous arrêtez votre plage à la ligne 39 ?
Et si vous voulez figer votre plage aux lignes 9:39 pourquoi ne pas utiliser Range("B9:N39") au lieu de truc comme Range(.Cells(9, "B"), .Cells(DL, "N")) ?
Un essai en PJ juste avec cette modif, mais je n'ai rien testé d'autre.

Hello, j'étais pile sur cette piste.... ligne 9 !!!!

 

[halecs93]

Re,
De plus je pense que la formule est évidemment fausse à cause du changement de plage.
Cependant je suis un peu largué par votre nouveau fichier car les macros ne sont pas commentées, et je n'arrive pas à suivre le fil.
Donc en PJ le fichier type initial avec les feuilles mois de votre nouvelle PJ, avec la macro :

Sub Propager()
Application.ScreenUpdating = False
For Each F In Worksheets
    If F.Name <> "Entete" Then
        With Sheets(F.Name)
            Formule = "=SI(OU($A9=0;$A9="""");"""";INDEX(Entete!B$1:K$7;JOURSEM($A9;2);COLONNE()-1))"   ' Formule à mettre à jour
            .[B9:N39].ClearContents
            .[B9:K39].FormulaLocal = Formule ' Adapter colonne K ( mise à K car idem plage Entete )
            .[B9:K39] = .[B9:K39].Value
        End With
    End If
Next F
End Sub

Inspirez vous de ça pour recoller les morceaux sur votre nouveau fichier.

Il est vrai que c'est bricolage sur bricolage mon fichier.... maintenant, je vais tenter de modifier encore un peu. En gros, si une date en colonne 1 fait partie de la plage "Vac" alors la ligne doit rester vierge et idem pour les jours se trouvant dans la plage Fer.

Le but de tout ceci c'est de "simplifier et d'alléger" le classeur. En effet, initialement, sur chaque cellulle de chaque feuille j'avais des formules du genre :

=SI($A$9=""; ""; SI(OU(JOURSEM($A$9; 2)>5; NB.SI(Fer;$A$9)>0; NB.SI(Vac;$A$9)>0; NB.SI(CA;$A$9)>0); ""; (DONNEES!$L$16+DONNEES!$L$17)-SI($A$9="";"0";SI(OU(JOURSEM($A$9; 2)<>3; NB.SI(Fer;$A$9)>0; NB.SI(Vac;$A$9)>0); "0"; (DONNEES!$L$16+DONNEES!$L$17)))))


ARGH !!!!!