Contrôle de chevauchement dans un planning Excel

Bonjour,

J'ai besoin d'aide pour terminer la macro ci-après. Elle cherche à contrôler des éventuels chevauchements dans un planning,

58exemple-tr.xlsm (232.68 Ko)

Pour le moment la macro cherche à tester une seule colonne (1 jour = 1jour). Les peronnes plannifiées ne se suivent pas forcément dans le planning.

C'est pour cette raison que je cherche d'abord à répérer pour une personne les lignes où elle se trouve. Je prends la dernère plage horaire (de C à D) que je compare aux autres plages horaires (A et B) déjà plannifié pour la même journée.Si'il y a chevauchement je voudrais mettre une croix sur la tâche et mettre en rouge la plage horaire.

La macro buugg quelque part avant d'arriver à la comparaison.

Pouvez-vous svp m'aider ?

Sub Contôle_Chevauchement()

Application.ScreenUpdating = False

Dim Collaborateur As String
Dim Ligne As Long, Colonne As Long
Dim A As Variant, B As Variant, C As Variant, D As Variant
Dim Ligne_à_Contrôler As Long

Collaborateur = Range("E" & ActiveCell.Row).Value
 ' Je récupére la personne pour qui je souhaite faire le ctrl, à 
'A terme, l'objectif est de faire une macro qui fait ce contrôle pour toutes les personnes (ligne par ligne) présentes dans le 'planning. Aussi je ne parcours qu'une seule colonne. A terme, il y aurait un boucle de la colonne 8 à 38.

Ligne = ActiveCell.Row
Colonne = ActiveCell.Column

    With ActiveSheet.Range("E9:E" & ActiveCell.Row - 1)
        Set C = .Find(Collaborateur, LookIn:=xlValues)

        'l'idée c'est de comparer la plage horaire que l'on vient de mettre pour une personne 
         'n'est pas en conflit (chevauchement) avec une autre tâche de la même journée (donc dans la colonne)

    If Not C Is Nothing Then
    firstAddress = C.Address
        Do
        MsgBox C.Row
                MsgBox "C= " & Format(Cells(ActiveCell.Row, 6).Value, "hh:mm") & _
                " D= " & Format(Cells(ActiveCell.Row, 7).Value, "hh:mm")
                MsgBox "A= " & Format(Cells(C.Row, 6).Value, "hh:mm") & _
                " B= " & Format(Cells(C.Row, 7).Value, "hh:mm")

        'Les bornes de la plage horaires que l'on vient de renseigner sont C et D. Les autres plages horaires à comparer avec celle
        'que 'lon vient de renseigner  ont pour borne A et B.Succesivement dans la comparaison, il y a souvent plusieurs A et B.

              C = Cells(ActiveCell.Row, 6).Value
              D = Cells(ActiveCell.Row, 7).Value
              B = Cells(C.Row, 7).Value
              A = Cells(C.Row, 6).Value

          '= s'il existe une tâche dans la colonne et
           'SI(OU(ET(C<=B;C>=A);ET(A<=D;A>=C));"Chevauchement";"Non chevauchement")
        If Cells(C.Row, Colonne).Value <> "" And ((C <= B) And (C >= A)) Or ((A <= D) And (A >= C)) Then

                 Range(Cells(ActiveCell.Row, 6), Cells(ActiveCell.Row, 7)).Interior.Color = vbRed
                 Cells(ActiveCell.Row, Colonne).Borders(xlDiagonalDown).LineStyle = xlContinuous
                 Cells(ActiveCell.Row, Colonne).Borders(xlDiagonalUp).LineStyle = xlContinuous

        Else
                  Range(Cells(ActiveCell.Row, 6), Cells(ActiveCell.Row, 7)).Interior.Color = RGB(255, 255, 255)
                 Cells(ActiveCell.Row, Colonne).Borders(xlDiagonalDown).LineStyle = xlNone
                 Cells(ActiveCell.Row, Colonne).Borders(xlDiagonalUp).LineStyle = xlNone

        End If

        Set C = .FindNext(C)

        Loop While Not C Is Nothing And C.Address <> firstAddress
    End If
    End With

    Application.ScreenUpdating = True
    End Sub

Bonjour,

