Positionner une valeur en fonction de l'axe X et Y du tableau

Bonjour,

Sur un planning journalier, je cherche à positionner automatiquement la lettre A sur le planning au début de la journée d'un employée et la lettre Z à la fin de sa journée.

Sa prise de poste est notée dans la colonne E à partir de la ligne 6 et son heure de fin dans la colonne F juste à côté.

Le planning est découpé par 1/4 d'heure, sur la ligne 5 j'ai rentré les valeurs 08:00, 08:15, 08:30, etc... (excel affiche des # mais les valeurs sont bien là, la ligne sera masqué par la suite)

J'ai créé une mise en forme qui passe toute la case en gris foncé si la valeur A ou Z est rentrée dans une cellule (ça me semblait plus simple comme ça mais peut -être pas je débute sur Excel et encore en plus en VBA.

J'ai créé un début de procédure me permettant d'effacer les A et Z déjà existant. Les heures dans les colonnes E et F étant amené à bouger régulièrement au cours d'und journée j'aimerais pouvoir "recalculer" le début et la fin pour chaque employé.

Je suppose qu'il faudrait faire une boucle et faire quelque chose avec la fonction find mais je bloque...

Le début de mon code :

Sub AZ()
Application.ScreenUpdating = False

'Efface les A et Z de la BS
Dim cel As Range, tabl As Range
Set tabl = Range("H5:CE50")
For Each cel In tabl
If cel.Value = "A" Or cel.Value = "Z" Then
cel.ClearContents
Else
End If
Next

'Positionne les AZ en fonction des shifts

End Sub
••••ˇˇˇˇ

Le fichier :

Si vous avez une solution ou des pistes de réflexions je suis preneur...

Merci d'avance pour votre aide !

Oncle Sid

Salut,

si j'ai bien compris, pour l'employé ligne 6, "A" en [J6] et "Z" en [AK6] ?

• quid des valeurs déjà présentes dans ces cellules ?
• plus d'explications seraient bienvenues quant aux changements d'heures dont tu parles ;
• traitement automatique lors d'un changement, j'imagine ? Pas de bouton !?

A+

Merci curulis57

Ça ne serait pas J6 car ma première colonne démarre a 8h en fait mais dans l'idée c'est ça.

Ces cases foncées me permettent d'encadrer la journée pour que le tableau soit plus lisible.

Si une valeur se trouve déjà dans la cellule j'aimerais l'écraser. Et encore mieux si ça écrase les valeurs avant le A sur la même ligne et après le Z mais bon j'en suis pas là

Effectivement si c'est en traitement automatique c'est encore mieux mais un bouton c'est très bien aussi.

Cette fonction me permettait surtout de créer les plannings jour après jour. Des fois j'ai 4 personnes qui commencent a 7h45, des fois 5 des fois 3...

Mais aussi en cours de journée car lorsque j'ai des absents ils sont remplacés par d'autres qui ne font pas forcément le même horaire, ou il peut arriver qu'ils partent finalement plus tôt ou plus tard. D'où l'idée de cette fonction. Aujourd'hui je fais tout ça a la main mais ça prend du temps

Voilà j'espère que ces précisions apporte de la clarté, c'est pas évident a pas expliquer simplement

Merci a ceux qui prendront le temps de jeter un œil

Ben, explique, bon sang!

Si ce n'est pas en [J6], c'est où ? La journée commence en [H:H] à 8:00 : je le fabrique comment ton 7:45 ??

Désolé tu as raison, j'ai mis à jour le fichier en ajoutant une colonne 7h comme ça c'est bien en J6

Salut OncleSid,

voilà déjà un petit quelque chose...

Pas le temps de continuer pour l'instant : j'ai une deadline pour JumpOff !

Comme d'hab', j'en fais plus qu'on me demande !

En l'état, tu as deux sortes de sélection possible :

• si tu veux inscrire une info sur une série de cellules (F, par ex.), double-clic sur "F" puis sélectionne le Range où tu veux voir apparaître "F" ;
• pour marquer un horaire facilement, sans double-cliquer cette fois, sélectionne simplement le-dit horaire : tes "A" et "Z" s'inscrivent, les côtés extérieurs de la sélection s'effacent et... les heures de début et de fin se calculent seules en colonnes [E:F]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
Dim rCelA As Range, rCelB As Range
'
Application.EnableEvents = False
Application.ScreenUpdating = False
'
iRow = Target.Row
If Selection.Count = 1 Then

Else
    If Selection.Rows.Count = 1 Then
        If Selection.Cells(1, 1).Font.Italic = False Then
            Set rCelA = Selection.Cells(1, 1)
            Set rCelB = Selection.Cells(1, 1).Offset(0, Selection.Columns.Count - 1)
            iHourA = Split(Cells(4, rCelA.Column - (rCelA.Column Mod 4)), "H")(0)
            iHourB = Split(Cells(4, rCelB.Column - (rCelB.Column Mod 4)), "H")(0)
            rCelA.Offset(0, -1).Value = "A"
            rCelB.Offset(0, 1).Value = "Z"
            sColA = Split(Columns(rCelA.Column - 2).Address(ColumnAbsolute:=False), ":")(1)
            sColB = Split(Columns(rCelB.Column + 2).Address(ColumnAbsolute:=False), ":")(1)
            Range("H" & iRow & ":" & sColA & iRow).Value = ""
            Range(sColB & iRow & ":" & "CI" & iRow).Value = ""
            Range("E" & iRow).Value = TimeValue(CStr(iHourA) & ":" & CStr(Choose((rCelA.Column Mod 4) + 1, "00", "15", "30", "45")))
            Range("F" & iRow).Value = TimeValue(CStr(iHourB) & ":" & CStr(Choose((rCelB.Column Mod 4) + 1, "14", "29", "44", "59")))
        Else
            Selection.Cells(1, 1).Font.Italic = False
            Selection.Value = Selection.Cells(1, 1).Value
        End If
        Range("D" & iRow).Select
    End If
End If
'
Application.EnableEvents = True
Application.ScreenUpdating = True
'
End Sub

Quand j'aurai terminé l'autre projet, je continuerai avec toi.

Attention, je n'ai placé aucun garde-fou pour les sélections de cellules. Á toi de rester dans les clous!

A+

Merci beaucoup curulis57 !

Je dois mal m'y prendre car je n'arrive ni a faire apparaître les A et Z ni a faire la manipulation du double clic pour mettre une valeur sur une sélection.

Mais t'embête pas avec cette deuxième chose j'ai déjà créé un useform me permettant de le faire qui marche très bien

Par ailleurs je vois que tu as retiré les dates dans les cellules d'heure. Je les avaient mises car parfois l'employé termine après minuit et du coup sans les dates excel n'arrivait pas a calculer la différence entre le début et la fin. Mais y a surement une autre solution qui m'échappe

Merci encore pour ton aide

Salut OncleSid,

Voilà...

• les dates sont maintenant des dates complètes avec l'heure ;
• le jour est adapté selon que la pause se termine après minuit.

La sélection des cellules est une simple sélection comme tu le ferais pour sélectionner une ligne de cellules.

            dDateA = Format(CDate(Range("E" & iRow).Value), "dd/mm/yyyy")
            dDateB = Format(CDate(Range("F" & iRow).Value), "dd/mm/yyyy")
            dDateB = DateAdd("d", IIf(iHourB < 3, 1, 0), dDateB)

            Range("E" & iRow).Value = dDateA + TimeValue(CStr(iHourA) & ":" & CStr(Choose((rCelA.Column Mod 4) + 1, "00", "15", "30", "45")))
            Range("F" & iRow).Value = dDateB + TimeValue(CStr(iHourB) & ":" & CStr(Choose((rCelB.Column Mod 4) + 1, "14", "29", "44", "59")))

Pour recopier une valeur, par ex. "F", tapes d'abord le "F" ensuite double-clic dessus et ensuite, sélectionne-le et étire la sélection sur le nombre de cellules voulues.

Note que c'est totalement équivalent à étirer une formule avec le caret (merci, Oxydum ! ) de recopie.

A+

Waaaa t'es un génie merci !!!

Ça va trop me servir le double clic pour tirer une valeur sans cassé les bordures ! Bien mieux que mon UserForm

Je comprends pas bien comment fonctionne la mise en place des A et Z. Y a une manip à faire pour mettre a jour en fonction des horaires rentrés ?

Merci encore pour ta précieuse aide

Salut OncleSid,

si l'horaire change, tu refais la sélection glissée et l'affichage des "A-Z" et des heures en [E:F] se corrige de lui-même !

A+

Hello curulis57 ! Hello les autres aussi

Désolé j'ai mis du temps a répondre mais j'essayais de comprendre ton code et son fonctionnement.

J'ai beau tester pleins de trucs la manip ne marche pas toujours... Je dois mal m'y prendre certainement. Des fois j'ai les horaires qui se mettent en italique.

Y aurait-il possibilité d'activer le réajustement de la ligne avec un bouton (en prenant l'ActiveCell en référence pour la ligne a ajuster par le exemple) ?

