Recherche de données sur une période pouvant s'étaler sur plusieurs onglets

Averell1976

XLDnaute Junior
Bonjour à toutes et à tous,

Toujours un souci de RH; j'avais posté un sujet sur ce thème, mais je retente ma chance aujourd'hui. J'espérai trouver la solution tout seul en cherchant sur le net, mais mes recherches se sont avérées infructueuses.
La problématique: j'ai un fichier excel avec un onglet MATRICE2014, des onglets mensuels (un par mois qui se crée automatiquement à partir de MATRICE2014) et un onglet DIMANCHE; c'est celui- là qui m'intéresse: en effet, quand je cliques sur le bouton de commande de cet ONGLET, je voudrai, en renseignant l'userform qui s'affiche, récupérer le nom des salariés/ remplaçant éventuel et horaires qui ont travaillé le dimanche sur la période indiquée: si j'indique en date de début de période 01/01/2015 et en date de fin de période 29/04/2015, il faudrait donc que mon code me permette de parcourir les onglets JANVIER, FEVRIER, MARS et AVRIL, me récupère toutes les dates correspondant à un dimanche et donc le nom de salariés qui ont travaillés sur ces dates...

Là ça ne marche que pour l'onglet MATRICE2014.... Comment faire pour parcourir plusieurs onglets?
Je vous met le code que j'ai actuellement ainsi que le fichier en PJ. Merci à vous pour votre aide...
Code:
Sub Relevé()
Dim cel As Range, i As Integer, derLigne As Integer, premLigne As Integer
Dim ligneEcri As Integer, Salarie As String, Remplaçant As String, Horaire_travail As String, colEcri As Integer
'je nettoie le contenu de la feuille "DIMANCHE"
Sheets("DIMANCHE").Range("B5:G78").ClearContents
 
Sheets("MATRICE2014").Activate
'Lecture du tableau de la feuille Matrice 2014
premLigne = 11: ligneEcri = 5
derLigne = Range("B" & Rows.Count).End(xlUp).Row
colEcri = 1
'Pour empêcher le clignotement pendant le fonctionnement
Application.ScreenUpdating = False
'Boucle pour parcourir une ligne sur deux en lecture
Do Until premLigne >= derLigne
For Each cel In Range(Cells(premLigne, 2), Cells(premLigne, 33))
    'Pour vérifier si on est un dimanche
    If Cells(7, cel.Column) = "" Then GoTo Suite
    If Weekday(Cells(7, cel.Column)) = 1 Then
    'Colonne pour écrire
    colEcri = colEcri + 1
    'lecture des données à écrire
        Salarie = Cells(cel.Row, 2)
        Remplaçant = cel.Offset(1, 0)
        Horaire_travail = cel.Offset(0, 0)
        DateJour = CDate(Cells(7, cel.Column))
        'Voir si travail il y a
        If cel <> "R" And Cells(cel.Row, 2) <> "REMPLACANT" Then
        'On ouvre la feuille pour écrire
        Sheets("DIMANCHE").Activate
                    If Cells(4, colEcri) = DateJour Then
                Cells(ligneEcri, colEcri) = Salarie
                Cells(ligneEcri + 1, colEcri) = Remplaçant
                Cells(ligneEcri + 2, colEcri) = Horaire_travail '******AJOUT
               
                 Sheets("DIMANCHE").Cells(ligneEcri, colEcri).Interior.Color = cel.Offset(0, 0).Interior.Color
               
               
            End If
        End If
    End If
 

Pièces jointes

  • Copie de MATRICE PG.xlsm
    274.5 KB · Affichages: 38

ROGER2327

XLDnaute Barbatruc
Re : Recherche de données sur une période pouvant s'étaler sur plusieurs onglets

Bonjour Averell1976.


Un essai en pièce jointe. Voyez si ça va dans le bon sens. Si oui, il restera encore du boulot pour décrasser ce code !

Au passage, très smart l'idée de savonner la planche en nommant AVRIL l'onglet contenant les données de mai. Ça fait gagner beaucoup de temps lors des essais. Merci et bravo !


Bonne soirée.


ℝOGER2327
#8278


