Générer un planning d'absence via macro

[alex67800]

Bonjour le Forum,
Une nouvelle fois j'ai besoin d'un oeil expert pour m'aider à me dépatouiller pour créer une macro.

J'utlise un planning hebdomadaire sous excel qui est alimenté par un ERP. Celui-ci me renseigne pour chaque journée et ligne de production le personnel qui y est affecté.

Toutefois cet ERP ne traite que le personnel affecté, donc ne transfert aucune liste de personnes absentes pour cause de congé, absence, RTT, maladie, etc....

Ce que je souhaiterai faire, est:
A partir d'une feuille "data" dans laquelle j'ai intégré tout mon personnel, faire une boucle sur mon extraction.
Si je ne retrouve pas le personnel, j'incrémente une liste sur une autre plage toutes les occurences absente, cela pour chaque journée (du lundi au vendredi).

Je vous joins un petit fichier exemple, avec la mise page du planning (je précise que cette construction est figée), ainsi que le planning d'absence dans lequel je voudrais faire apparaitre les absents.

Autre info, le planning est sur 4 semaines (empilage horizontal), alors que mon planning absent en empilage vertical (pour tenir sur une seule feuille)

J'éspère avoir été suffisament précis.

Je vous remercie d'avance pour toute l'aide que vous pourrez m'apporter et le temps que vous allez me consacrer.

A bientôt

Alex

 

[alex67800]

Re : Générer un planning d'absence via macro

Bonjour Le Forum,
Visiblement ce que j'éssai de faire n'est pas très aisé, je m'en doutais.
J'ai beau retourner le problème j'ai du mal à trouver une solution.
J'ai toutefois commencé à coder mes plages de cellule, dont je vous joins mes reflexions.

'Ci dessous les plages des 4 semaines de mon planning
plage1 = Range("B5:B30,F5:F30,J5:J30,N5:N30,R5:R30") 'semaine 1
plage2 = Range("AD5:AD30,AH5:AH30,AL5:AL30,AP5:AP30,AT5:AT30") 'semaine 2
plage3 = Range("BF5:BF30,BJ5:BJ30,BN5:BN30,BR5:BR30,BV5:BV30") 'semaine 3
plage4 = Range("CH5:CH30,CL5:CL30,CP5:CP30,CT5:CT30,CX5:CX30") 'semaine4


'Ci dessous la liste du personnel dont je veux comparer les noms

Salarié = Sheets(1).Range("B65536").End(xlUp).Row ' liste du personnel

'Ci dessous les plages cible ou je veux recopier les absents (exprimé par la suite sous forme de Target.offset(0,ListeRésultat)
Listerésultat = 113 ' colonne à partir de laquelle j'édite le résultat du lundi de la semaine 1
Listerésultat2 = 110 ' colonne à partir de laquelle j'édite le résultat du mardi de la semaine 1
Listerésultat3 = 107 ' colonne à partir de laquelle j'édite le résultat du mercredi de la semaine 1
Listerésultat4 = 104 ' colonne à partir de laquelle j'édite le résultat du jeudi de la semaine 1
Listeréusltat5 = 101 ' colonne à partir de laquelle j'édite le résultat  du vendredi de la semaine 1

Je me rend compte en recopiant le code sur le fil, que je vais du coup devoir multiplier les ListeRésultat
pour chaque semaine, cela va être lourd. Je dois être sur une fausse piste.

Une idée?

Merci pour votre aide.

Edit:Suppression des balises "ouleur" qui ne resorte pas dans le code

 

[Bebere]

Re : Générer un planning d'absence via macro

bonjour Alex
voilà une solution

 

[alex67800]

Re : Générer un planning d'absence via macro

Bonjour Bebere,
Je me disais bien que je construisais une usine à gaz, l'on reconnait là les Barbatruc

UNe lecture en diagonale montre bien qu'il y avait plus simple.

Je vias maintenant essayer de comprendre la structure de ton code.

un grand merci, je vais essayer de le transcrire dans mon fichier excel.

Un grand merci d'avance., je reviendrai certainement vers toi pour m'éclairer.

Alex

 

[alex67800]

Re : Générer un planning d'absence via macro

Bonsoir Bebere, le Forum,

Dans l'ensemble j'ai bien compris ton code.
Par contre pourrais-tu me dire à quoi correspond cette partie:

If i = 0 Then
ReDim Preserve Absent(i): Absent(i) = .Cells(11, C): i = i + 1 
ReDim Preserve Absent(i): Absent(i) = listeref(l, 1): i = i + 1
Else
ReDim Preserve Absent(i): Absent(i) = listeref(l, 1): i = i + 1
End If
End If

