XL 2016 VBA - Traitement de données avec valeurs identiques dans un dictionnaire

[Mak_tarmak]

Bonjour,
Je sollicite votre conseil car je n'arrive pas à trouver la bonne méthode pour obtenir ce que je veux.

Dans un fichier, j'ai deux onglets :
- ELT_SESSIONS qui contient des cellules avec des données fixes (en gris) et des données calculées en vba (en jaune).
On peut effacer à souhait les données en jaune, elles sont générées par les macros.

- audit_user qui contient les données d'un rapport qui peut faire plus de 50 000 lignes.

Ce fichier me sert à contrôler des présences de stagiaires par rapport à des heures saisies afin de voir si elles coincident.

Par exemple le code session 6380 devrait remonter 4, 4, 1 dans les colonnes D à K car le code utilisateur 3102465 apparait 2 fois dans audit_user.
Du coup, cela influera aussi sur la somme des heures en colonne O puisque l'on prendra 7 au lieu de 10,5 pour cet utilisateur.

La macro ctrl_presences est censée parcourir chaque id session de la colonne B de ELT_SESSIONS et vérifier s'il y a une occurrence dans la colonne I de audit_user.
Si l'id session est trouvé dans audit_user, elle doit vérifier dans la colonne A que le code utilisateur est unique. S'il y a plusieurs fois le même code, il ne faut traiter que la dernière ligne de ce code utilisateur (il peut y avoir 3 ou 4 fois le même code utilisateur mais il ne faut pas supprimer les autres lignes, juste ne pas en tenir compte dans le traitement).
Une fois que la liste des codes utilisateurs valides est dressée, je regarde la valeur du statut d'avancement dans N et je compte combien de fois il apparait selon une liste de statuts et je colle chaque compte dans les cellules D à K pour chaque id session.
Sur cette macro, mon compte est faux car je n'arrive pas à écarter les lignes précédentes quand il y a plusieurs codes utilisateurs identiques.


La macro CalculerPrevisionHeures me calcule la prévision d'heures si toutes les inscrits en colonne C de ELT_SESSIONS sont tous présents.


La macro CalculerHeuresParStatut me calcule le nombre d'heures que je dois avoir (colonne N) en fonction des statuts de présence (colonnes D à K) et du nombre de segments (colonne L).
Un segment est une demi-journée de 3,5 heures.
Si une personne est absente, cela retourne 0.
Si présente, cela retourne le nombre de présents * (nbre de segments * 3,5).
Si partiellement présent, cela retourne le nombre de partiellement présent * ((nbre de segments * 3.5) - 3.5))
Actuellement mon nombre d'heures retournées est faux car je n'arrive pas à écarter les lignes des codes utilisateurs en plus sur la macro précédente.


La macro ReelHeuresSaisies est censée retournée la somme des heures en colonne O de audit_user, une fois les codes utilisateurs identiques écartés en ne gardant que celui de la dernière ligne.



Je n'ai pas le droit de supprimer des lignes dans le rapport, c'est pour cela que je voulais vérifier les codes utilisateurs uniques et les stocker dans une collection ou un dictionnaire mais je ne maitrise pas cette fonctionnalité et je n'ai pas réussi à adapter les différentes macros que j'ai trouvé sur le sujet.

J'espère que vous pourrez m'orienter sur une méthode qui pourrait convenir car je ne sais plus par quel bout attaquer

Si j'ai oublié quelque chose, n'hésitez pas à revenir vers moi.
En vous remerciant,

 

[Mak_tarmak]

Donc il ne faudra pas reproduire les sessions de l'Audit qui ne figureront pas dans dans l'ELT ?

Non mais comme les sessions se déroulent régulièrement il y en a qui ne sont pas encore dans ELT mais qui le seront prochainement. On fait beaucoup de formations et le nombre ne fait que s'agrandir.

 

[Dranreb]

Bon, j'en suis là :

 

[Mak_tarmak]

Bon, j'en suis là :

