VB/VBA pour extraction de données d'une feuille vers une autre avec formatage des caractères

**pchang** · 28/02/2015, 06h52

Au secours, y aurait il une âme charitable ?

Voilà je suis technicien informatique dans un centre de formation et notre secrétariat bute sur un problème que je n’arrive pas à résoudre (même si ce n’est pas mon domaine), car voilà, je ne connaît rien ou si peu à Excel.
Pour faire simple, voilà le problème :
On reçoit de diverses sociétés, des listes d’élèves d’une part et des listes de professeurs d’autre part.
A partir de ces infos, on remplit un tableau (souvent par copier coller de ce qui nous a été envoyé) qui regroupes toutes les infos (nécessaires ou non à NOTRE travail). Au final on a autant de lignes que d’élèves (nom, prénoms et date de naissance, etc) et les professeurs qui apparaissent forcément en doublons : 1 prof pour 12 élèves en moyenne,
Ce tableau est sur une feuille nommée « Stage »
A partir de ce tableau, je dois extraire les nom, prénom et date de naissance des élèves et prof. (dans un format bien particulier : pas d’espace, ni de é, à, ç… et date sur 6 chiffres) vers une autre feuille nommé « Bio » à 3 colonnes seulement

Pour le moment, je fais les opérations suivantes :

Copie des noms des élèves Stage/Colonne J et collage en Bio/colonne A
Copie des prénoms des élèves Stage/Colonne K et collage en Bio/colonne B
Copie des date de naissance des élèves Stage/Colonne L et collage en Bio/colonne C
Copie des noms des prof. Stage/Colonne S et collage en Bio/colonne A
Copie des prénoms des prof. Stage/Colonne T et collage en Bio/colonne B
Copie des date de naissance des prof. Stage/Colonne V et collage en Bio/colonne C
→ On retrouve tous les nom en A, tous les prénoms en B et les date de naissances en C
A partir de ces 3 colonnes, je crée 3 autres colonnes (E ,F,G) ou je colle les ABC en appliquant des formules pour supprimer les éventuels espaces avant (SUPPRESPACE) je passe en minuscule et j’applique une fonction (ALT+F11) qui remplace les é par e etc.
Et je me retrouve au final avec :
** certaines lignes vides si la salle de 12 élèves n’en héberge que 10
** 12 fois le nom du professeurs
** des dates mal formatées que le SUPPRESPACE transforme en chiffre etc…

Est il possible en VB (VBA ?) de :
- Tant que la Stage/colonne J n’est pas vide de copier les valeurs Stage Col J vers Bio/colonne A, Stage/Col K vers Bio/colonne B et Stage/Col L vers Bio/colonne C
- Puis, mais cette fois en testant les doublons (qui peuvent ne pas se suivre) de copier les valeurs de stage/col S vers Bio/colonne A, Stage/Col T vers Bio/colonne B et Stage/Col U vers Bio/colonne C
- Remplacer dans Bio Colonne A, B les caractères accentués par des lettres sans accent
- Supprimer les espaces avant après (mais pas l’espace dans le nom, ex Le Bon – le bon) dans Bio Colonne A, B les caractères accentuées par des lettres sans accent
- Supprimer les ou les espaces devant la date de naissance et lui imposer le format jj/mm/aaaa sans retrouver une valeur style 256487.
Ouf.

En tout cas grand merci à tous même à ceux qui ont décroché à la 5eme ligne.
Je joue un peu au jeu de la patate chaude, mais je suis le bec dans l’eau devant excelle et cette manip hebdomadaire me gonfle de plus en plus…
Bon courage à ceux qui décide de plancher.

**Daniel.C** · 28/02/2015, 10h41

Bonjour,

Mets un classeur en pièce jointe sans données confidentielles (remplace les noms). Ca sera plus facile pour tester.

**pchang** · 28/02/2015, 13h22

Bonjour,
Je pensais l'avoir joint : erreur, Mea Culpa.
En plus je découvre le forum...
Voici le fichier (nom changé bien sur). https://dl.dropboxusercontent.com/u/...de%20stage.xls

Pour infos j'ai supprimé quelques colonnes mais je ne pense pas que cela impacte.
En tout cas, quelque soit le résultat, un grand merci à vous de vous être (pour le moment) simplement sur mon problème
Bien cordialement

**Daniel.C** · 28/02/2015, 14h15