J'avoue que je cale un peu.

Une autre idée:
Je vérifie sur une semaine entière du lundi au dimanche s'il n'y pas au moins une fois le personnel présent.

Le cas échéant, j'édite les absents dans la colonne DK pour la semaine 1, DL pour la semaine 2, DM pour la 3 et DN pour la 4.

La ligne 4 (ligne des titres) prendrai une valeur dans 4 plages différentes du genre J2, AL2, BN2 et CP2.

Pourquoi cette nouvelle demande? Dans ma base , la macro génère une liste très longue, au-delà de la plage d'impression définie (normal beaucoup de personne en repos).

Cela permettrai de réduire considérablement la longueur de la page.

Sinon ton code fonctionne à merveille.

Une idée?

Merci encore.

Alex

P.S:J'ai passé l'après-midi dessus sans y parvenir en modifiant ton code, j'ai essayé tellement de modifications que je ne saurais vous les présenter, j'ai donc repris ton code initial.

 

[Bebere]

Re : Générer un planning d'absence via macro

bonjour Alex
changer en partie le code de module1
code dans module2 pour n° semaine
change en date les données de ligne4 et une fonction donne le n° de la semaine

 

[alex67800]

Re : Générer un planning d'absence via macro

Bonjour Bebere, le Forum,

Merci pour ta réponse.
Pas mal le code pour extraire la date d'un texte, j'y avais pas pensé (et n'aurais d'ailleurs pas trouvé )

Le résultat est presque celui auquel je pensai.

Toutefois je vouali dire, que je considère une personne absente, si et seulement si, elle n'est pas présente au moins une fois dans la semaine
Dans ce cas je l'inscrit dans le tableau des absents.