Bonjour Dranreb, merci beaucoup, c'est très bien avancé.
J'ai rajouté un jeu de données complet de 400 lignes et ça marche nickel. La vitesse de traitement est incroyable.
Le seul souci qu'il reste, c'est qu'il y a un incrément de 1 sur les inscrits en colonne C quand les résultats sont copiés dans la feuille. J'ai passé le code en pas à pas mais je n'ai pas réussi à voir d'où cela venait.
J'ai passé mes autres macros qui permettent d'alimenter les colonnes M et N et c'est nickel.

 

[Dranreb]

Non, je ne prévois pas d'autre macro. Il faut juste m'expliquer ce qu'il faut mettre en M et N.

 

[Mak_tarmak]

Non, je ne prévois pas d'autre macro. Il faut juste m'expliquer ce qu'il faut mettre en M et N.

Je ne comprends pas quand vous dites : je ne prévois pas d'autre macro.
L'incrément de 1 sur les inscrits ne peut pas être corrigé ?
Pour M et N, je vous ai rajouté dans le module ElementsSessions, les deux macros que j'utilise pour les alimenter.

 

[Dranreb]

Vu et corrigé la cause du 1 de trop dans les Nbr inscrit(s)
Ajouté la colonne M.
Pour la colonne N j'ai appliqué une règle que j'avais trouvé quelque part.

 

[Mak_tarmak]

Vu et corrigé la cause du 1 de trop dans les Nbr inscrit(s)
Ajouté la colonne M.
Pour la colonne N j'ai appliqué une règle que j'avais trouvé quelque part.

Je vous joints un jeu de données complété car j'ai des erreurs.
Par exemple la session 5452 a bien ses 15 inscrits et 3 segments de 3,5H chacun.
En M, N, O on devrait avoir 157,5, 136,5 et 157,5 mais après nouveau traitement j'ai 157,5, 136,5 et 556,5, test fait avec mon jeu de données de 57 000 lignes.
Je retesté avec le jeu que je vous envoie ici et j'obtiens le bon nombre ! Pas compris !
De plus, grâce à vous, je me suis aperçu que certaines sessions (7089 pour le code stage ADISFON1947 avec 22 inscrits) sont présentes dans ELT car elles ont été crées dans notre base mais elles ne sont pas encore dans audit car elles ne se sont pas déroulées. Du coup, elles ressortent en cellule vide en O ou bien sort un nombre incohérent en O ici 1015 après votre traitement et le nombre d'inscrits est passé à 0.
Pour la session 7010, il n'y a pas d'inscrits mais seulement le chiffre 4 pour indiquer 4 demi-journées et ça sort quand 633 heures dans O dans un précédent traitement mais là la cellule est vide.
Après, que les cellules soient vides en O pour les sessions qui ne sont pas passées peut-être un moyen d'identifier les sessions qui vont se dérouler dans le futur.
Le nombre 2851 n'est pas une erreur. En fait, ce fichier va me servir à déterminer s'il y a eu des erreurs de saisies humaines et là on voit que c'est le cas car le nombre de segments maximum est de 10.
J'ai essayé de vous fournir le jeu de données complet mais il est trop volumineux. J'ai adapté avec des codes problématiques.
Je suis désolé je ne me suis pas aperçu de ces erreurs avant.

 

[Mak_tarmak]

Vu et corrigé la cause du 1 de trop dans les Nbr inscrit(s)
Ajouté la colonne M.
Pour la colonne N j'ai appliqué une règle que j'avais trouvé quelque part.

Bonjour Dranreb, j'ai essayé d'intégrer les modules et les modules de classe dans un autre classeur Excel pour pouvoir utiliser votre code mais ça n'a pas marché.
J'ai bien renommé les noms des feuilles dans les propriétés de Visual Basic en WshELT et WshAudit mais cela n'a pas marché, tous les statuts remontent à 0.
Je n'ai pas trouvé où vous identifiez les noms des entêtes de colonne de WshAudit pour le listobject.
Est-ce qu'il faut préparer le fichier d'une certaine manière pour utiliser votre code ?
Merci

 

[Dranreb]

