Discussion :

[dutronephilippe]

Bonjour à tous,

Vous trouverez en pièce jointe les données.

Dans ce classeur, il y a une feuille 'Commun' (commun à tous).
Et plusieurs feuilles avec des noms différents (j'en ai laissé que 3).

C'est un peu difficile à expliquer mais je vais faire de mon mieux. Je cherche à automatiser tout ça en vba.
(niveau débutant)

A partir de maintenant, je vais parler du document Excel joint.

- je cherche à obtenir le résultat surligné en couleur vert sur les onglets (BBB, WAR, TIE).
Comment obtenir ce résultat?

1) Réf interne:
a. c'est la Réf interne en colonne K collée valeur en colonne N
b. j'ai remplacé 'CDS: ' par rien
c. j'ai supprimé les doublons
d. trié par ordre croissant

2) Invoice No:
a. ça vient de l'onglet 'Commun' en colonne C
b. pour le représentant BBBKO, le raccourci est BBB dans l'onglet commun
c. j'applique un filtre pour BBB
d. je sélectionne tous les Invoice No de ce représentant et je colle valeur dans l'onglet BBB ent colonne P
attention, pour certain, il faut convertir les données en nombre
e. supprimer les doublons et trier par ordre croissant

3) Colonne O:
a. ça correspond au matching des pièces des 2 onglets, une simple soustraction, si c'est égale à 0, ça veut dire que ça correspond.
b. si ce n'est pas égale, décaler de ligne afin que les autres pièces match bien.
c. la différence correspond au pièce manquante soit dans l'un soit dans l'autre onglet

J'espère que c'est assez clair.

Je vous remercie grandement pour votre aide.

[Invité]

Bonjour,