(Si présent le mardi de semaine 1, il n'apparait pas dans la colonne semaine 1)


Le tableau absent contient 4 colonnes (1 par semaine), il ne faut pas décomposer la semaine dans celle-ci.

Cela a pour but de ne pas générer de doublons et donc réduire le nombre de ligne du tableau.

Pourriez-vous me dire quelle partie du code il faut modifier, cela me fera pratiquer.

Merci d'avance.

Alex

 

[alex67800]

Re : Générer un planning d'absence via macro

Re bonjour le forum,

J'essai de dcomposer le code de Bebere, je vous joint mon analyse:

Ici je défini mes plages tels que colonne début et fin pour CDébut et Cfin
Li pour ligne et CDEST pour la colonne ou je crée ma liste Absent

Cdebut = 2: Cfin = 19
li = 4: CDest = 115


With Worksheets("Planning")
.Cells(3, "DK").CurrentRegion.ClearContents

For Col = Cdebut To 105 Step 28 'B à DA
Set rng = .Range(.Cells(5, Col), .Cells(5, Col + 27))
For C = Col To Col + Cfin Step 4 'boucle lu à ve

Ici j'effectue la recherche d'occurence

Set Cel = .Columns(C).Find(ListeRef(l, 1), LookIn:=xlValues, lookat:=xlWhole)

Ici je génère la liste Absent

For j = 0 To UBound(Absent)
.Cells(li + j, CDest) = Absent(j)
Next j

A partir de cette ligne une fois C atteint , je vide la liste Absent et vais à la première ligne vide de la colonneet boucle sur le jour suivant

Erase Absent: i = 0
li = .Cells(100, CDest).End(xlUp).Row + 1

Next C

Ici je reprend la ligne 4 et mais 2 colonnes à droite quand j'ai atteint la dernière ligne de vendredi

li = 4
CDest = CDest + 2

Next Col

Pourquoi avoir ajouter ces 2 lignes?
Je vois bien de quelle plage il s'agit, sans plus.

Set rng = .Range(.Cells(5, Col), .Cells(5, Col + 27))

If Application.WorksheetFunction.CountIf(rng, "=" & ListeRef(l, 1)) > 0 Then Exit For

Pour que la boucle ce fasse en une seule fois pour toute la semaine, je pense qu'il faut modifier le code For C, car ici il va considérer chaque jour comme une nouvelle liste Absent.
Pour ma part je voudrai qu'il prenne en une seule fois du lundi au dimanche.
Pour intégrer le dimanche, je change CFin= 19 par CFin = 27, jusque là c'est bon.

Des conseils?

Merci d'avance pour vos explications.

 

[alex67800]

Re : Générer un planning d'absence via macro

Re bonjour le forum,

J'ai transcri le code dans ma base, et là surprise la macro SemDate, fait un bug je vous joins la capture d'écran (certaines données confidentielles, d'où l'image).

Par contre quand je la test sur le fichier de Bebere redimensionné cela fonctionne très bien?

Pouvez-vous m'expliquer?

Merci d'avance.

Alex.

P.S: le code me génère 2 fois Semaine 35?!?

Edit: Au fait la même erreur ce produit sur le fichier de Bebere.

 

[Bebere]

Re : Générer un planning d'absence via macro

Alex
dans module3 tu as un code pour absent sans doublon
en lisant le code tu auras une réponse à une partie de tes questions
fonction countif équivalent à nb.si
cette ligne sort de la boucle l si>0(ListeRef(l, 1) présent dans la plage rng)
If Application.WorksheetFunction.CountIf(rng, "=" & ListeRef(l, 1)) > 0 Then Exit For
pour n° semaine il faut adapter le code
dans ton fichier commence en C dans le mien en B

 

[alex67800]

Re : Générer un planning d'absence via macro

Bonjour Bebere, le Forum,

La liste sans doublons correspond exactement à ce que je souhaitais, merci Bebere.

Toutefois la macro SemDate me fait toujours des misère.

Regardez le fichier joint avec la bonne mise ne page, cela me génère 2x "Semaine 35".

Pourtant je n'ai changéceci

 LigneDate = Range("planning!B4:DI4")

    ColDest = 113
    For C = 1 To UBound(LigneDate, 2)

        If Left(LigneDate(1, C), 2) = "Lu" Then
            Col = C + 1
            ColDest = ColDest + 2
        Else
            Col = 0
        End If

        If LigneDate(1, C) <> "" Then
            Mot = Mid(LigneDate(1, C), InStr(LigneDate(1, C), " ") + 1)
            pos = InStr(Mot, "/")
            Jour = Mid(Mot, 1, pos - 1)
            Mois = Mid(Mot, pos + 1)
            madate = DateSerial(Year(Date), Mois, Jour)
        End If

        If Col > 0 Then
            Worksheets("planning").Cells(3, Col) = NumSem(madate)
            Worksheets("planning").Cells(3, ColDest) = "Semaine " & NumSem(madate)
        End If

    Next C

Par cela

LigneDate = Range("planning!C11:DJ11") ' <<<<<<< Ici

    ColDest = 114 ' <<<<<<< Ici
    For C = 1 To UBound(LigneDate, 2)

        If Left(LigneDate(1, C), 2) = "Lu" Then
            Col = C + 1
            ColDest = ColDest + 2
        Else
            Col = 0
        End If

        If LigneDate(1, C) <> "" Then
            Mot = Mid(LigneDate(1, C), InStr(LigneDate(1, C), " ") + 1)
            pos = InStr(Mot, "/")
            Jour = Mid(Mot, 1, pos - 1)
            Mois = Mid(Mot, pos + 1)
            madate = DateSerial(Year(Date), Mois, Jour)
        End If

        If Col > 0 Then
            Worksheets("planning").Cells(4, Col + 13) = NumSem(madate) '<<<<< Ici
            Worksheets("planning").Cells(9, ColDest) = "Semaine " & NumSem(madate)
        End If

    Next C

Pourquoi du coup le 2 "Semaine 35"?

Merci d'avance.

Alex

Edit: J'ai supprimé le fichier, car présent dans le fil suivant.

 

[alex67800]

Re : Générer un planning d'absence via macro

Re,
Je vous mets la mise en page tel que pn fichier source dans le fichier PlanningAbsent1xls
Du coup les semaines ok, par contre la liste des absens ne fonctionne pas pour la semaine 38.

J'y perd mon latin.

 

[Bebere]

Re : Générer un planning d'absence via macro

bonjour Alex

listeref doit être initialisée comme suit

With Worksheets("Data")
l = .Range("B1000").End(xlUp).Row
ListeRef = .Range("B2:B" & l)
End With

 

[alex67800]

Re : Générer un planning d'absence via macro

Bonjour Bebere, le Forum,

J'ai modifié le code, pour la génération des semaines, tout est ok.

Sub EtabliAbsentUnic()
Dim Absent() As String 'déclare un tableau non dimensionné
Dim C As Integer, Col As Integer, Cdebut As Integer, Cfin As Integer, CDest As Integer
Dim i As Integer, j As Integer, l As Integer, li As Integer, MonDico As Object
Dim Cel As Range, ListeRef As Variant

Cdebut = 3: Cfin = 30
li = 11: CDest = 116
'ListeRef = Range("Data!B2:B31")

With Worksheets("Data")
 l = .Range("B1000").End(xlUp).Row
 ListeRef = .Range("B1:B" & l)
 End With
 
With Worksheets("Planning")
.Range("DK3:DQ100").ClearContents
Module2.SemDate 'appel procédure
For Col = Cdebut To 114 Step 30 'B à DA
  Set MonDico = CreateObject("Scripting.Dictionary")
Set rng = .Range(.Cells(5, Col), .Cells(5, Col + 29))
For C = Col To Col + Cfin Step 4 'boucle lu à ve
For l = 1 To UBound(ListeRef, 1)
If Application.WorksheetFunction.CountIf(rng, "=" & ListeRef(l, 1)) > 0 Then Exit For
Set Cel = .Columns(C).Find(ListeRef(l, 1), LookIn:=xlValues, lookat:=xlWhole)
If Cel Is Nothing Then
 If Not MonDico.Exists(ListeRef(l, 1)) Then MonDico.Add ListeRef(l, 1), ListeRef(l, 1) 'absent sans doublon
End If
Next l
Next C

For Each Item In MonDico.items
.Cells(li, CDest) = Item
li = li + 1
Next Item

li = 11
CDest = CDest + 2

Next Col
End With

End Sub

Par contre j'ai l'impression que la boucle ne se fait que sur les lundi.

Exemple: une personne absente le lundi, mais présente le reste de la semaine figure dans la liste des Absents.
Or je considère que si elle travaille 1 seul jour de la semaine, elle n'est pas absente.

Je pense qu tout vient de cette partie:

Cdebut = 3: Cfin = 30

et

For C = Col To Col + Cfin Step 4 'boucle lu à ve

Je souhaiterai que C boucle sur:
.Range("C12:AD" & wsbd.Range("A65536").End(xlUp).Row) pour la semaine 1
.Range("AE12:BF" & wsbd.Range("A65536").End(xlUp).Row) pour la semaine 2
.Range("BG12:CH" & wsbd.Range("A65536").End(xlUp).Row) pour la semaine 3
.Range("CI12: DJA" & wsbd.Range("A65536").End(xlUp).Row) pour la semaine 4.

Pourquoi Range("A65536").End(xlup).Row), tout simplement car la colonne A détermine le nombre de ligne de fabrication en fonctionnement dans la semaine.