Samedi 7 Gidouille 143 (Saint Bébé Toutout, évangéliste - fête Suprême Quarte)
3 Messidor An CCXXIV, 7,1684h - oignon
2016-W25-2T17:12:15Z
 

Pièces jointes

  • Copie de Copie de MATRICE PG.xlsm
    277.5 KB · Affichages: 32

Averell1976

XLDnaute Junior
Re : Recherche de données sur une période pouvant s'étaler sur plusieurs onglets

Bonsoir ROGER2327

Je regarde cela demain mais je crois comprendre ton code. Merci beaucoup pour ton aide.
Je suis désolé pour le savonnage bien involontaire de ma part..... J'ai tellement fait de tests différents que je me suis emmêlé les pinceaux.

Je te remercie encore du temps que tu as bien voulu consacrer à ma problématique.
 

Averell1976

XLDnaute Junior
Re : Recherche de données sur une période pouvant s'étaler sur plusieurs onglets

Sur le même principe, je souhaiterai récupérer les données (nom, horaire et éventuellement remplaçant) des personnes ayant travaillés les jours fériés.
Il faudrait donc, en cliquant sur le bouton de l'onglet "JOURS FERIES", comparer les dates de l'onglet JOURS FERIES (de B4 à L4) à celles contenues dans les différents onglets (Mois); si c'est la même date, je récupères les données....
J'ai le code qui fonctionne juste pour l'onglet MATRICE2014; mais je bloques pour la recherche multi- onglets.....

Code:
Sub Relevé_Ferie()
'je nettoie le contenu de la feuille
Sheets("JOURS FERIES").Range("B5:L78").ClearContents
Dim cel As Range, i As Integer, derLigne As Integer, premLigne As Integer, colLect As Integer
Dim ligneEcri As Integer, Salarie As String, Remplaçant As String, Horaire As String, colEcri As Integer
Dim JF() As Date, NB_JF As Integer, Flag As Boolean, j As Integer, JF_M(5) As Date, CE(5) As Integer
'Pour empêcher le clignotement pendant le fonctionnement
Application.ScreenUpdating = False
'Relevé du mois affiché dans la feuille MATRICE...
Sheets("MATRICE2014").Activate
Mois = Month(Cells(7, 3))
j = 0: k = 1
'Table des jours fériés
Sheets("JOURS FERIES").Activate
NB_JF = Range("B4:B" & Columns.Count).End(xlToRight).Column - 1
ReDim JF(NB_JF)
'Trouver le/les jours fériés du mois affiché
For i = 1 To NB_JF
  JF(i) = Cells(4, i + 1)
  If Month(JF(i)) = Mois Then
    j = j + 1
    CE(j) = i + 1
    JF_M(j) = JF(i)
  End If
Next i
nbJF_M = j
If nbJF_M = 0 Then Exit Sub

j = 1
Retour:
Sheets("MATRICE2014").Activate
'Lecture du tableau de la feuille Matrice 2014
premLigne = 11: ligneEcri = 5
derLigne = Range("B" & Rows.Count).End(xlUp).Row
Flag = False: i = 0
For Each cel In Range(Cells(premLigne, 2), Cells(premLigne, 33))
    'Pour vérifier si on est un jour férié
    
  If Cells(7, cel.Column) = JF_M(j) Then
    colLect = cel.Column
    For i = premLigne To derLigne Step 2
    'lecture des données à écrire dans la colonne du jour férié
        Salarie = Cells(i, 2)
        Remplaçant = Cells(i, colLect).Offset(1, 0)
        Horaire = Cells(i, colLect).Offset(0, 0)
       
        DateJour = CDate(Cells(7, cel.Column))
        'Voir si travail  ou jour férié il y a
   If Cells(i, colLect) <> ("R") And Cells(i, colLect) <> ("FERIE") And Cells(i, colLect) <> ("Férié") And Cells(i, colLect) <> ("férié") And Cells(i, colLect) <> ("ferie") And Cells(i, colLect) <> ("F") And Cells(i, 2) <> "REMPLACANT" Then
       ' If Cells(i, colLect) <> "R" And Cells(i, 2) <> "REMPLACANT" Then
        'On ouvre la feuille pour écrire
        Sheets("JOURS FERIES").Activate
        'On vide la colonne où on va écrire
            If Flag = 0 Then
                Range(Cells(5, CE(j)), Cells(derLigne, CE(j))).ClearContents
                Flag = True
            End If
            If Cells(4, CE(j)) = DateJour Then
                Cells(ligneEcri, CE(j)) = Salarie
                Cells(ligneEcri + 1, CE(j)) = Remplaçant
                Cells(ligneEcri + 2, CE(j)) = Horaire
            End If
        End If
    
        ligneEcri = ligneEcri + 3
        Sheets("MATRICE2014").Activate
    Next i
 End If
    'End If
    'On retourne dans la feuille pour lire les données suivantes
    'Sheets("MATRICE 2014").Activate