Ci-dessous le code résolvant le premier point. Je n'ai plus le temps pour l'instant et reviendrai dès que possible.
Cordialement.
Jean-Claude.

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
Sub Ref_Interne()
    Dim plage As Range
    Dim cel As Range
    Dim x As Integer
    Application.ScreenUpdating = False 'Interdit l'affichage pour gagner du temps d'exécution
    'copie de l'étiquette de colonne
    For x = 12 To 14
        Cells(1, x) = Cells(1, 11)
    Next
    'la variable plage couvre la matrice partant de la cellule(ligne2, colonne11) à _
    depuis la dernière cellule de la feuille on simule l'appui des touches Ctrl flèche vers le haut _
    ainsi on est sûr d'être sur la dernière cellule de la colonne contenant une donnée.
    Set plage = Range(Cells(2, 11), Cells(Rows.Count, 11).End(xlUp))
    ' la boucle For Next parcourt chaque cellule
    For Each cel In plage
        'la valeur de la cellule décalée de 3 colonnes devient la partie droite de la cellule d'origine _
        sur sa longueur - 6 (CDS : )= 6 caractères
        cel.Offset(0, 1) = Right(cel, Len(cel) - 6)
    Next
    'on utilise le filtre élaboré pour copier sans doublons
    Range("L1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
        ("M1:M2"), CopyToRange:=Columns("N:N"), Unique:=True
    'tri ce la colonne N
    Range("N2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Sort Key1:=Range("N2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    'on efface les colonnes intermédiaires
    Range("L:L,M:M").Clear
    Application.ScreenUpdating = True 'Réinitialise l'affichage
End Sub

ReBonjour,

Ceci pour appliquer le traitement à chaque feuille sauf "Commun"

Code :

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

1
2
3
4
5
6
7
8
Sub Feuilles()    
    Dim feuille As Worksheet
    Application.ScreenUpdating = False 'Interdit l'affichage pour gagner du temps d'exécution
    For Each feuille In Worksheets
        If Not feuille.Name = "Commun" Then feuille.Select: Ref_Interne
    Next
    Application.ScreenUpdating = True 'Réinitialise l'affichage
End Sub

[dutronephilippe]

Bonjour JC,

Merci beaucoup pour la première partie.
Ça à l'air bien parti.
J'ai hâte de voir le résultat final.

Grand merci encore une fois.

[Invité]

Bonjour,

Finalisation ci-dessous, à te relire si besoin de renseignements complémentaires.
Cordialement.
Jean-Claude

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
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
Sub Feuilles()    
    Dim feuille As Worksheet
    Application.ScreenUpdating = False 'Interdit l'affichage pour gagner du temps d'exécution
    For Each feuille In Worksheets
        If Not feuille.Name = "Commun" Then feuille.Select: Ref_Interne: Invoice: Decalage
    Next
    Application.ScreenUpdating = True 'Réinitialise l'affichage
End Sub
 
Sub Ref_Interne()
    Dim plage As Range
    Dim cel As Range
    Dim x As Integer
    'copie de l'étiquette de colonne
    For x = 12 To 14
        Cells(1, x) = Cells(1, 11)
    Next
    'la variable plage couvre la matrice partant de la cellule(ligne2, colonne11) à _
    depuis la dernière cellule de la feuille on simule l'appui des touches Ctrl flèche vers le haut _
    ainsi on est sûr d'être sur la dernière cellule de la colonne contenant une donnée.
    Set plage = Range(Cells(2, 11), Cells(Rows.Count, 11).End(xlUp))
    ' la boucle For Next parcourt chaque cellule
    For Each cel In plage
        'la valeur de la cellule décalée de 3 colonnes devient la partie droite de la cellule d'origine _
        sur sa longueur - 6 (CDS : )= 6 caractères
        cel.Offset(0, 1) = Right(cel, Len(cel) - 6)
    Next
    'on utilise le filtre élaboré pour copier sans doublons
    Range("L1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
        ("M1:M2"), CopyToRange:=Columns("N:N"), Unique:=True
    'tri ce la colonne N
    Range("N2").Select
    Range(Selection, Selection.End(xlDown)).Select
    VerifNum
    Selection.Sort Key1:=Range("N2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    'on efface les colonnes intermédiaires
    Range("L:L,M:M").Clear
End Sub
 
 
Sub Invoice()
    Dim cel As Range
    onglet = ActiveSheet.Name
    Sheets("Commun").Select
    'on utilise le filtre élaboré pour copier sans doublons
    Range("M3") = Range("C3")
    Range("L3") = Range("H3")
    Range("L4") = onglet
    Range(Cells(3, 3), Cells(Rows.Count, 8).End(xlUp)).Select
    Selection.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
        ("L3:L4"), CopyToRange:=Range("M3"), Unique:=True
    Range("M4").Select
    Range(Selection, Selection.End(xlDown)).Select
    VerifNum
    Selection.Sort Key1:=Range("M4"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Selection.Copy Destination:=Worksheets(onglet).Range("P2")
    Range("K:M").Clear
    Sheets(onglet).Select
End Sub
Sub VerifNum()
    Dim cel2 As Range
    For Each cel2 In Selection
        If IsNumeric(cel2) Then cel2 = CLng(cel2)
    Next
End Sub
 
Sub Decalage()
    Dim plage As Range
    Dim cel As Range
    Dim x As Integer
    Set plage = Range(Cells(2, 14), Cells(Rows.Count, 14).End(xlUp))
    For Each cel In plage
        If cel.Offset(0, 2) - cel <> 0 Then
            cel.Activate
            cel.Insert Shift:=xlDown
            cel.Offset(-1, 1) = cel.Offset(-1, 0) - cel.Offset(-1, 2)
       End If
    Next
End Sub

[dutronephilippe]

Bonjour JC,

J'ai mis tous les codes que vous avez crée l'un en dessous de l'autre, ça me met: Erreur de compilation:Nom ambigu: Feuilles. ça le fait aussi pour Ref_Interne.

Du coup, j'ai inséré au autre module pour mettre le programme ci-dessus. Et là, ça me met Erreur d'exécution '1004': Afin d'éviter la perde des données, Excel ne déplace pas les cellules non vides en dehors de la feuille de calcul. Sélectionner un autre emplacement pour y insérer les nouvelles cellules ou supprimez des données à la fin de la feuille de calcul. Si aucune donnée ne peut être déplacée en dehors de la feuille de calcul. vous pouvez paramétrer les cellules qu'Excel considère comme....

Et en débogage: ça surligne en jaune: cel.Insert Shift:=xlDown vers la fin du code.

Merci pour votre considération.

[Invité]

Bonsoir,

J'ai refait les essais en repartant des données de base (classeur initial, copier-coller des procédures affichées ici sur le forum) tout fonctionne ... presque.
Il faut que les colonnes cibles (Réf interne Invoice No) soient effacées, donc, ajouter une ligne dans la première des procédures.
Il n'y a pas de nom ambigu donc si nécessaire repartir de modules totalement vides.
La feuille "TIE" ne présente aucun Invoice, de ce fait la procédure "Décale" tourne en rond. Il faut que le programme teste la présence de données sinon on sort de la procédure.

Quelques trucs pour se faciliter la vie.

Pour obtenir facilement la syntaxe de la plupart des travaux effectués,

1. Lancer l'enregistrement d'une macro.
2. Exécuter le travail considéré (attention, tout sera enregistré Sélections, déplacements d'affichage, etc... Par exemple dans Excel 2013 il y a une possibilité de suppression des doublons, après avoir sélectionné la colonne K j'ai obtenu sans problème cette syntaxe :

   ActiveSheet.Range("$A$1:$K$65534").RemoveDuplicates Columns:=11, Header:= xlYes

   qui remplacerait avantageusement l'utilisation du filtre élaboré.

Pour l'organisation des feuilles de structure identiques (même étiquettes de colonne, formules identiques etc...) on peut utiliser le "groupe de travail". Dans notre cas

1. Sélectionner la feuille "BBB"
2. en gardant enfoncée la touche "MAJ" cliquer l'onglet "TIE"
3. les 3 feuilles vont se comporter comme si elles étaient carbonées.
4. Cliquer l'icône de sélection de la feuille entière (à gauche de la colonne A)
5. Pointer entre 2 colonnes pour obtenir le pointeur de dimensionnement
6. Double-Cliquer pour obtenir un ajustement automatique de la largeur des colonnes
7. Sélectionner de N2 à P65000 (en fait à la dernière cellule)
8. Appuyer "Suppr" pour effacer.
9. Cliquer l'onglet "Commun" pour désactiver le groupe de travail

Pour le VBA.
Pour tester un programme, exécuter par modules et par ligne si besoin est.
Par exemple :

1. Sélectionner la feuille "BBB"
2. Exécuter chacun des 3 modules Ref_Interne, Invoice, Décalage en vérifiant chaque fois la justesse des données obtenues.
3. Faire de même avec chaque feuille

Si un module pose problème. faire le nécessaire pour avoir en fond la feuille sélectionnée et par dessus la fenêtre affichant le VBA (on peut y accéder par ALT+F11)

• F8 exécute ligne à ligne et permet entre autres de suivre les variables (mettre le pointeur au-dessus du nom et une légende affiche la valeur en cours)
• F9 pose un point d'arrêt sur la ligne où se trouve le curseur (ça permet par exemple de sauter une boucle traitant de nombreux éléments) re F9 pour l'enlever.
• F5 pour exécuter sans arrêt.

Voilà, amuse-toi bien.
Cordialement.
Jean-Claude

[dutronephilippe]

Merci beaucoup Jean-Claude!!!
J'aimerais trop pouvoir faire ce type de programmation.

Ça marche bien !
Grand merci.