Une proposition à étudier !?

la formule de base, sans VBA :

=SOMMEPROD(([@Début]<=[Fin])*([@Fin]>=[Début])*([Collaborateurs]=[@Collaborateurs]))

Cdlt.

74exemple-tr.xlsm (32.33 Ko)

Merci pour votre réponse mais la configuration du planning ne s'y prête pas. En effet, je voudrais mettre en évidence la tâche qui rentre en conflit avec une des tâches dans le même jour( les colonnes suivantes)

capture

Bonjour,

Une proposition à étudier !?

la formule de base, sans VBA :

=SOMMEPROD(([@Début]<=[Fin])*([@Fin]>=[Début])*([Collaborateurs]=[@Collaborateurs]))

Cdlt.

Exemple TR.xlsm

Bel outil Jean-Eric, simple et efficace, je garde (en plus de la fonction FORMULETEXTE que je ne connaissais pas).

@ MarieG :

Bonjour,

je n'ai pas compris cette notion de conflit ... peux-tu donner un cas et dire en quoi il y a conflit ?

Bonjour,

Un complément pour aborder la chose !...

Voir MFCs

Cdlt.

43exemple-tr.xlsm (34.01 Ko)
annotation 2019 08 11 102002

Tu peux aussi faire une MFC

=SOMMEPROD(($E$9:$E$17=$E9)*($G9>$F$9:$F$17)*($F9<$G$9:$G$17)*(H$9:H$17<>""))

pas facile à voir car tu as déjà des couleurs !!

Cela dit c'est un peu le même principe que Jean-Eric qui a l'avantage d'avoir une récap en tête de ligne.

31exemple-tr.xlsx (19.19 Ko)

re,

@Steelson,

N'y a t-il pas un souci avec LL qui est tout seul ?

Je titille !

Cdlt.

Bonjour,

Merci beaucoup pour votre réponse qui se rapproche de ce que je souhaite. Je vous fais une capture d'écran pour explications:

capture

Bonjour,

Un complément pour aborder la chose !...

Voir MFCs

Cdlt.

Exemple TR.xlsm

Annotation 2019-08-11 102002.png

Bonjour,

Merci pour votre réponse.

Non il n y a pas de souci avec LL. Il peut arriver q'une personne soit présente une seule fois dans un planning qui en réalité un planning mensuel.

re,

@Steelson,

N'y a t-il pas un souci avec LL qui est tout seul ?

Je titille !

Cdlt.

re,

@Steelson,

N'y a t-il pas un souci avec LL qui est tout seul ?

Je titille !

Cdlt.

tu as raison, j'ai oublié le
>1
! j'aurais mieux fait de m’abstenir, d'autant que ta solution est plus intéressante !

Re,

@MarieG,

Avant d'aller plus loin, si LL n'est pas un souci et que tout seul il peut se chevaucher, il y a question !..

Cdlt.

En fait, je veux dire qu'il peut y avoir une seule ligne pour LL dans tout le planning mensuel si bien qu' une de ses tâches ne sera jamais en conflit avec une autre de ses tâches. Par Exemple, l'unique ligne et plage horaire pour LL, (7h30 :13h30) sera utilisée soit le 12, 13, 14 ou le 15 août.

Merci

Re,

@MarieG,

Avant d'aller plus loin, si LL n'est pas un souci et que tout seul il peut se chevaucher, il y a question !..

Cdlt.

Bonjour,

Une petite mise à jour qui élimine le chevauchement de la première occurrence.

Je ne sais pas si cela va t'aider beaucoup.

Dans l'idée, on traite les chevauchements à la saisie des données !...

Cdlt.

Nota :

@Steelson

Peux-tu regarder la MFC principale ?

37exemple-tr-2.xlsx (27.00 Ko)

Nota :

@Steelson

Peux-tu regarder la MFC principale ?

rien à dire ... superbe ! d'ailleurs je stocke la solution car il y a un brin de génie (si si !)

en passant, tu aurais pu mettre en F5 pour conserver la syntaxe tableaux ...