Voici le début de la macro :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub Copie()
    Dim Plage As Range, C As Range
    With Sheets("Stage")        Set Plage = .Range("H1", .Cells(.Rows.Count, 10).End(xlUp))
    End With
    With Sheets("Bio")
        Plage.Copy .[A1]
        .[C:C].NumberFormat = "dd/mm/yy"
        For Each C In .Range("A1", .Cells(.Rows.Count, 2).End(xlUp))
            C.Value = Application.Trim(C.Value)
            C.Value = LCase(C.Value)
            C.Value = Sans_accents(C)
        Next C
    End With
End Sub

Je n'ai pas compris :

- Puis, mais cette fois en testant les doublons (qui peuvent ne pas se suivre) de copier les valeurs de stage/col S vers Bio/colonne A, Stage/Col T vers Bio/colonne B et Stage/Col U vers Bio/colonne C

Est-ce que tu es sûr de la colonne et est-ce que tu peux donner un exemple ?

**pchang** · 28/02/2015, 14h42

Tout d’abord, merci de m’aider.

Ce que je veux dire c’est que dans la feuille Bio, après la copie des colonnes JKL, puis STV de la feuille Stage, on a maintenant, dans la feuille Bio 3 colonnes ABC (Nom Prénom DateNaissance).
==> Cette colonne A reprend les noms (élèves, puis prof.) : généralement avec les élèves il n’y a pas de doublon (association Nom Prénom DateNaissance) car le même élève ne peut être inscrit qu’une seule fois par semaine.
==> Mais dans la colonne S, il y a plusieurs fois de suite le même nom de prof (autant de fois qu’il y a d’élèves dans cette salle, car à chaque ligne de l’élève il y a le nom du prof.) : en regardant la colonne S, tu te rends compte que les noms des profs apparaissent sur plusieurs lignes de suite.

Voir sur ce fichier joint (v2) dans lequel j’ai surligné les cases dont je te parle : https://dl.dropboxusercontent.com/u/...stage%20V2.xls

Au final je souhaiterais que les professeurs n’apparaissent qu’une seule fois (par prof).

Pour te donner une idée du pourquoi de tout ce travail, ce fichier BIO est en fait destiné à être exporté en CSV, puis remis à notre service des accès au site (autorisation de passage).
Je me rends compte que par email, c’est bien plus hardu à expliquer par écrit que de vive voix

Ha Ha

**Daniel.C** · 28/02/2015, 14h49

Vérifie les encolonnements. Tout à l'air décalé.

**pchang** · 01/03/2015, 10h18

Tu as raison, voila un fichier et un texte à jour

Source : Stage, colonne HIJ, puis QRT
Destination : BIO, colonnes ABC

Ce que je veux dire c’est que dans la feuille Bio, après la copie des colonnes HIJ, puis QRT de la feuille Stage, on a maintenant, dans la feuille Bio 3 colonnes ABC (Nom Prénom Naissance).
==> Cette colonne A reprend les noms (élèves, puis prof.) : généralement avec les élèves il n’y a pas de doublon (association Nom Prénom Naissance) car le même élève ne peut être inscrit qu’une seule fois par semaine.
==> Mais dans les colonnes QRT, il y a plusieurs fois de suite le même nom/prénom/naissance d'un prof (autant de fois qu’il y a d’élèves dans cette salle, car à chaque ligne de l’élève il y a le nom du prof.) : en regardant ces colonne Q, tu te rends compte que les noms des profs apparaissent sur plusieurs lignes de suite (idem pour R et T).

Voir sur ce fichier joint (v2) dans lequel j’ai surligné les cases dont je te parle : https://dl.dropboxusercontent.com/u/...stage%20V2.xls

Au final je souhaiterais que les professeurs n’apparaissent qu’une seule fois (par prof), et les dates de naissances soient au format jj/mm/aaaa.

Merci pour ton travail et mes excuses pour mes erreurs ou infos mal formulées.