Next
'k pour compter le nombre de jour déjà inscrits quand il y plusieurs jours le même mois
k = k + 1
If nbJF_M >= k Then
     j = j + 1
    GoTo Retour
End If
Application.ScreenUpdating = True
Sheets("JOURS FERIES").Activate
End Sub

Je suis à la ramasse... Comment adapter ce code?
Merci à vous pour votre aide éventuelle
Et merci à ROGER2327 pour son aide précieuse.
 

Averell1976

XLDnaute Junior
Re : Recherche de données sur une période pouvant s'étaler sur plusieurs onglets

Bonjour jeanp.
Oups, désolé, je me rends compte qu'en effet, ça n'est pas clair du tout..
Je mets un fichier en PJ.
Je m'intéresse donc à l'onglet JOURS FERIES: Je souhaites en cliquant sur le bouton de cet onglet transférer des onglets JUILLET et AOUT (là je n'ai que 2 mois, mais je peux aussi bien en avoir 1 seul que 12) le nom des salariés/ horaires et éventuellement remplaçants s'ils ont travaillé un jour férié.

Ce serait en fait le même principe que la récupération des jours travaillés le dimanche (Cf onglet DIMANCHE, tester le bouton; ça marche nikel grâce à l'aide de ROGER2327) mais pour les jours fériés.....

Merci! En espérant avoir été plus clair
 

Pièces jointes

  • PG.xlsm
    246 KB · Affichages: 28

Averell1976

XLDnaute Junior
Re : Recherche de données sur une période pouvant s'étaler sur plusieurs onglets

Bonjour,

Bon j'ai essayé de retravailler le tout.
J'ai donc un fichier avec:
- des onglets contenant des horaires de travail pour des salariés par jour (un onglet par mois)
- Un onglet "DIMANCHE" avec un bouton appelant un userform; en y indiquant une date de début de période et une date de fin de période, il me récupère (dans cet onglet DIMANCHE) le nom des salariés, leurs horaires et éventuellement leur remplaçant ayant travaillés durant tous les dimanche qu'il y a eu entre la date de début et la date de fin indquées dans l'userform
- Un onglet "JOURS FERIES"; je voudrais faire la même chose que pour l'onglet DIMANCHE mais pour les jours fériés.

J'ai le code ci- dessous mais j'ai bug sur bug....

Code:
Sub Relevé_fériés(dd#, df#)
Dim cel As Range, i As Integer, derLigne As Integer, premLigne As Integer
Dim ligneEcri As Integer, Salarie As String, Remplaçant As String, Horaire_travail As String ', colEcri As Integer

Dim DateJour As Date, nfc$, fc As Worksheet, d As New Scripting.Dictionary


Dim colLect As Integer
Dim JF() As Date, NB_JF As Integer, Flag As Boolean, j As Integer, JF_M(5) As Date, CE(5) As Integer


'je nettoie le contenu de la feuille "JOURS FERIES"
Sheets("JOURS FERIES").Range("B5:L115").ClearContents

premLigne = 11: ligneEcri = 5 '%%%
'Pour empêcher le clignotement pendant le fonctionnement
Application.ScreenUpdating = False

For Each fc In Worksheets
  nfc = LCase(fc.Name)
  For i = 11 To 0 Step -1
    If nfc = Array("janvier", "février", "mars", "avril", "mai", "juin", "juillet", "août", "septembre", "octobre", "novembre", "décembre")(i) Then Exit For
  Next
  If i >= 0 Then
    If fc.Cells(7, 3).Value2 <= df And CDbl(DateSerial(Year(fc.Cells(7, 3).Value2), Month(fc.Cells(7, 3).Value2) + 1, 1)) > dd Then
premLigne = 11: ligneEcri = 5
      With fc
      

'Lecture du tableau de la feuille fc
        derLigne = .Range("B" & .Rows.Count).End(xlUp).Row
'Boucle pour parcourir une ligne sur deux en lecture
        Do Until premLigne >= derLigne
          For Each cel In .Range(.Cells(premLigne, 2), .Cells(premLigne, 33))
          
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Table des jours fériés
Sheets("JOURS FERIES").Activate
NB_JF = Range("B4:B" & Columns.Count).End(xlToRight).Column - 1
ReDim JF(NB_JF)

'Trouver le/les jours fériés du mois affiché
For i = 1 To NB_JF
  JF(i) = Cells(4, i + 1)
  If Month(JF(i)) = Mois Then
    j = j + 1
    CE(j) = i + 1
    JF_M(j) = JF(i)
  End If
Next i
nbJF_M = j
If nbJF_M = 0 Then Exit Sub

'j = 1
'Retour:

'Pour vérifier si on est un jour férié
If Cells(7, cel.Column) = JF_M(j) Then
colLect = cel.Column
For i = premLigne To derLigne Step 2
  
'lecture des données à écrire
Salarie = .Cells(cel.Row, 2)
Remplaçant = cel.Offset(1, 0)
Horaire_travail = cel.Offset(0, 0)
DateJour = CDate(.Cells(7, cel.Column))
If Not d.Exists(DateJour) Then
d.Add DateJour, d.Count + 2
End If

'Voir si travail il y a
If cel <> "R" And .Cells(cel.Row, 2) <> "REMPLACANT" Then
With Sheets("JOURS FERIES")
.Cells(4, d(DateJour)) = DateJour
.Cells(ligneEcri, d(DateJour)) = Salarie
.Cells(ligneEcri + 1, d(DateJour)) = Remplaçant
.Cells(ligneEcri + 2, d(DateJour)) = Horaire_travail '******AJOUT
.Cells(ligneEcri, d(DateJour)).Interior.Color = cel.Offset(0, 0).Interior.Color
End With
End If
'End If
              'End If
            'End If
          Next
'Incrémentation des lignes de lecture et d'écriture
          ligneEcri = ligneEcri + 3 '*****MODIF
          premLigne = premLigne + 2
        Loop
      End With
    End If
  End If
Next
  Application.ScreenUpdating = True
End Sub

J'ai sûrement plusieurs erreurs.... mais je n'y arrive pas.

Merci pour votre aide!

Averell
 

ROGER2327

XLDnaute Barbatruc
Re : Recherche de données sur une période pouvant s'étaler sur plusieurs onglets

Re...

Bonjour,

Bon j'ai essayé de retravailler le tout.
(...)
Moi aussi :​
Code:
Sub Relevé()

Dim cel As Range, i As Integer, derLigne As Integer, premLigne As Integer
Dim ligneEcri As Integer, Salarie As String, Remplaçant As String, Horaire_travail As String
Dim DateJour As Date, nfc$, fc As Worksheet, p As Range, d()

  'p = Plage des dates fériées.
  With Sheets("JOURS FERIES").Range("B4"): Set p = .Parent.Range(.Cells, .Parent.Cells(.Row, .Parent.Columns.Count).End(xlToLeft)): End With
  'Effacement du contenu de la feuille "JOURS FERIES".
  p.Offset(1).Resize(111).ClearContents
  'Chargement des dates fériées dans un tableau d.
  d = p.Value
  premLigne = 11: ligneEcri = 5
  'Pour empêcher le clignotement pendant le fonctionnement.
  Application.ScreenUpdating = False
  For Each fc In Worksheets
    If fc.Visible = xlSheetVisible Then '...ce qui exclut les feuilles cachées
      nfc = LCase(fc.Name) '= nom d'onglet de la feuille fc.
      For i = 11 To 0 Step -1
        If nfc = Array("janvier", "février", "mars", "avril", "mai", "juin", "juillet", "août", "septembre", "octobre", "novembre", "décembre")(i) Then Exit For
      Next
      If i >= 0 Then ' si le nom d'onglet de la feuille fc est un nom de mois...
        premLigne = 11: ligneEcri = 5
        With fc 'Lecture du tableau de la feuille fc.
          derLigne = .Range("B" & .Rows.Count).End(xlUp).Row
          Do Until premLigne >= derLigne 'Boucle pour parcourir une ligne sur deux en lecture.
            For Each cel In .Range(.Cells(premLigne, 2), .Cells(premLigne, 33))
              If cel <> "R" And .Cells(cel.Row, 2) <> "REMPLACANT" Then 'Si travail il y a...
                On Error GoTo ErreurDate
                DateJour = CDate(.Cells(7, cel.Column))
                On Error GoTo 0
                For i = UBound(d, 2) To 1 Step -1
                  If DateJour = d(1, i) Then Exit For
                Next
                If i > 0 Then 'Si DateJour est une date fériée...
  '...lecture des données à écrire :
                  Salarie = .Cells(cel.Row, 2)
                  Remplaçant = cel.Offset(1, 0)
                  Horaire_travail = cel.Offset(0, 0)
  '...écriture des données relevées :
                  With Sheets("JOURS FERIES")
                    .Cells(ligneEcri, i + 1) = Salarie
                    .Cells(ligneEcri + 1, i + 1) = Remplaçant
                    .Cells(ligneEcri + 2, i + 1) = Horaire_travail
                    .Cells(ligneEcri, i + 1).Interior.Color = cel.Offset(0, 0).Interior.Color
                  End With
                End If
E1:           End If
            Next
  'Incrémentation des lignes de lecture et d'écriture
            premLigne = premLigne + 2: ligneEcri = ligneEcri + 3
          Loop
        End With
      End If
    End If
  Next
  Application.ScreenUpdating = True
Exit Sub
ErreurDate: 'Survient pour les mois creux.
Resume E1
End Sub
J'en ai profité pour remplacer le module CLEAN_DIMANCHE par un module CLEAN contenant :​
Code:
Sub CLEAN(p As Range, c&)
Dim i&, q As Range
    Set q = p
    For i = 3 To 3 * c Step 3: Set q = Union(q, p.Offset(i)): Next
    With q.Interior: .Pattern = xlNone: .TintAndShade = 0: .PatternTintAndShade = 0: End With
End Sub
On peut ainsi utiliser la procédure CLEAN pour le relevé des dimanches aussi bien que pour le relevé des jours fériés.
J'ai modifié en conséquence le code de Userform6 et celui de Feuil13.

Voyez si ça va comme il faut.​


Maintenant, en route pour la manif ! Bonne journée.


ℝOGER2327
#8281


Lundi 9 Gidouille 143 (Sainte Outre, psychiatre - fête Suprême Quarte)
5 Messidor An CCXXIV, 3,7113h - mulet
2016-W25-4T08:54:26Z
 

Pièces jointes

  • Copie de PG.zip
    288.1 KB · Affichages: 35

Averell1976

XLDnaute Junior
Re : Recherche de données sur une période pouvant s'étaler sur plusieurs onglets

Bonjour ROGER2327,

Je continues de bosser sur ce fichier RH et je me rends compte que ça n'est pas une bonne idée de séparer mes recherches de jours travaillés. En gardant le principe de recherche des jours travaillés le Dimanche (useform qui s'ouvre pour sélectionner une période de recherche), je souhaiterai intégrer également les jours fériés à cette recherche pour en fait avoir une recherche "travail les dimanches et jours fériés".
Dans l'onglet "Données", j'ai donc rajouter les jours fériés de l'année 2016 et 2017 que j'ai défini comme plage de données intitulée "JOURSFERIES".
Donc en gros, le code recherche si le jour travaillé est un dimanche:
Code:
  'Pour vérifier si on est un dimanche
            If .Cells(7, cel.Column) <> "" Then
              If dd <= CDbl(.Cells(7, cel.Column)) And CDbl(.Cells(7, cel.Column)) <= df Then
                If Weekday(.Cells(7, cel.Column)) = 1 Then

Il faudrait également vérifier si le jour travaillé appartient à la plage de données JOURSFERIES.
Je penses que le principe est bon, mais comment le traduire en vba? avec and? avec Or?

Je te remercie en tout cas une fois de plus de toute l'aide que tu m'as apportée.

Averell
 

ROGER2327

XLDnaute Barbatruc
Re : Recherche de données sur une période pouvant s'étaler sur plusieurs onglets

Re...


Un essai en pièce jointe.
Mais il serait préférable d'établir le cahier des charges AVANT de commencer à tartiner du code...
On gagnerait beaucoup de temps (ce qui ne devrait pas laisser indifférent du côté des R.H./presse-citron) et le résultat serait certainement plus propre.


Bonne soirée.


ℝOGER2327
#8286


Dimanche 15 Gidouille 143 (Sainte Giborgne, vénérable - fête Suprême Tierce)
11 Messidor An CCXXIV, 6,5002h - coriandre
2016-W26-3T15:36:01Z
 

Pièces jointes

  • Copie de Copie de PG.zip
    311.9 KB · Affichages: 32

Averell1976

XLDnaute Junior
Re : Recherche de données sur une période pouvant s'étaler sur plusieurs onglets

Il y a des tas de choses qui seraient préférables très cher.... comme confier des boulots à des personnes compétentes: je suis loin d'être aussi bon que toi mais notre RH tape des courriers sur EXCEL et fait des tableaux sur WORD ou POWERPOINT (plus facile selon elle). Vaste débat
Je l'ai vu travailler là dessus et j'ai eu un peu pitié de la voir galérer... malheureusement je ne connaissais pas bien sa demande qu'elle n'a pas su me formuler clairement. Je savais juste que l'informatique permettrait de le faire, mais je ne maîtrise pas bien VBA
En tout cas sur ce truc, je crois qu'on est arrivé au bout et je te remercie sincèrement pour ton aide précieuse. Bon la Rh va peut- être me demander de récupérer aussi les jours travaillés le samedi, mais avec l'exemple du dimanche, je serai faire...

Donc un grand merci à toi pour ton aide et tes remarques perspicaces.
Les manifs ne sont certainement pas finies alors bonne continuation!!!!

Averell
 

ROGER2327

XLDnaute Barbatruc
Re : Recherche de données sur une période pouvant s'étaler sur plusieurs onglets

Re...


Il y a des tas de choses qui seraient préférables très cher.... comme confier des boulots à des personnes compétentes: je suis loin d'être aussi bon que toi mais notre RH tape des courriers sur EXCEL et fait des tableaux sur WORD ou POWERPOINT (plus facile selon elle). Vaste débat
(...)
Oh oui ! J'ai vécu cela... mais je ne développe pas : l'essentiel est ici.


(...) Bon la Rh va peut- être me demander de récupérer aussi les jours travaillés le samedi, mais avec l'exemple du dimanche, je serai faire...
(...)
J'espère bien ! Sinon, c'est bien connu, "ce pays va dans le mur", comme ils disent.​


(...)
Donc un grand merci à toi pour ton aide et tes remarques perspicaces.
(...)
Si ça marche, tant mieux. Mais le code que je propose n'est probablement pas un modèle.​



(...)
Les manifs ne sont certainement pas finies alors bonne continuation!!!!

Averell
Merci, on va tenter de continuer comme il faut.​


Cordialement.


ℝOGER2327
#8287


Dimanche 15 Gidouille 143 (Sainte Giborgne, vénérable - fête Suprême Tierce)
11 Messidor An CCXXIV, 7,3459h - coriandre
2016-W26-3T17:37:49Z
 

Discussions similaires

Statistiques des forums

Discussions
311 740
Messages
2 082 041
Membres
101 879
dernier inscrit
Arthur M