=NB.SI(Tableau1[[#En-têtes];[Collaborateurs]]:[@Collaborateurs];[@Collaborateurs])

Bonjour Jean-Eric,

Bonjour Steelson,

Je vais tester tout ça. Cela m'a l'air pas très bien.

Super Contente

Merci beaucoup, je vous reviens.

Bonjour,

Une petite mise à jour qui élimine le chevauchement de la première occurrence.

Je ne sais pas si cela va t'aider beaucoup.

Dans l'idée, on traite les chevauchements à la saisie des données !...

Cdlt.

Nota :

@Steelson

Peux-tu regarder la MFC principale ?

Exemple TR 2.xlsx

Bonsoir Jean-Eric,

Bonsoir Steelson,

Je ne sais pas comment vous remercier. Vous m’avez bien m’aider. C’est pour cela que je suis un peu gênée de demander un petit dernier réglage. Sinon grâce à vos contributions, mon fiche touche l'imperfection. Mille mercis.

Ci-après le dernier détail:

capture

PS: Les lignes ne se suivent pas forcément :la tâche "Comp" que j'ai effacée aurait pu être sur une autre ligne que celle au dessus de la tâche "Audi".

Merci

Bonjour Jean-Eric,

Bonjour Steelson,

Je vais tester tout ça. Cela m'a l'air pas très bien.

Super Contente

Merci beaucoup, je vous reviens.

Bonjour,

Une petite mise à jour qui élimine le chevauchement de la première occurrence.

Je ne sais pas si cela va t'aider beaucoup.

Dans l'idée, on traite les chevauchements à la saisie des données !...

Cdlt.

Nota :

@Steelson

Peux-tu regarder la MFC principale ?

Exemple TR 2.xlsx

Bonjour,

je laisse Jean-Eric répondre, mais pour moi il faut donc revenir à la solution précédente sans la colonne occurrences et traiter tous les items sur le même pied d'égalité, le premier comme le dernier.

Bonsoir MarieG,

Bonsoir Jean-Eric,

Bonsoir Steelson,

Si j'ai bien compris le besoin je vous propose, sur la base de votre excellent travail, un petit complément afin de visualiser les éventuels chevauchements horaires par jour d'une part, et d'autre part les éventuels doublons d'opérations par jour également.

En effet je vois que les saisies se font par liste déroulantes (elles ne sont pas actives dans l'exemple).

Cordialement

30exemple-tr-3.xlsm (27.63 Ko)

Bonjour,

Notre sauveur ?

Cdlt.

Bonsoir lheveder,

Bonsoir Jean-Eric,

Bonsoir Steelson,

Merci pour vote contribution. La colonne date rajoutée ne me convient pas vraiment. Enfin, je ne vois comment lire cette date alors qu'il y a des colonnes dates. Je pense qu'elle va créer des confusions. Je préfère ne peut pas l'utiliser.

Sinon en fait je voudrais si possible pouvoir voir la barre sur une tâche s'enlever même si le check n'est pas bon pour toute la ligne:

Par exemple, pour le 14/08/2018, j'ai deux tâches, "Audi" et "m" qui rentrent en conflit. De 9h15 à 16h puis de 13h à 13h30.

capture1

J'ai réussi à mettre en avant que dans cette amplitute horaire j'ai deux tâches avec la formules suivantes :

SOMMEPROD(($E5>$D$5:$D$14)*($D5<$E$5:$E$14)*($B$5:$B$14=$B5)*($I$5:$I$14<>"")*($I5<>""))

(L'exemple porte que sur le 14/08)

J'ai une ideé mais je suis incapable de la mettre en oeuvre: même si je suis sur une ligne avec un check pas bon, je ne barre pas la tâche si le résutat de ma formule en haut est égal à 1. Par exemple, dans le tableau ci-après, si je supprime la tâche "Audi" ou la tâche "m", je vais me retrouver avec 1.

capture2

Merci

Bonsoir MarieG,

Bonsoir Jean-Eric,

Bonsoir Steelson,

Si j'ai bien compris le besoin je vous propose, sur la base de votre excellent travail, un petit complément afin de visualiser les éventuels chevauchements horaires par jour d'une part, et d'autre part les éventuels doublons d'opérations par jour également.

En effet je vois que les saisies se font par liste déroulantes (elles ne sont pas actives dans l'exemple).

Cordialement

Rechercher des sujets similaires à "controle chevauchement planning"