Rechercher plusieurs éléments communs sur deux onglets
Salut Nextia,
Salut gmb,
correction : je me suis rendu compte d'un problème lors des tris de 'BDD' et 'Absence salariés'.
Je souligne à nouveau que ce code n'est valable que pour des encodages au coup par coup et non pour une lecture globale des fichiers.
A+
Bonjour Curilis,
Merci bcp pour ton retour.
Je me permets de te poser la question suivante : Comment se lance ta macro? Celle de GMB possède un icône pour pouvoir la lancer, la tienne non. J'ai beau lui donner un nom, elle ne se lance pas, par ailleurs, je note que tu indiques "ThisWorkbook" --> quelle différence avec une création de macro (je vais apprendre comme ça )
Par ailleurs, "Je souligne à nouveau que ce code n'est valable que pour des encodages au coup par coup et non pour une lecture globale des fichiers." peux tu être plus précis stp. Cela signifie t'il que la macro n'est pas capable de générer le résultat d'un coup en lançant le programme? ou... je suis à l'ouest...
Merci de ton indulgence et de ta compréhension.
Salut Nextia,
un code placé dans le module 'Thisworkbook' (vas-y, double-clic sur 'ThisWorkbook') est "compris" par TOUTES les feuilles du fichier.
Adroitement utilisé, cela permet de n'écrire qu'UNE fois une procédure pour une action identique dans toutes les feuilles.
Tu peux évidemment choisir les feuilles qui interagiront avec ces procédures.
Dans ce cas, j'intercepte un changement quelconque dans 'BDD' ou 'Absence salariés' et j'en fais UNE action selon les critères demandés.
Effectivement, ce code ne fonctionne que pour UN changement dans une cellule quelconque.
En fonction de la feuille, il vérifie quelle cellule est cliquée puis si l'ensemble des données nécessaires au calcul sont présentes.
Ci-dessous, dans 'BDD', le code vérifie la présence d'un matricule en [C:C] (je devrais même ajouter un contrôle du type de données encodé - string ou number) et si une date est encodée en [O:O]. Il donne son accord (iOK = 1), note la ligne (lgRow) et le matricule concerné (lgMat) avant de lancer le tri et le calcul.
Select Case Sh.Name
Case "BDD"
If Not Intersect(Target, Sh.Columns("O")) Is Nothing Then _
If Sh.Range("C" & lgRow).Value <> "" And IsDate(Sh.Range("O" & lgRow).Value) Then _
iOK = 1: _
lgMat = Range("C" & lgRow).ValueLaisse cette procédure là où elle est, n'essaie pas de la renommer ou de la déplacer ou d'inventer un raccourci pour la faire démarrer : un simple changement de valeur dans une cellule de 'BDD' ou 'Absence salariés' EST le signal qu'elle attend pour démarrer automatiquement !
Étant distrait par les nombreuses et magnifiques sirènes du forum, je n'ai pas encore développé le code permettant une MÀJ générale de tes données en [AF].
A+
Salut Nextia,
Salut gmb,
voilà ton fichier :
- un double-clic sur 'BDD' [AF1] démarre la macro de calcul global de tes données ;
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
'
Dim sWkBDD As Worksheet, sWkABS As Worksheet
Dim tBDD, tABS, lgRow&, lgRowT&, lgMat&, lgMatT&
'
Set sWkBDD = Worksheets("BDD")
Set sWkABS = Worksheets("Absence salariés")
Application.EnableEvents = False
Application.ScreenUpdating = False
'
If Not Intersect(Target, sWkBDD.[AF1]) Is Nothing Then
Cancel = True
Call Tri(2)
lgRow = 1
tBDD = sWkBDD.Range("A2:AF" & sWkBDD.Range("C" & Rows.Count).End(xlUp).Row).Value
tABS = sWkABS.Range("A2:O" & sWkABS.Range("B" & Rows.Count).End(xlUp).Row).Value
For x = 1 To UBound(tBDD, 1)
If tBDD(x, 3) <> "" And IsDate(tBDD(x, 15)) Then
For y = 1 To UBound(tABS, 1)
If tABS(y, 2) = tBDD(x, 3) Then
If DateValue(tBDD(x, 15)) >= CDate(tABS(y, 11)) And DateValue(tBDD(x, 15)) <= CDate(tABS(y, 12)) Then
tBDD(x, UBound(tBDD, 2)) = "OUI"
tABS(y, UBound(tABS, 2)) = CInt(tABS(y, UBound(tABS, 2))) + 1
End If
End If
Next
End If
Next
sWkBDD.Range("A2").Resize(UBound(tBDD, 1), UBound(tBDD, 2)).Value = tBDD
sWkABS.Range("A2").Resize(UBound(tABS, 1), UBound(tABS, 2)).Value = tABS
End If
'
Application.EnableEvents = True
Application.ScreenUpdating = True
'
End Sub- en 'Absence salariés", j'ai ajouté (mais on peut supprimer, évidemment) une colonne [O:O] dans laquelle j'additionne, pour chaque période de maladie renseignée, le nombre de consommation du matricule pendant cette période ;
- plutôt que le calcul global, il y toujours le calcul au coup par coup : à chaque encodage d'une période de maladie ou d'une consommation, le calcul se fait en 'BDD' [AF] et 'Absence salariés' [O] pour le matricule encodé ;
- quelque soit la feuille, un clic gauche sur une en-tête de colonne trie la feuille selon les données xlAscending de cette colonne ;
- quelque soit la feuille, un clic droit sur une en-tête de colonne trie la feuille selon les données xlDescending de cette colonne ;
Y a-t-il des motifs d'absence qu'il conviendrait de ne pas compter comme... absence ? Je pense à cet étrange R.T.T : pas en mission pour le boulot, le bonhomme ?
À tester...
A+
Bonjour GMB,
PI, en mettant mes 80K de lignes, elle met environ 13minutes à se générer. Le résultat donné est vraiment interessant, j'attends simplement le retour de Curilis afin que je puisse vérifier qu'aucune ligne n'est oubliée. Par ailleurs, si c'était possible d'ajouter une colonne juste après "consommation" indiquant le type d'absence afin que je puisse le compiler sur un TCD?
Bonjour Curilis,
Merci ton retour détaillé, en revanche, en copiant toute la base, celle-ci me génère un montant très très faible de consommation(à peine 16K au lieu des 175K constatés avec la macro de GMB). Probablement, je fais une mauvaise manip.
Toutefois, je préfère lorsqu'il y ait un bouton à cliquer pour déclencher la Macro car j'ai l'impression que la macro se lance sans arrêt provoquant des ralentissements ou parfois, cela met le doute sur la bonne exécution de la macro.
Enfin, je préfère qu'il y ait une précision sur le type d'absence si ça te dérange pas afin que je puisse même compiler les données par la suite sur un TCD (par typologie d'absence)
Merci beaucoup à vous deux encore une fois.
Salut Nextia,
Salut gmb,
@gmb,
Hormis le torticolis neuronal que je me suis fait pour casser le chrono, peux-tu regarder ça et me donner ton avis ?
Une grande différence est que j'utilise DateValue pour tester la date en 'BDD', laquelle donne l'heure. Or, après test, CDate donne la date AVEC l'heure : serait-ce un début d'explication ?
Je me suis permis de joindre ton code dans le module 'BDD'
- ta macro démarre sur un double-clic ;
- la mienne avec le bouton rouge ;
- après le calcul, clic droit sur [AG1] pour trier selon le type d'absence.
J'ai vérifié quelques Matricules - 60972, 75, 90 : sauf erreur, ils sont dans les conditions..
For x = 1 To UBound(tBDD, 1)
If tBDD(x, 3) <> "" And tBDD(x, 15) <> "" Then
For y = lgRow1 To UBound(tABS, 1)
If CLng(tABS(y, 2)) = CLng(tBDD(x, 3)) Then
lgRow2 = y
If DateValue(tBDD(x, 15)) >= CDate(tABS(y, 11)) And DateValue(tBDD(x, 15)) <= CDate(tABS(y, 12)) Then
tBDD(x, 32) = "OUI"
tBDD(x, 33) = tABS(y, 10)
tABS(y, 15) = CInt(tABS(y, 15)) + 1
End If
If CLng(tABS(y + 1, 2)) <> CLng(tBDD(x, 3)) And lgRow2 > 0 Then
If CLng(tBDD(x + 1, 3)) <> CLng(tBDD(x, 3)) Then
lgRow1 = lgRow2 + 1
lgRow2 = 0
End If
Exit For
End If
End If
Next
End If
NextÀ te lire,
@ Curulis
Tu as raison. Il y a bien un bug dans ma macro.
Quand une date comme 23/10/2020 est notée dans une cellule, excel la traduit en entier 44127 dans ce cas
Si on a une horodate dans une cellule, comme 23/10/2020 12:48 excel la traduit en valeur décimale : 44127,54
D'où l'erreur : pour excel et avec ces conventions, le 23/10/2020 à 12h48 n'est pas dans la période du 20 au 23/10/2020
Avec la correction apportée dans la macro ci-après, (voir l'instruction isolée) je pense que l'on retrouve bien tes valeurs.
Option Explicit
Dim fb As Worksheet, fa As Worksheet, tabloA, tabloB, tabloR()
Dim i&, iA&
Sub Vérifications()
Set fb = Sheets("BDD")
Set fa = Sheets("Absence salariés")
tabloB = fb.Range("C" & 1 & ":O" & fb.Range("A" & Rows.Count).End(xlUp).Row)
tabloA = fa.Range("B" & 1 & ":L" & fa.Range("B" & Rows.Count).End(xlUp).Row)
ReDim tabloR(1 To UBound(tabloB, 1) - 1, 1 To 2)
For i = 2 To UBound(tabloB, 1)
For iA = 2 To UBound(tabloA, 1)
If tabloB(i, 1) = tabloA(iA, 1) Then
If CDate(tabloB(i, 13)) >= CDate(tabloA(iA, 10)) And CDate(tabloB(i, 13)) < CDate(tabloA(iA, 11)) + 1 Then
tabloR(i - 1, 1) = tabloB(i, 2) & " " & tabloB(i, 3) & " était en absence " '& tabloA(iA, 9)
tabloR(i - 1, 2) = tabloA(iA, 9)
End If
End If
Next iA
Next i
fb.Range("AF2").Resize(UBound(tabloR, 1), 2) = tabloR
End SubBye !
Bye !
bjr GMB, Curilis
Merci à vous deux, peut-on dire que grâce à vos deux cerveaux, que le dernier code est le dernier? comme ça, que je puisse commencer à faire une synthèse? merci
GMB, j'ai lancé la macro depuis 9H10, je te dis lorsqu'elle se termine(il y a environ 100K de lignes ) puis par la suite je ferai mois par mois
Curilis, comme le fichier est bloqué en attendant le lancement de la macro de GMB, je ne peux essayer la tienne.
Je vous tiens au jus et merci encore les experts
je viens de passer de 229K d'anomalies à 330K ... lol
Encore merci à vous deux, GMB et curilis, je vous ai mis dans mon top 5 ;)
Bonjour Curulis, bonjour GMB
J'espère que vous allez bien :)
Je me permets de revenir vers vous au sujet du magnifique travail que vous avez effectué en "duo" ;)
Pour rappel, la macro avait pour but d'identifier les dépenses faites pendant l'absence du salarié.
J'aimerais ajouter une règle à la MACRO initial, qui indiquerait sur une colonne à part "prise de carb effectuée la veille de l'absence du salarié" (essence/gasoil)
Exemple : Matricule 18 a effectué une prise de carburant (colonne Q ESSENCE/GASOIL) le 29/03/2021 à 6:30 alors qu'elle est en congés le 30/03/2021 --> prise de carb effectuée la veille de l'absence"
Autre ex : Matricule 445 est en congés le 31/03/2021 et a effectué une prise de carburant la veille (le 30/03/2021 à 19H) --> "prise de carb effectuée la veille de l'absence"
En bref, peu importe l'heure tant que c'est au maximum 24H avant la date de l'événement.
dites moi si ce n'est pas clair
encore merci
En fait non, je me suis trompé... l'idée est de voir ceux qui ont consommé à moins de 24H de leur reprise(le carburant uniquement pour le moment donc gasoil/essence, colonne Q), donc la veille de leur reprise.
Autrement dit, si on sait qu'il sera absent du 15/04/2021 au 17/04/2021 et que le 17/04/2021 à 5:50 par exemple, il consomme du carburant (ou autre), je voudrais un commentaire qui indique "veille de reprise, donc ok" car le lendemain il reprend le travail.
Salut Nexia,
quelque chose ainsi ?
Juste une ligne à ajouter...
If DateValue(tBDD(x, 15)) = CDate(tABS(y, 12)) Then tBDD(x, 34) = "OUI"À tester, ce que je n'ai pas vraiment le temps de faire tout de suite, vaccination de ma chère mère oblige...
Toujours le même petit bouton rouge pour démarrer la macro.
A+
Pas beaucoup plus testé mais plutôt que d'ajouter une colonne, j'affiche un "!" lors d'une consommation la veille d'une reprise...
OUI !
A+
Salut Nextia,
petite correction : j'avais zappé le fait qu'il ne fallait cibler que les consommations de carburant...
Tu pourrais imaginer une suite d'abréviations pour identifier chaque consommation d'un coup d'oeil.
C = carburant
P = parking
PG = péage
...
A+
top Curulis :) :) !!! Merci bcp !
Bonjour :)
j'espère que vous allez bien, j'ai tenté de relancer la MACRO selon le dernier fichier, il se lance bien mais semble ne pas tenir compte de l'ensemble des lignes (j'ai l'impression que la macro s'arrête uniquement sur Janvier 2022, et ne va pas chercher les infos de février alors qu'il y a bien certaines anomalies)
Savez-vous pourquoi? Elle s'arrête à 5.6K lignes; ma BDD va jusqu'à 35K de lignes