Remplacement de données en fonction d'une liste
Bonjour à tous,
Je suis plutôt novice en macro et je sèche pour trouver une solution à mon problème.
J'ai un tableau exporté chaque mois depuis un logiciel de gestion des temps, que je retravaille manuellement et que je colle ensuite dans un autre tableau pour effectuer des calculs automatiques et qui sont en fonction des valeurs obtenues dans les lignes 1 à 3 de chaque salarié. La trame reste inchangée.
Le tableau est construit de la manière suivante, pour chaque salarié (onglet "Edition planning") :
- ligne 1 : Nom du salarié avec des codes horaires selon les jours travaillés. (Les codes horaires sont ensuite modifié via la fonction "Remplacer", mais avec 70 références qui ne cessent d'augmenter, je me suis dit qu'une petite macro pourrait me faire gagner du temps)
- ligne 2 : En fonction du code horaire en ligne 1 nous rajoutons manuellement "J" ou "N"
- ligne 3 : Nous rajoutons une valeur qui est copié / collée depuis un autre tableau exporté depuis notre logiciel de temps (hélas impossible d'exporter toutes les valeurs sur le même tableau)
Pour essayer d'optimiser ces tâches voici ce que j'aimerai :
- 1er étape : dans l'onglet "Édition planning", selon le code horaire initial et la table de référence dans l'onglet "Correspondance codes horaires", ajouter "J" ou "N" en ligne 2 de chaque salarié
- 2ème étape : remplacer les abrégés des codes horaires de l'onglet "Edition planning" par les abrégés de l'onglet "Correspondance codes horaires", colonne 2 "Codes horaires modifiés".
Pensez vous que l'étape 1 soit réalisable ? (j'ai des doutes mais je tente !)
Pour l'étape 2, j'ai tenté des macros mais je n'arrive pas à faire un remplacement avec une table de correspondance dans un autre onglet.
En vous remerciant par avance pour toute l'aide que vous pourrez m'apporter !
Bonjour,
Si je ne me suis pas planté, sur la base d'une table structurée pour vos correspondance nommée COR :
Sub MAJ()
Dim I%, LR%, LC%
Application.ScreenUpdating = False
Application.Calculation = xlManual
With ActiveSheet
LR = .Cells(.Rows.Count, 1).End(xlUp).Row
LC = .Cells(12, .Columns.Count).End(xlToLeft).Column
.Cells(8, 4).Formula = "=IF(D7="""","""",VLOOKUP(D7,COR,2,FALSE))"
.Cells(9, 4).Formula = "=IF(D7="""","""",VLOOKUP(D7,COR,3,FALSE))"
.Range(.Cells(8, 4), .Cells(9, 4)).Copy
For I = LR + 1 To 15 Step -3
.Range(.Cells(I, 4), .Cells(I, LC)).PasteSpecial xlPasteFormulas
Next I
Application.Calculate
For I = LR + 1 To 15 Step -3
.Cells(I, 4).Resize(2, LC - 3).Copy
.Cells(I, 4).Offset(-1).PasteSpecial xlPasteValues
.Cells(I, 4).Offset(1).Resize(1, LC - 3).ClearContents
Next I
.Cells(8, 4).Resize(2, 1).ClearContents
End With
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End SubEt le fichier qui va bien. Par contre j'ai des correspondances qui ne vont pas, d'où les N/A ... Je suis parti du principe que votre liste était vide.
La ligne 3 pour chaque salarié n'est à renseigner qu'après exécution de la macro car je m'en sert pour mettre des formules.
Cdlt,
Je viens d'effectuer un premier test et à première vue tout fonctionne (effectivement j'avais supprimé des valeurs pour alléger le tableau, d'où les NA) ! Merci infiniment !
Second test avec un nouvel export de mon logiciel de gestion des temps, puis en copiant les nouvelles valeurs dans l'onglet "Édition planning".
Je ne comprends pas pourquoi lorsque j’exécute la macro, les valeurs de la 1ère ligne restent inchangées et les suivantes supprimées. Je n'avais pourtant pas le problème en re-copiant/collant mes données après mise à jour de ma table de correspondance...
J'ai juste mis à jour ma table des correspondances et ajouté une colonne à la fin de mon édition planning pour rajouter une formule pour visualiser les NA
Auriez vous une idée d'où vient cette anomalie ?
Bonjour,
L'écart entre chaque jeu de donnée est-il de 2 lignes ? Serait-il possible de nous fournir le jeu de donnée anonymisé sur lequel ça ne fonctionne pas ?
Normalement toutes les lignes sont prises en compte par la macro, donc hormis le fait que 2 lignes ne séparent pas chaque salarié, rien d'autre ne me vient en tête désolé ...
Cdlt,
Rebonjour,
Je vous mets le fichier en pièce jointe. J'ai l'impression que le problème provient des donnée de la colonne "Salarié". J'ai juste supprimé les noms des salariés, et pour le coup la macro n'actualise plus les valeurs. Lorsque je rajoute les vrais noms des salariés, la macro fonctionne partiellement en laissant juste la 1ère ligne apparaitre sans la modifier. Par contre si je modifie le nom du salarié par "Salarié 1", la macro s'actualise bien.
Cdt
Bonjour,
Alors en effet la colonne SALARIE me permet de déterminer la dernière ligne contenant une valeur. Donc elle est vide, nous n'aurons aucun changement.
Si vous souhaitez vous baser sur une autre colonne il faut changer cette ligne de code :
LR = .Cells(.Rows.Count, 1).End(xlUp).Row 'Où 1 correspond au numéro de la colonne, donc ici colonne A. Essayez avec 2 ou 3.
LR = .Cells(.Rows.Count, 2).End(xlUp).Row 'Par exempleSi il y a des noms de salariés il est très surprenant que la 1ère ligne ne se mette pas à jour. Pour que la macro fonctionne correctement il faut pour résumer que chaque cellule bleue contienne une valeur et c'est sur cette colonne qu'il faut adapter votre code.
Je me suis rendu compte également que l'export mettait probablement un caractère invisible dans votre tableau, d'où le décalage. En effet le code cherche la dernière ligne où est renseigné un salarié (donc la ligne bleue), qui devrait alors me renvoyer 551. Or ce dernier me renvoie 552 donc ça me décale tout d'une ligne. Ce que j'ai fait c'est sélectionner les lignes 552 et 553 et fait SUPPR sur mon clavier pour être sur de bien nettoyer ces lignes avant de lancer la macro et alors tout s'est déroulé comme prévu.
Il est possible de le faire automatiquement par macro, mais j'ai peur qu'un jour l'import ne mette pas ce caractère invisible et que je me retrouve à effacer la ligne d'un salarié ... C'est pourquoi je pense que pour le moment il vaut mieux effacer ces deux dernières lignes à la main.
Si cela devient trop pénible pour vous alors on trouvera une solution, j'ai déjà quelques pistes.
En esperant que mes explications vous aident à résoudre votre problématique.
Ci-joint votre fichier corrigé.
Cdlt,
Rebonjour,
Finalement tout fonctionne. En fait il fallait simplement vider les cellules qui sont d'apparence vides, comme pour les dernières lignes 552/553. Dans mon tout premier fichier envoyé, j'avais du les "vider" machinalement. En y réfléchissant, lorsque je récupère mon fichier du logiciel des temps, je filtre et supprime des valeurs inutiles sur les lignes 2. Du coup ce n'est pas gênant de devoir "vider" aussi les cellules entre chaque noms, puisque je le fais déjà.
J'aurai une dernière requête à vous soumettre : en tant normal je compile 2 fichiers pour en obtenir un, en m'amusant à copier / coller les valeurs, notamment pour ajouter la valeur de ma fameuse ligne 3 pour chaque salarié. Est ce que techniquement il est possible d'ajouter à la macro, les étapes suivantes :
- Étape 3 : dans l'onglet "Edition temps", modifier les valeurs des cellules de D14 à AH193 (voir plus) pour que les heures indiquées soit transformées en heures centièmes (évidement ce serait plus facile si notre logiciel pouvait exporter la valeur directement en centième ou en heure excel, mais le format exporté est plutôt du type texte. Actuellement j'utilise la fonction "remplacement" de "h" -> "," , puis je corrige manuellement les heures où il y a des minutes à corriger).
- Etape 4 : dans l'onglet "Edition planning", pour chaque salarié compléter la ligne 3 avec les valeurs des colonnes D à AH de l'onglet "Édition temps"
Bien évidement si ce n'est pas possible ce n'est pas grave, vous m'avez déjà permis de gagner pas mal de temps :-)
En tout cas je vous remercie pour votre aide précieuse.
Cordialement
Bonjour,
Oui je me doute que c'était du à l'export, c'est assez courant.
Du coup je suis parti du principe que ce caractère serait toujours là pour vous éviter d'avoir à faire cette manipulation. J'ai aussi changé totalement le code suite à votre dernière requête, résultat : elle est plus rapide et plus courte vous avez bien fait de demander !
Sub MAJ()
Dim I%, LR%, LC%
Application.ScreenUpdating = False
Application.Calculation = xlManual
Application.DisplayAlerts = False
Worksheets("Edition temps").Range("D:AH").Replace "h", ":"
With ActiveSheet
LR = .Cells(.Rows.Count, 2).End(xlUp).Row - 1
LC = .Cells(12, .Columns.Count).End(xlToLeft).Column
.Cells(14, LC + 1).Formula = "=IF(D14="""","""",VLOOKUP(D14,COR,2,FALSE))"
.Cells(15, LC + 1).Formula = "=IF(D14="""","""",VLOOKUP(D14,COR,3,FALSE))"
.Cells(16, LC + 1).Formula = "=VLOOKUP($A14, 'Edition temps'!" & Worksheets("Edition temps").[A12].CurrentRegion.Address & ", COLUMN(D14), FALSE)"
.Range(.Cells(14, LC + 1), .Cells(16, LC + 1)).Copy
.Range(.Cells(14, LC + 1), .Cells(553, 65)).PasteSpecial xlPasteFormulas
Application.Calculate
.Range(.Cells(14, LC + 1), .Cells(LR + 2, LC * 2 - 3)).Copy
.Cells(14, 4).PasteSpecial xlPasteValues
.Range(.Cells(14, LC + 1), .Cells(LR + 2, LC * 2 - 3)).ClearContents
End With
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
Application.DisplayAlerts = True
End SubJe vous laisse tester. Pour les heures il s'agit d'heures en journées, donc 12h = 0,5 soit 1 demi journée. Je ne sais trop ce que vous allez en faire par la suite donc je ne m'en suis pas trop occupé. A vous de préciser le besoin.
Et le fichier qui va avec.
PS : Je vous conseille de tester sur une copie auparavant car je ne suis pas sûr sûr de tout mon code vu le nombre de modifications réalisées en peu de lignes de code, sur beaucoup de lignes de données.
Cdlt,
Bonsoir,
Je viens de tester votre codage et il me semble fonctionner. Mon seul "problème" c'est le résultat des lignes 3 avec les heures. En fait, ce que j'aimerai obtenir comme résultat c'est par exemple : transformer "12h00" en "12,00" ; "11h40" en "11,67" ; les "heures minutes" en "heures centièmes" et l'importer en ligne 3 de l'onglet "Edition planning".
Pour vous donner une idée de l'utilité de ces valeurs, elles me permettent de calculer ensuite en automatique via des formules, différentes variables de paie qui dépendent des codes horaires, du travail jour/nuit et du temps de travail, en gros des paramètres de chacune de nos 3 lignes.
" J'ai aussi changé totalement le code suite à votre dernière requête, résultat : elle est plus rapide et plus courte vous avez bien fait de demander ! " -> comme quoi il faut pas hésiter à demander finalement :)
Cordialement
Bonjour,
Là encore je m'en étais douté mais n'étais pas sûr. Pour votre information personnelle il vaut mieux conserver cette conversion de h en deux points afin de convertir les heures en heures journalières (où 0,5 = 12) puis l'astuce consiste a inscrire 24 dans une cellule, la copier et réaliser un collage spécial avec opération de multiplication (Collage Spécial > Opération > Multiplication) sur votre plage d'heures journalières afin d'obtenir des heures décimales. Ca évite les erreurs de retranscription manuelle et est nettement plus rapide quand vous travaillez sur un grand volume de données. J'espère que cette astuce vous aidera pour le futur.
Pour revenir au code, en suivant ces explications, ci-joint le code, que j'ai commenté pour vous expliquer les différentes étapes si vous devez réaliser une maintenance :
Sub MAJ()
Dim I%, LR%, LC%, LR_2%, LC_2%
Application.ScreenUpdating = False
Application.DisplayAlerts = False
With Worksheets("Edition temps")
LR_2 = .Cells(.Rows.Count, 2).End(xlUp).Row 'Dernière ligne tableau basée sur colonne 2
LC_2 = .Cells(12, .Columns.Count).End(xlToLeft).Column 'Dernière colonne tableau basée sur ligne 12
.Range(.Cells(14, 4), .Cells(LR_2, LC_2)).Replace "h", ":" 'Converti h en heure journée
.Cells(LR_2, LC_2).Offset(0, 1) = 24 'Place 24 dans une cellule vide pour h/j
.Cells(LR_2, LC_2).Offset(0, 1).Copy 'Copie 24
.Range(.Cells(14, 4), .Cells(LR_2, LC_2)).PasteSpecial xlPasteValues, xlPasteSpecialOperationMultiply 'Multiplie h journée par 24 pour h décimales
.Cells(LR_2, LC_2).Offset(0, 1).ClearContents 'Supprime 24
End With
With ActiveSheet
LR = .Cells(.Rows.Count, 2).End(xlUp).Row - 1 'Dernière ligne tableau basée sur colonne 2
LC = .Cells(12, .Columns.Count).End(xlToLeft).Column 'Dernière colonne tableau basée sur ligne 12
.Cells(14, LC + 1).Formula = "=IF(D14="""","""",VLOOKUP(D14,COR,2,FALSE))" 'Formule 1ère ligne salarié à droite de dernière colonne
.Cells(15, LC + 1).Formula = "=IF(D14="""","""",VLOOKUP(D14,COR,3,FALSE))" 'Formule 2ème ligne salarié à droite de dernière colonne
.Cells(16, LC + 1).Formula = "=VLOOKUP($A14, 'Edition temps'!" & Worksheets("Edition temps").[A12].CurrentRegion.Address & ", COLUMN(D14), FALSE)" 'Formule 3ème ligne salarié à droite de dernière colonne
.Range(.Cells(14, LC + 1), .Cells(16, LC + 1)).Copy 'Copie les 3 formules
.Range(.Cells(14, LC + 1), .Cells(553, 65)).PasteSpecial xlPasteFormulas 'Etend formules nombre lignes x nombre colonnes
.Range(.Cells(14, LC + 1), .Cells(LR + 2, LC * 2 - 3)).Copy 'Copie plage de formules
.Cells(14, 4).PasteSpecial xlPasteValues 'Colle en valeur sur tableau initial
.Range(.Cells(14, LC + 1), .Cells(LR + 2, LC * 2 - 3)).ClearContents 'Nettoie plage de formules
End With
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End SubCode à placer dans le module 1 afin de remplacer l'ancien erroné. Si vous avez toujours le module 2 dans votre fichier, vous pouvez le supprimer il m'a servi pour des tests qui sont obsolètes.
En esperant que ce code vous permette de gagner un temps précieux pour vous et vos collaborateurs.
Cdlt,
Bonjour,
C'est vrai que je n'avais pas pensé à procéder de la sorte, merci pour votre astuce.
Je teste le nouveau code dès mon retour de week end mais je ne doute pas que ça va fonctionner.
Encore merci pour le temps que vous avez passé sur mon cas. Vous me donnez envie de continuer mon apprentissage du codage !
Bon week end
Cordialement
Bonjour,
J'ai effectué mes tests et tout fonctionne parfaitement. Merci beaucoup !
Par contre j'ai essayé de comprendre le codage et notamment dans l'éventualité où le nombre de salarié soit plus conséquent, cela augmentera forcément le numéro de la dernière ligne.
Je me suis dit qu'il fallait modifier simplement au niveau du code, en modifiant 553 par 1000 par exemple. Mais apriori cela ne fonctionne plus, il doit donc y avoir une autre zone à modifier que je n'ai pas trouvée.
.Range(.Cells(14, LC + 1), .Cells(553, 65)).PasteSpecial xlPasteFormulas 'Etend formules nombre lignes x nombre colonnesCordialement
Bonjour,
Ci-joint :
Sub MAJ()
Dim I%, LR%, LC%, LR_2%, LC_2%
Application.ScreenUpdating = False
Application.DisplayAlerts = False
With Worksheets("Edition temps")
LR_2 = .Cells(.Rows.Count, 2).End(xlUp).Row 'Dernière ligne tableau basée sur colonne 2
LC_2 = .Cells(12, .Columns.Count).End(xlToLeft).Column 'Dernière colonne tableau basée sur ligne 12
.Range(.Cells(14, 4), .Cells(LR_2, LC_2)).Replace "h", ":" 'Converti h en heure journée
.Cells(LR_2, LC_2).Offset(0, 1) = 24 'Place 24 dans une cellule vide pour h/j
.Cells(LR_2, LC_2).Offset(0, 1).Copy 'Copie 24
.Range(.Cells(14, 4), .Cells(LR_2, LC_2)).PasteSpecial xlPasteValues, xlPasteSpecialOperationMultiply 'Multiplie h journée par 24 pour h décimales
.Cells(LR_2, LC_2).Offset(0, 1).ClearContents 'Supprime 24
End With
With ActiveSheet
LR = .Cells(.Rows.Count, 2).End(xlUp).Row - 1 'Dernière ligne tableau basée sur colonne 2
LC = .Cells(12, .Columns.Count).End(xlToLeft).Column 'Dernière colonne tableau basée sur ligne 12
.Cells(14, LC + 1).Formula = "=IF(D14="""","""",VLOOKUP(D14,COR,2,FALSE))" 'Formule 1ère ligne salarié à droite de dernière colonne
.Cells(15, LC + 1).Formula = "=IF(D14="""","""",VLOOKUP(D14,COR,3,FALSE))" 'Formule 2ème ligne salarié à droite de dernière colonne
.Cells(16, LC + 1).Formula = "=VLOOKUP($A14, 'Edition temps'!" & Worksheets("Edition temps").[A12].CurrentRegion.Address & ", COLUMN(D14), FALSE)" 'Formule 3ème ligne salarié à droite de dernière colonne
.Range(.Cells(14, LC + 1), .Cells(16, LC + 1)).Copy 'Copie les 3 formules
.Range(.Cells(14, LC + 1), .Cells(LR + 2, LC * 2 - 3)).PasteSpecial xlPasteFormulas 'Etend formules nombre lignes x nombre colonnes
.Range(.Cells(14, LC + 1), .Cells(LR + 2, LC * 2 - 3)).Copy 'Copie plage de formules
.Cells(14, 4).PasteSpecial xlPasteValues 'Colle en valeur sur tableau initial
.Range(.Cells(14, LC + 1), .Cells(LR + 2, LC * 2 - 3)).ClearContents 'Nettoie plage de formules
End With
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End SubJe pense que j'avais dû "figer" les certaines variables du codes pour mes tests et ai oublié de les reconvertir. Vous aviez bien raison pour la ligne à modifier, à voir si vous aviez réaliser la même que moi. LR renvoi à la dernière ligne du tableau, auquel je rajoute 2 pour avoir les 2 lignes "vides" de la colonne A (où en AI j'ai bien des formules). J'ai aussi remis de façon dynamique le nombre de colonne au cas où vous avez plus de jours inclus dans votre plage.
Cdlt,
Rebonjour,
C'est parfait. Ça prend bien en compte toutes les lignes même si je rajoute le double de salariés.
Cordialement
Bonjour,
Je suis toujours en train de peaufiner et améliorer mon tableau. Je souhaiterai rajouter une donnée qui va m'être utile pour basculer ensuite dans mon tableau de calculs des variables de paie.
Voici mon idée : Je souhaite écraser les valeurs de la colonne C de l'onglet "Edition planning" (qui ne me sont pas utiles), en ajoutant une recherchev qui viendra reporter la valeur de la colonne 2 de mon tableau de correspondance "Matricule".
En gros, en colonne C ligne 1 // ligne 2 // ligne 3 de chaque salarié je veux faire apparaitre le matricule du salarié. (Les matricules de nos salariés commence par un chiffre différent pour chaque société que nous gérons, avec des calculs de variables différents donc des tableaux de calculs différents. Avec cette donnée supplémentaire je pourrais donc filtrer les salariés de la société que je veux, pour ensuite copier coller les copier coller dans mon tableau de calcul de variables).
J'ai tenté de le faire moi même mais j'ai encore du travail pour arriver à tout comprendre du codage.
Je vous mets en pièce jointe le tableau avec ma proposition.
En vous remerciant par avance pour votre aide.
Cordialement