Extraction d'une liste en fonction d'un critère de date (plan de formation)

[Anarhim]

Bonjour,
J'aurais besoin de votre aide pour réaliser un tableau "plan de formation". Je suis au quotidien un tableau avec les différentes formations du personnel de mon entreprise et je cherche a automatiser le plan de formation afin de visualiser facilement les personnes à former.
J'ai déjà mis en place des mises en formes conditionnelles pour avoir un rappel simple sur les gens concerné mais l'entreprise grandissante au fil des années, les mises en forme conditionnelles seules ne me permettent plus d'être aussi efficient qu'auparavant.
Du coup, dans mon tableau de suivi des formations, je voulais créer un onglet reprenant en colonnes les différentes formations effectués dans l'entreprise avec une formule ou macro qui listerait dans chacune de ces colonnes les noms des gens qui ont besoin d'être formé car leur formation est dépassé (date dépassé par rapport à la date du jour) ou qui ont besoin d'être formé car leur formation sera bientôt dépassé (dans les deux prochains mois, afin de voir venir et ne pas être tout le temps dans l'urgence).

En faisant des recherches, j'ai bien trouvé des choses qui s'en rapprocherait mais jamais rien gérant de multiples colonnes et jamais de dates, toujours des catégories (par exemple une extraction suivant la catégorie socio-pro. des gens d'un tableau). J'ai essayé d'adapter au mieux mes trouvailles mais impossible de faire ce que je souhaite.

Pensez-vous que cela est possible ?

Merci pour votre aide.

Je vous joint un tableur excel reprenant la forme globale de mon document avec ce que je souhaite.

 

[Dranreb]

Re : Extraction d'une liste en fonction d'un critère de date (plan de formation)

Bonjour.
Cette procédure calcule le tableau final :

Private Sub GarnirTbÀFormer(TR(), TE(), ByVal DateLim As Date)
Dim C&, LR&, LE&, ÀFormer As Boolean
For C = 2 To UBound(TE, 2)
   LR = 0
   For LE = 1 To UBound(TE, 1)
      If VarType(TE(LE, C)) = vbDate Then
         ÀFormer = TE(LE, C) < DateLim
      Else
         ÀFormer = Not IsEmpty(TE(LE, C)): End If
      If ÀFormer Then
         LR = LR + 1: TR(LR, C - 1) = TE(LE, 1): End If: Next LE, C
End Sub

Vérifié avec cette Function qui l'utilise :

Function ÀFormer(ByVal PLage As Range) As Variant()
Dim T(), L&, C&
ReDim ÀFormer(1 To Application.Caller.Rows.Count, 1 To Application.Caller.Columns.Count)
T = PLage.ListObject.DataBodyRange.Value
GarnirTbÀFormer ÀFormer, T, DateSerial(Year(Date), Month(Date) + 3, 1)
End Function

En M2:O20 validé par Ctrl+Maj+Entrée :

=ÀFormer('Suivi formations'!A2)

 

[Dranreb]

Re : Extraction d'une liste en fonction d'un critère de date (plan de formation)

Et pour une procédure qui met à jour le tableau en A2:C20 c'est :

Sub MàJÀFormer()
Dim TR(), TE()
With Feuil3.[A2].ListObject.DataBodyRange
   ReDim TR(1 To .Rows.Count, 1 To .Columns.Count)
   TE = Feuil2.[A2].ListObject.DataBodyRange.Value
   GarnirTbÀFormer TR, TE, DateSerial(Year(Date), Month(Date) + 3, 1)
   .Value = TR: End With
End Sub

 

[Anarhim]

Re : Extraction d'une liste en fonction d'un critère de date (plan de formation)

Ça fonctionne super bien.

Par contre c'est quoi l'histoire de la formule en M2:O20 ? Car j'ai mis GarnirTbÀFormer, ÀFormer et MàJÀFormer dans la feuille Visual Basic et tout fonctionne très bien. J'ai créé un bouton qui lance MàJÀFormer et quand je clique dessus, tout se met à jour parfaitement, donc quel est l'intérêt de la formule en M2:O20 ?

En revanche, si possible, pourriez-vous m'expliquer le pourquoi du comment de chaque ligne, que j'essais de comprendre la formule et pas juste l'utiliser bêtement ?

 

[Dranreb]

Re : Extraction d'une liste en fonction d'un critère de date (plan de formation)

C'était juste un test pour voir si ça donnait le bon résultat. Vous pouvez supprimer la formule et la Function ÀFormer.

Le problème c'est que je ne sais pas ce que vous ne comprenez pas alors je risquerait d'alourdir encore le code avec des commentaires qui ne serviraient à rien. Mettez en vous même là où ça vous paraît obscur, quitte à me demander une explication pour chaque truc qui échape à votre compréhention. Ce sera plus profitable. Sinon vous risquer de ne pas comprendre mes commentaires en plus de ne pas comprendre le code !

 

[Anarhim]

Re : Extraction d'une liste en fonction d'un critère de date (plan de formation)

Effectivement, vu comme ça, votre remarque est bien vue. Disons que je ne connais pas grand chose au VBA et que d'une manière générale j'ai beaucoup à apprendre. J'arrive généralement à procéder à de petites modifications dans des codes existants pour peu que ces derniers soient relativement simple mais dans ce cas ci, je ne comprend strictement rien...

J'ai transposé votre code dans mon classeur final et là il ne fonctionne plus... Evidemment les deux classeurs se ressemblent mais ne sont pas comparables donc il faut adapter certaines choses. Donc voici les erreurs et mon état d'avancement :
- Erreur d'éxécution 91 (Sub MajàFormer) :

TE = Feuil2.[A2].ListObject.DataBodyRange.Value

Effectivement, les noms du personnel ne commence pas en A2 mais en B4 sur mon tableur final, j'ai donc corrigé pour mettre :

TE = Feuil2.[B4].ListObject.DataBodyRange.Value

- Pas d'erreur détecté mais même principe, la feuille n'est pas la même

With Feuil3.[A2].ListObject.DataBodyRange

Sur mon tableur final la Feuil3 du tableur test correspond à ma Feuil4 et les données à écrire commencent en A3 j'ai donc corrigé en :

With Feuil4.[A3].ListObject.DataBodyRange

- Erreur 9 (Private Sub GarnirTbÀFormer) :

         LR = LR + 1: TR(LR, C - 1) = TE(LE, 1): End If: Next LE, C

Là je sèche complètement, je ne comprend pas la ligne en fait...

Je vous remet en pièce jointe le document mis à jour avec une forme qui se rapproche plus à la version finale.

Je ne sais pas si l'erreur peut venir de là étant donné que je n'arrive pas à comprendre votre code mais l'emplacement des colonnes d'une formation X dans le premier onglet ne correspond pas obligatoirement au même emplacement dans le second onglet car il y a des colonnes pour lesquelles j'ai besoin des dates de formation mais où il n'y a pas de recyclage nécessaire, du coup ces colonnes apparaissent dans le premier onglet mais pas dans le second.
En revanche, le nom des colonnes pour une même formation est systématiquement le même entre le premier et le second onglet donc il faudrait que la macro recherche la colonne portant le même nom dans le premier onglet avant de vérifier les dates présentent dans les lignes inférieures puis qu'elle renvoie le nom de la personnel concerné pour chaque date inférieure à celle d'aujourd'hui ou qui le sera dans les 2 prochains mois.

 

[Dranreb]

Re : Extraction d'une liste en fonction d'un critère de date (plan de formation)

J'ai commencé à mettre des commentaires sur la procédure principale :

Sub MàJÀFormer()
Dim TR(), TE() ' Déclare ces deux tableaux dynamiques.
With Feuil3.[A2].ListObject.DataBodyRange ' Avec la plage des données couvertes par l'objet tableau auquel appartient la cellule A2 de l'objet Worksheet Feuil3 (A former)
   ReDim TR(1 To .Rows.Count, 1 To .Columns.Count) ' Redimensionne le Tableau Résultant à son nombre de lignes et de colonne
   TE = Feuil2.[A2].ListObject.DataBodyRange.Value ' TE reçoit les valeurs de la plage des données couvertes par l'objet tableau auquel appartient la cellule A2 de l'objet Worksheet Feuil2 (Suivi formations)
   GarnirTbÀFormer TR, TE, DateSerial(Year(Date), Month(Date) + 3, 1) ' Appelle la procédure pour TR et d'après TE, pour des dates inférieures au 1er jour du 2nd mois suivant celui en cours.
   .Value = TR: End With ' Lui affecte les valeurs du tableau résultant et finit ce groupe avec la plage des données couvertes…
End Sub

La ligne que vous ne comprenez pas comporte 4 instructions séparées par des ":": Ajoute 1 au numéro de ligne courant du tableau résultant, verse le nom colonne 1 ligne d'entrée dans l'élément de cette ligne et de la colonne précédente et ternine le bloc If et les deux boucles. Peut être faut il revoir le dimensionnement maximum en lignes du tableau résultant. Est il inférieur à celui du tableau d'entrée ? Pareil pour les colonnes ?

 

[Dranreb]

Re : Extraction d'une liste en fonction d'un critère de date (plan de formation)

Provisoirement ceci évitera peut être le plantage, sans plus :

Sub MàJÀFormer()
Dim TR(), TE(), LRMax As Long ' Déclare ces deux tableaux dynamiques et un indice de lignes maxi effectif.
TE = Feuil2.[A2].ListObject.DataBodyRange.Value ' TE reçoit les valeurs de la plage des données couvertes par l'objet tableau auquel appartient la cellule A2 de l'objet Worksheet Feuil2 (Suivi formations)
ReDim TR(1 To UBound(TE, 1), 1 To UBound(TE, 2) - 1) ' Redimensionne le Tableau Résultant au nombre de lignes et de colonnes de la plage d'entrée cette fois.
GarnirTbÀFormer TR, TE, DateSerial(Year(Date), Month(Date) + 3, 1), LRMax ' Appelle la procédure pour TR et d'après TE, pour des dates inférieures au 1er jour du 2nd mois suivant celui en cours.
Feuil3.[A2].ListObject.DataBodyRange.Resize(LRMax).Value = TR ' Les valeurs du tableau résultant sont affectées la plage de données couvertes par l'objet tableau auquel appartient la cellule A2 de l'objet Worksheet Feuil3 (A former).
End Sub

Private Sub GarnirTbÀFormer(TR(), TE(), ByVal DateLim As Date, ByRef LRMax As Long)
Dim C As Long, LR As Long, LE As Long, ÀFormer As Boolean
LRMax = 0
For C = 2 To UBound(TE, 2)
   LR = 0
   For LE = 1 To UBound(TE, 1)
      If VarType(TE(LE, C)) = vbDate Then
         ÀFormer = TE(LE, C) < DateLim
      Else
         ÀFormer = Not IsEmpty(TE(LE, C)): End If
      If ÀFormer Then
         LR = LR + 1: If LRMax < LR Then LRMax = LR
         TR(LR, C - 1) = TE(LE, 1): End If: Next LE, C
End Sub

 

[Anarhim]

Re : Extraction d'une liste en fonction d'un critère de date (plan de formation)

Merci pour les commentaires, cela devient beaucoup plus clair même sans être un spécialiste.

Pour répondre à votre question, oui le tableau résultant est plus petit que le tableau d'origine. Le tableau d'origine possède plus de 70 colonnes et 50 lignes alors que le tableau résultant n'a que 40 colonnes, car toutes les colonnes liées aux formations n'ayant pas besoin d'être renouvelé n'y figure pas. C'est pourquoi je vous parlais dans mon dernier message de peut-être cherché à faire en sorte que la macro cherche la colonne correspondante avec le nom de la colonne.

 

[Dranreb]

Re : Extraction d'une liste en fonction d'un critère de date (plan de formation)

Comme ça c'est peut être un peu mieux :

Sub MàJÀFormer()
Dim TR(), TE(), LRMax As Long ' Déclare ces deux tableaux dynamiques et un indice de lignes maxi effectif.
TE = Feuil2.[A2].ListObject.DataBodyRange.Value ' TE reçoit les valeurs de la plage des données couvertes par l'objet tableau auquel appartient la cellule A2 de l'objet Worksheet Feuil2 (Suivi formations)
ReDim TR(1 To UBound(TE, 1), 1 To UBound(TE, 2) - 1) ' Redimensionne le Tableau Résultant au nombre de lignes et de colonnes de la plage d'entrée cette fois.
GarnirTbÀFormer TR, TE, DateSerial(Year(Date), Month(Date) + 3, 1), LRMax ' Appelle la procédure pour TR et d'après TE, pour des dates inférieures au 1er jour du 2nd mois suivant celui en cours.
With Feuil3.[A2].ListObject.DataBodyRange ' Avec la plage couverte par l'objet tableau auquel appartient la cellule A2 de l'objet Worksheet Feuil3 (A former).
   If .Rows.Count > LRMax Then .Rows(LRMax + 1).Resize(.Rows.Count - LRMax).Delete xlShiftUp ' Les lignes en trop sont supprimées.
   .Resize(LRMax).Value = TR ' Les valeurs du tableau résultant sont affectées à la plage de données qu'il couvre.
   End With
End Sub

La sub GarnirTbÀFormer ne change plus.

Ah. Je n'avais pas vu votre dernier message. Oui ça change pas mal de choses. Je vais regarder…
À +

 

[Dranreb]

Re : Extraction d'une liste en fonction d'un critère de date (plan de formation)

Voilà, essayez comme ça, chez moi avec toutes les mêmes colonnes dans le problème posé de façon incomplète, ça marche. Chez vous: à tester et mettre au point.

Sub MàJÀFormer()
Dim LOR As ListObject, LOE As ListObject, TR(), CR As Long, TE(), CE As Long, TCE(), L As Long, LRMax As Long
Set LOR = Feuil3.[A2].ListObject ' Tableau auquel appartient la cellule A2 de l'objet Worksheet Feuil3 (A former).
Set LOE = Feuil2.[A2].ListObject ' Tableau auquel appartient la cellule A2 de l'objet Worksheet Feuil2 (Suivi formations).
ReDim TE(1 To LOE.ListRows.Count, 1 To LOR.ListColumns.Count + 1)
TCE = LOE.ListColumns("PERSONNEL").DataBodyRange.Value
For L = 1 To UBound(TCE, 1): TE(L, 1) = TCE(L, 1): Next L
For CR = 2 To LOR.ListColumns.Count
   TCE = LOE.ListColumns(LOR.ListColumns(CR).Name).DataBodyRange.Value
   For L = 1 To UBound(TCE, 1): TE(L, CR) = TCE(L, 1): Next L, CR
TE = Feuil2.[A2].ListObject.DataBodyRange.Value ' TE reçoit les valeurs de la plage des données couvertes par l'objet tableau auquel appartient la cellule A2 de l'objet Worksheet Feuil2 (Suivi formations)
ReDim TR(1 To UBound(TE, 1), 1 To UBound(TE, 2) - 1) ' Redimensionne le Tableau Résultant au nombre de lignes et de colonnes de la plage d'entrée cette fois.
GarnirTbÀFormer TR, TE, DateSerial(Year(Date), Month(Date) + 3, 1), LRMax ' Appelle la procédure pour TR et d'après TE, pour des dates inférieures au 1er jour du 2nd mois suivant celui en cours.
With LOR.DataBodyRange
   If .Rows.Count > LRMax Then .Rows(LRMax + 1).Resize(.Rows.Count - LRMax).Delete xlShiftUp ' Les lignes en trop sont supprimées.
   .Resize(LRMax).Value = TR ' Les valeurs du tableau résultant sont affectées à la plage de données qu'il couvre.
   End With
End Sub

 

[Anarhim]

Re : Extraction d'une liste en fonction d'un critère de date (plan de formation)

Bonjour,
Désolé de vous embêtez encore mais... La macro ne plante plus, elle se déroule correctement, en revanche aucun noms n’apparaît dans le tableau final.

 

[Dranreb]

Re : Extraction d'une liste en fonction d'un critère de date (plan de formation)

Bonjour.
Mettez donc des noms mnémoniques comme FSuiviFo et FÀFormer aux objets Worksheet de la rubrique Microsoft Excel Objets. Feuil2 et Feuil3 ça ne veut rien dire. Et si la première cellule est à un autre endroit que A2 changez donc ça en conséquence !
Ou alors basez vous sur autre chose. Débrouillez vous un peu pour adapter bon sang !
Bon là il n'y a plus qu'un seul ListObject par feuille alors je vais prendre le premier et seul de sa collection ListObjects.
S'il devait à nouveau y en avoir plusieurs donner leurs de noms significatifs, là aussi.
Ah et puis ça n'allait pas du tout. J'avais oublié d'enlever l'ancienne instruction qui prenait TE = etc.
Et il y avait des erreurs de colonnes.
Bon je récris tout complètement différemment…

Voilà, je suis là :

Option Explicit

Sub MàJÀFormer()
Dim LOR As ListObject, LOE As ListObject, TNoms(), TR(), DateLim As Date, C As Long, _
    TE(), LR As Long, LE As Long, ÀFormer As Boolean, LRMax As Long
Set LOR = FÀFormer.ListObjects(1)
Set LOE = FSuiviFo.ListObjects(1)
TNoms = LOE.ListColumns("PERSONNEL").DataBodyRange.Value
ReDim TR(1 To LOE.ListRows.Count, 1 To LOR.ListColumns.Count)
DateLim = DateSerial(Year(Date), Month(Date) + 3, 1)
For C = 1 To LOR.ListColumns.Count
   TE = LOE.ListColumns(LOR.ListColumns(C).Name).DataBodyRange.Value
   LR = 0
   For LE = 1 To UBound(TE, 1)
      If VarType(TE(LE, 1)) = vbDate Then
         ÀFormer = TE(LE, 1) < DateLim
      Else
         ÀFormer = Not IsEmpty(TE(LE, 1)): End If
      If ÀFormer Then
         LR = LR + 1: If LRMax < LR Then LRMax = LR
         TR(LR, C) = TNoms(LE, 1): End If: Next LE, C
With LOR.DataBodyRange
   If .Rows.Count > LRMax Then .Rows(LRMax + 1).Resize(.Rows.Count - LRMax).Delete xlShiftUp
   .Resize(LRMax).Value = TR
   End With
End Sub

 

[Anarhim]

Re : Extraction d'une liste en fonction d'un critère de date (plan de formation)

Ça fonctionne. Parfaitement !

Je suis désolé de vous avoir énervé. Je ne peux malheureusement pas adapter à mon cas aussi facilement que vous écrivez vos macro car mes connaissances en VBA sont très limitées. Je sais généralement utiliser ce que les gens ont créés et faire des petites adaptations mais là ce que vous avez écrit me dépasse de très loin...
La preuve, je ne savais même pas que l'on pouvait changer les termes Feuil par des termes personnalisés...

Je vous remercie beaucoup pour ce que vous avez fait, la macro s’exécute parfaitement et la tableau se remplit comme il se doit.

Encore merci

 

[Dranreb]

Re : Extraction d'une liste en fonction d'un critère de date (plan de formation)

Ce n'est pas grave. Vous ne m'avez vraiment pas beaucoup énervé. Mais profitez peut être des discussions pour affiner vos connaissances en posant des questions. Il ne faut pas que ce que j'ai écrit vous dépasse sinon vous pourriez avoir du mal à le maintenir.