Bonjour.
Je me souvient d'avoir dû corriger en majuscule la 1ère lettre des intitulés de colonnes du WshELT pour qu'ils correspondent bien aux textes présents en colonne 14 "Statut d’avancement2" du WshAudit. Peut être cela n'a-t-il pas été fait dans cet autre classeur ?
Ainsi l'instruction TR = WshELT.ListObjects(1).HeaderRowRange.Value récupèrera les bonnes valeurs.

 

[Mak_tarmak]

Bonjour.
Je me souvient d'avoir dû corriger en majuscule la 1ère lettre des intitulés de colonnes du WshELT pour qu'ils correspondent bien aux textes présents en colonne 14 "Statut d’avancement2" du WshAudit. Peut être cela n'a-t-il pas été fait dans cet autre classeur ?
Ainsi l'instruction TR = WshELT.ListObjects(1).HeaderRowRange.Value récupèrera les bonnes valeurs.

Merci. Effectivement je n'avais pas mis les majuscules dans WshELT. J'ai corrigé mais j'ai toujours l'erreur d'execution 9 : l'indice n'appartient pas à la sélection sur TR = WshELT.ListObjects(1).HeaderRowRange.Value.
Pourtant c'est comme dans votre fichier. Je ne vois pas d'où vient l'erreur.
Le TblELT se crée tout seul ?

 

[Dranreb]

Ah, oui, dans le classeur original l'ELT n'avait pas été mis sous forme de tableau non plus, et la collection ListObjects de l'objet Worksheet était donc vide et ne comportait par conséquent pas de premier et seul élément.

 

[Mak_tarmak]

Ah, oui, dans le classeur original l'ELT n'avait pas été mis sous forme de tableau non plus, et la collection ListObjects de l'objet Worksheet était donc vide et ne comportait par conséquent pas de premier et seul élément.

Bonjour Dranreb,
Un grand merci, ça marche beaucoup mieux maintenant !
J'avais encore une erreur de remontée d'Absent mais c'était parce qu'un espace s'était glissé dans une cellule.
Est-ce vous auriez la possibilité de commencer la macro CtrlPresences car je ne suis pas confort pour l'utiliser autre part ou l'étendre à d'autres colonnes de mon fichier.
Je ne sais pas à quoi correspond TR.
C et L c'est peut-être pour colonne et ligne.
Les différents chiffres de TabCols doit indiquer des colonnes mais je ne sais pas comment ça marche avec 1, 2, , 12 et 1, 2, 3
Je ne sais pas à quoi correspond Détail et DonnéesDébut.
En tout cas merci, passez un bel après-midi.

 

[Dranreb]

Bonjour.
TR est un tableau dynamique destiné au résultat.
Les chiffres spécifiés à TabCols sont les numéro de colonnes désirés de la source. Il faut en effet assurer un même colonage au deux tableaux pour que Gigogne traite convenablement le tableau fusionné obtenu.
Détail est un variant contenant un tableau à 1 dimension, chaque élément ayant même indice que la colonne du tableau traité, donc ici le fusionné. DonnéeDébut est la première ligne détail du sous groupe, c'est une propriété en lecture seule de l'objet SsGr.

J'ai remplacé par leurs intitulés les numéros de colonnes spécifiés aux TabCols, finalement ça marche s'is sont correctement écrits.

 

[Mak_tarmak]

Bonjour.
TR est un tableau dynamique destiné au résultat.
Les chiffres spécifiés à TabCols sont les numéro de colonnes désirés de la source. Il faut en effet assurer un même colonage au deux tableaux pour que Gigogne traite convenablement le tableau fusionné obtenu.
Détail est un variant contenant un tableau à 1 dimension, chaque élément ayant même indice que la colonne du tableau traité, donc ici le fusionné. DonnéeDébut est la première ligne détail du sous groupe, c'est une propriété en lecture seule de l'objet SsGr.

J'ai remplacé par leurs intitulés les numéros de colonnes spécifiés aux TabCols, finalement ça marche s'is sont correctement écrits.

Bonjour Dranreb, désolé je pensais vous avoir répondu. Un grand merci pour ces explications. Au plaisir.