**Daniel.C** · 02/03/2015, 11h52

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Sub test()
    Dim DerLig As Long, Plage As Range, C As Range, Lig As Long
    With Sheets("Stage")
        DerLig = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set Plage = .Range("H1" & ":J" & DerLig)
        Plage.Copy [BIO!A1]
        Lig = [BIO!A65000].End(xlUp).Row + 1
        Set Plage = .Range("Q2" & ":R" & DerLig)
        Plage.Copy Sheets("BIO").Cells(Lig, 1)
        Set Plage = .Range("T2" & ":T" & DerLig)
        Plage.Copy Sheets("BIO").Cells(Lig, 3)
    End With
    With Sheets("BIO")
        DerLig = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set Plage = .Range("A2" & ":B" & DerLig)
        For Each C In Plage
            C.Value = LCase(C.Value)
            C.Value = Sans_accents(C)
        Next C
        For i = DerLig To 2 Step -1
            If .Cells(i, 1).Value = "" Then Rows(i).Delete
        Next i
        DerLig = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set Plage = .Range("C2" & ":C" & DerLig)
        For Each C In Plage
            If InStr(1, C.Value, " ") > 0 Then
                C.Value = Application.Trim(C.Value)
            End If
        Next C
        .Range("A1:C" & DerLig).RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes
    End With
End Sub

**pchang** · 02/03/2015, 19h44

Grand merci pour ton travail (rapide en plus)

Comme je l'ai écrit dans le 1er post, je découvre Excel et encore plus le VBA. Du coup j'ai bien mis 10 minutes avant de comprendre qu'il fallait coller le code dans un nouveau module... Misère de moi.
Dès demain, j'imprime et je tente de comprendre le fonctionnement de ces quelques lignes magiques.
Puis-je abuser et te poser une dernière question ?
Dans la colonne des dates de naissance, certaines date comporte, en 1er et/ou 2eme caractère un espace. Je j'applique la formule SUPPRESPACE(cellule), je me retrouve avec une valeur numérique du style 33002 au lieu d'une date (09/05/90). Comment faire pour supprimer le/les espaces ET retrouver une date en jj/mm/aaaa ?
Aller, j’arrête là car j'ai entendu le soupir

de dépit devant un tel ignorant (moi)

Bonne soirée et encore grand merci

**Daniel.C** · 02/03/2015, 20h49

N'hésite pas à demander des explications. Je peux poster le code avec des commentaires.

je me retrouve avec une valeur numérique du style 33002 au lieu d'une date (09/05/90). Comment faire pour supprimer le/les espaces ET retrouver une date en jj/mm/aaaa ?

Ca a l'air de fonctionner chez moi; Feuille "Stage" :

Nom : Capture.JPG
Affichages : 2189
Taille : 15,7 Ko

Sur la feuille "BIO" :

Nom : Capture.JPG
Affichages : 2311
Taille : 26,3 Ko

**pchang** · 03/03/2015, 06h53