Merci encore pour ton aide je vais continuer d'essayer de comprendre comment fonctionne ton code mais c'est un poil trop avancé pour mon petit niveau

Bonne soirée a tous !

Oncle Sid

Salut OncleSid,

Voici quelques commentaires pour t'aider à suivre le raisonnement.

J'ai ajouté l'effacement d'éventuelles anciennes valeurs "A-Z" incluses dans un nouvel horaire modifié.

Bonne lecture!

    If Selection.Rows.Count = 1 Then                                                    'si la sélection tient sur la même ligne
        If Selection.Cells(1, 1).Font.Italic = False Then                               'si la 1ère cellule de la sélection n'est pas en italique
            Set rCelA = Selection.Cells(1, 1)                                           'position de la 1ère cellule de la sélection
            Set rCelB = Selection.Cells(1, 1).Offset(0, Selection.Columns.Count - 1)    'position de la dernière cellule de la sélection
            Set rCel = Range(rCelA.Address & ":" & rCelB.Address).Find(what:="A", lookat:=xlWhole, LookIn:=xlValues, searchdirection:=xlNext)
            If Not rCel Is Nothing Then rCel.Value = ""     'recherche dans la sélection un éventuel "A" d'un horaire précédent et effacement du "A"
            Set rCel = Range(rCelA.Address & ":" & rCelB.Address).Find(what:="Z", lookat:=xlWhole, LookIn:=xlValues, searchdirection:=xlPrevious)
            If Not rCel Is Nothing Then rCel.Value = ""     'recherche dans la sélection un éventuel "Z" d'un horaire précédent et effacement du "Z"
            iHourA = Split(Cells(4, rCelA.Column - (rCelA.Column Mod 4)), "H")(0)       'calcul de l'heure en ligne 4 correspondant à la 1ère cellule
            iHourB = Split(Cells(4, rCelB.Column - (rCelB.Column Mod 4)), "H")(0)       'calcul de l'heure en ligne 4 correspondant à la dernière cellule
            dDateA = Format(CDate(Range("E" & iRow).Value), "dd/mm/yyyy")               'calcul de la date dans la colonne [E:E]
            dDateB = Format(CDate(Range("F" & iRow).Value), "dd/mm/yyyy")               'calcul de la date dans la colonne [F:F]
            dDateB = DateAdd("d", IIf(iHourB < 3, 1, 0), dDateB)                        'ajoute 1 jour si iHourB < 3
            rCelA.Offset(0, -1).Value = "A"                                             'cellule avant 1ère cellule de la sélection = "A"
            rCelB.Offset(0, 1).Value = "Z"                                              'cellule après dernière cellule de la sélection = "Z"
            sColA = Split(Columns(rCelA.Column - 2).Address(ColumnAbsolute:=False), ":")(1)     'calcul de la lettre de colonne de la 2e cellule avant la 1ère cellule de la sélection
            sColB = Split(Columns(rCelB.Column + 2).Address(ColumnAbsolute:=False), ":")(1)     'calcul de la lettre de colonne de la 2e cellule après la dernière cellule de la sélection
            Range("H" & iRow & ":" & sColA & iRow).Value = ""                           'effacement des valeurs avant la sélection
            Range("H" & iRow & ":" & sColA & iRow).Interior.Color = xlNone              'effacement des couleurs avant la sélection
            Range(sColB & iRow & ":" & "CI" & iRow).Value = ""                          'effacement des valeurs après la sélection
            Range(sColB & iRow & ":" & "CI" & iRow).Interior.Color = xlNone             'effacement des couleurs après la sélection
            Range("E" & iRow).Value = dDateA + TimeValue(CStr(iHourA) & ":" & CStr(Choose((rCelA.Column Mod 4) + 1, "00", "15", "30", "45")))   'calcul de l'heure de début de sélection en [E:E]
            Range("F" & iRow).Value = dDateB + TimeValue(CStr(iHourB) & ":" & CStr(Choose((rCelB.Column Mod 4) + 1, "14", "29", "44", "59")))   'calcul de l'heure de fin de sélection en [F:F]
        Else
            Selection.Value = Selection.Cells(1, 1).Value           'copie d'une valeur si la 1ère cellule est en italique
            Selection.Cells(1, 1).Font.Italic = False               'effacement de l'italique de la 1ère cellule de la sélection
        End If