Donc si je trouve ne serait-ce qu'une seule fois une personne dans la plage concernée, je ne la considère pas en absence.

Merci pour vos idées.

P.S: Visiblement nous devons être seul, car personne d'autre n'intervient dans le fil, j'en suis surpris, c'est dans la diversité que l'on partage, donc un grand merci encore Bebere.

Alex.

 

[Bebere]

Re : Générer un planning d'absence via macro

bonjour Alex
puisque une présence semaine est suffisante pas besoin de boucler
si countif=0 absent
pris en compte du lu au dim

un autre code

Sub EtabliAbsentUnic()
Dim Absent() As String 'déclare un tableau non dimensionné
Dim Cdebut As Integer, Cfin As Integer, CDest As Integer
Dim i As Integer, j As Integer, l As Integer, li As Integer
Dim ListeRef As Variant

Cdebut = 3: Cfin = 30
li = 11: CDest = 116

With Worksheets("Planning")
.Range("DL9:DR100").ClearContents
End With

With Worksheets("Data")
l = .Range("B1000").End(xlUp).Row
ListeRef = .Range("B1:B" & l)
End With

With Worksheets("Planning")
.Range("DL9:DR100").ClearContents
Module2.SemDate 'appel procédure
For tour = 1 To 4
If tour = 1 Then ecart = 0 Else ecart = 27
 'rng du lu au dim
Set rng = .Range(.Cells(5, Cdebut + ecart), .Cells(.Range("A65536").End(xlUp).Row, Cfin + ecart))
For l = 1 To UBound(ListeRef, 1)

If Application.WorksheetFunction.CountIf(rng, "=" & ListeRef(l, 1)) = 0 Then
ReDim Preserve Absent(i): Absent(i) = ListeRef(l, 1): i = i + 1 'absent suivant
End If
Next l

For j = 0 To UBound(Absent)
.Cells(li + j, CDest) = Absent(j)
Next j

Erase Absent: i = 0
CDest = CDest + 2

Next tour
End With

End Sub