Merci de ta proposition de commenter les lignes de code : je n'ai pas osé demander.
Pour ce qui est des espaces, je ferai un test au bureau ce matin : en effet chez moi sur mac avec la version Office de Mac, je me suis aperçu que beaucoup de choses ne fonctionnent pas depuis mon mac vers mon PC et en particulier les formules. Je ne comprends pas pourquoi. Dans mon module "sans accent", les lettres qui doivent être remplacée apparaissent normalement sur le Mac (ou j'ai copié le code) mais affiche des caractères bizares une fois lancé sur un PC (win7 + office 2010).
Bonne journée

**Daniel.C** · 03/03/2015, 10h45

Voila la macro commentée. Si j'ai l'occasion de la tester sur Mac, je le ferai.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
Sub test()
    Dim DerLig As Long, Plage As Range, C As Range, Lig As Long
    'on travaille sur la feuille "Stage". Les plages précédées d'un "."
    'appartiennent à cette feuille
    With Sheets("Stage")
        'calcul de la dernière ligne de la colonne A (25 dans le classeur)
        DerLig = .Cells(.Rows.Count, 1).End(xlUp).Row
        'définition de la plage H:J
        Set Plage = .Range("H1" & ":J" & DerLig)
        'copie de la plage en A1 de la feuille BIO
        Plage.Copy [BIO!A1]
        'calcul de la première cellule vide de la colonne A
        Lig = [BIO!A65000].End(xlUp).Row + 1
        'définition de la plage Q:R  de la feuille "Stage"
        Set Plage = .Range("Q2" & ":R" & DerLig)
        'Copie de cette plage sous la plage de la feuille BIO
        Plage.Copy Sheets("BIO").Cells(Lig, 1)
        'définition de la plage de la colonne T  de la feuille "Stage"
        Set Plage = .Range("T2" & ":T" & DerLig)
        'copie en colonne C de la feuille BIO
        Plage.Copy Sheets("BIO").Cells(Lig, 3)
    End With
    'on travaille sur la feuille "BIO". Les plages précédées d'un "."
    'appartiennent à cette feuille
    With Sheets("BIO")
        'calcul de la dernière ligne de la colonne A
        DerLig = .Cells(.Rows.Count, 1).End(xlUp).Row
        'détermination de la plage A:B
        Set Plage = .Range("A2" & ":B" & DerLig)
        'Boucle sur chaque cellule de cette plage
        For Each C In Plage
            'Transformation de la cellule en minuscules
            C.Value = LCase(C.Value)
            'suppression des accents
            C.Value = Sans_accents(C)
        Next C
        'suppression des lignes dont la cellule de la colonne A est vide
        'quand on supprime des lignes, il faut commencer par la dernière ligne en remontant
        'boucle de la dernière ligne à la ligne 2
        For i = DerLig To 2 Step -1
            'si la cellule de la colonne A est vide, on supprime la ligne
            If .Cells(i, 1).Value = "" Then Rows(i).Delete
        Next i
        'calcul de la dernière ligne de la colonne A (pour tenir compte des suppressions)
        DerLig = .Cells(.Rows.Count, 1).End(xlUp).Row
        'définition de la plage C
        Set Plage = .Range("C2" & ":C" & DerLig)
        'Alignement du texte à gauche (les dates des profs sont centrées)
        Plage.HorizontalAlignment = xlLeft
        'boucle sur la plage pour suppression des espaces en trop
        For Each C In Plage
            'si on trouve un espace
            If InStr(1, C.Value, " ") > 0 Then
                'on applique l'équivalent de SUPPRESPACE
                C.Value = Application.Trim(C.Value)
            End If
        Next C
        'Suppression des doublons
        .Range("A1:C" & DerLig).RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes
    End With
End Sub

**pchang** · 11/12/2015, 15h46

Daniel, si tu suis toujours ce fil j'ai un SOS...
Au secours pour les autres "pointus des macros"

J'ai du adapté ces lignes de codes comme suit :
Set Plage = .Range("J2" & ":K" & DerLig) (au lieu de "J2" & ":L" car plus besoin de la date donc de la colonne L)
Idem pour
Set Plage = .Range("S2" & ":T" & DerLig) (au lieu de "S2" & ":U" car plus besoin de la date donc de la colonne L)
J'ai aussi ajusté : .Range("A1:B" & DerLig).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes (au lieu de .Range("A1:C" ...

Cela fonctionne toujours aussi bien sauf que désormais, je dois avoir une nouvelle colonne dans ".Range" qui reprend le 3 premiers caractère de la colonne A du Sheets("1.Stage")

Pour résumer, je dois avoir dans 2.BioDatas
La colonne A qui reprend les 3 premiers caractère de la colonne A de l''onglet 1.Stages
La colonne B qui reprend (à la suite) les colonnes J et S de l''onglet 1.Stages
La colonne C qui reprend (à la suite) les colonnes K et T de l''onglet 1.Stages
Naturellement toutes les mises en forme, retrait de doublons, etc restent d'actualité. Il "suffit juste d'ajouter cette P... de partie de colonne A/1.stages.

Pourrais tu m'aider ?

Question subsidiaire, que je développerai peut-être : est-il possible d’exporter en CSV automatiquement en ajoutant dans le nom du fichier exporté les 3 caractères de la colonne A ? par exemple Login-Stage-xxx.csv ou xxx est la valeur de la colonne A (P51 par exemple) ?

Merci d'avance pour toute l'aide apportée.

**Daniel.C** · 11/12/2015, 16h39

Bonjour,

Je n'ai pas de plage commençant par J2 dans mon code (message #12) ? Poste ton code. J'ai encore ton ancien classeur. S'il est modifié, poste le nouveau.

**pchang** · 11/12/2015, 19h44

Donc il me faudrait, dans la 1ere colonne (A) de l'onglet BIO, les 3 premières lettre de la colonne A (référence interne) de l'onglet de l'onglet Stage
Les nom en colonne B les prénoms en C et les date naissance en D ( onglet BIO)
Merci d'avance pour ton aide... je suis sur un petit projet et je risque de te solliciter
Bonne soirée
Denis