Pas rencontré de problèmes particulier dans les manip' !!

A+

Merci pour les commentaires ça va bien m'aider a comprendre comment ton code fonctionne

Je découvre MOD et SPLIT que je n'avais encore jamais vu, c'est intéressant

Un truc que j'ai du mal à saisir, c'est l'heure qui se met a jour en fonction des positions de A et Z sur la ligne ou l'inverse ?

Pour mettre a jour je dois faire la technique double clic + sélection glissé sur quelle zone ?

Salut OncleSid,

comprends pas trop ce que tu veux dire...

Pour mettre a jour je dois faire la technique double clic + sélection glissé sur quelle zone ?

A+

Salut curulis57 !

En fait je comprends pas où je dois double cliquer puis glisser. C'est bien ça la manip à faire non ?

Tu disais dans un précédent de double cliquer puis de cliquer et glisser pour faire une sélection, mais quand je double clique j'ai un curseur qui permet de modifier la valeur de la cellule donc j'arrive pas a glisser lol

Compliqué a expliquer, je ferais une capture vidéo pour te mettre montrer 😜

Salut OncleSid,

tu as deux sortes de "mises à jour", comme tu dis :

- pour créer ou modifier un horaire (A-Z)

Dans ce cas-ci, tu fais une simple sélection en ligne comme tu le ferais pour sélectionner quelques cellules. C'est tout !

- pour copier un de tes symboles (F, B, CC,...) sur plusieurs cellules

Là où tu veux créer cette suite :

* tu encodes d'abord le symbole et tu valides avec ENTER ;

* ensuite, tu double-cliques sur cette cellule que tu viens d'encoder ce qui a pour effet de la mettre en italique ;

* ensuite, à partir de cette cellule, tu fais une simple sélection en ligne comme tu le ferais pour sélectionner quelques cellules.

Vois pas comment t'expliquer ça mieux !!

A+