Discussion :

[AntoineBQ]

Bonjour à tous et à toutes,

Je viens à vous dans l'espoir de trouver une solution (formule ou macro) afin de résoudre mon problème.

Toutes les semaines, j'extrait de mon progiciel d'entreprise des lignes de commandes non-réceptionnées par des agences. Ces lignes non-réceptionnées peuvent être dûes à une livraison non-effectuée comme une réception oubliée par un magasinier. Ainsi, chaque semaine le fournisseur m'établit des réponses (colonne P, feuillet 'S02').

D'une semaine à l'autre, je ressors la même extraction et des lignes peuvent très bien être en doublon d'une semaine à une autre.

Mon problème : je voudrais qu'Excel puisse rechercher les lignes identiques d'un feuillet à un autre et puisse copie-colle le commentaire dans le feuillet le plus récent quand la ligne est identique d'une semaine à l'autre.

Fichier test.xlsx

[MarcelG]

Bonjour Antoine et bienvenu sur le Forum,

Comment définis-tu des lignes identiques?
1 seule cellule? laquelle?
Tous les champs de la ligne?

Comment définis-tu le feuillet (ou plutôt la feuille de travail) le plus récent?

[AntoineBQ]

Bonjour Marcel,

Le feuillet le plus ancien est le 'S02', le plus récent 'S03' (ce sont les numéros de la semaine).

L'idée, c'est qu'Excel rentre dans le feuillet le plus récent, lise la première ligne (A2:O2) et va rechercher dans le feuillet plus ancien si la ligne y est déjà.

SI OUI : copier-coller le commentaire du fournisseur (colonne P) de la ligne 'S02' dans 'S03'
SI NON : passer à la ligne suivante.

J'ai essayé avec du =RECHERCHEH, des macros 'Do Loop', 'Do While'... rien.

En terme de code :

Si 'S03'!A2:O2 est égal à 'S02'!A2:O2' alors
'S02'!P2 = 'S03'!P2
sinon (passer à la ligne suite du feuillet 'S02'!)
Tant que 'S03!' n'a pas comparé toutes ses lignes avec 'S02!', la boucle continue

Et l'idée est d'automatiser toujours cela, surtout quand je serai à la semaine S42.

[MarcelG]

salut,

et va rechercher dans le feuillet plus ancien si la ligne y est déjà.

Tu n'as répondu qu'à une partie des questions posées.
Pour tester 2 lignes identiques, la vérification doit-elle porter sur
- un champ (exemple n° de commande)
- plusieurs champs (exemple n) de commande et code article)
- tous les champs (de code Agence à Année)
?

[AntoineBQ]

La comparaison doit se porter sur tous les champs. Excel doit vraiment comparer une ligne à une autre avec tous les champs (de 'Agences' à 'Année').

[MarcelG]

Salut Antoine.

Pour ce code:
J'ai considéré l'égalité de la concaténation de tous les champs des colonnes A à O (de la colonne 1 à la colonne 15)
Si l'égalité des lignes balayées est constatée, alors le commentaire s'ajoute en colonne P (colonne 16)

Attention! Code éventuellement restant à adapter.
J'ai considéré 2 feuilles S02et S03.
Si une feuille seule est à compléter au niveau du commentaire, alors la procédure pourrait être argumentée.
Ceci en lieu et place du balayage des feuilles (variable n)

Voici, Voilou.

Procédure.

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
Option Explicit
 
Public Sub ajout_comment()
 
Dim n As Byte
Dim wkvide As Worksheet, wkpréc As Worksheet
Dim i As Integer, dernvide As Integer
Dim j As Integer, dernpréc As Integer
 
'Balayage de la feuille S03 à la feuille S52
For n = 3 To ThisWorkbook.Worksheets.Count + 1
        'Définition de la feuille à complter
        Set wkvide = Worksheets("S" & Format(n, "0#"))
        With wkvide
                dernvide = .Cells(.Rows.Count, 1).End(xlUp).Row
        End With
        'Définition de la feuille précédente d'où reporter le commentaire
        Set wkpréc = Worksheets("S" & Format(n - 1, "0#"))
        With wkpréc
                dernpréc = .Cells(.Rows.Count, 1).End(xlUp).Row
        End With
        'Balayage de toutes les lignes à compléter
        For i = 2 To dernvide
                'Balayage de toutes les lignes d'où reporter le commentaire éventuel
                For j = 2 To dernpréc
                        'Test égalité concaténation - Voir fonction personnalisée ci-dessous.
                        If concat(wkvide, i) = concat(wkpréc, j) Then
                                'Information du commentaire
                                wkvide.Cells(i, 16).Value = wkpréc.Cells(j, 16).Value
                                Exit For
                        End If
                Next j
        Next i
        'libération des variables Worksheet
        Set wkvide = Nothing
        Set wkpréc = Nothing
Next n
 
End Sub

Fonction de concaténation:

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
Public Function concat(lawks As Worksheet, laligne As Integer) As String
 
Dim lachaine As String
Dim col As Byte
 
'Initialisation de la chaine de concaténation
lachaine = ""
 
'Concaténation de  la colonne A à la colonne O
For col = 1 To 15
        lachaine = lachaine & lawks.Cells(laligne, col)
Next col
 
concat = lachaine
 
End Function

[AntoineBQ]

C'est incroyable, moi qui pensais qu'une simple boucle en 'Tant que' aurait suffit.

Merci beaucoup. Ainsi, qu'importe le nombre de feuillet, ces lignes de codes travaillerons de la même façon ?

Dernière requête, puis-je installer ce code sous un bouton de contrôle de formulaire ? Je travaille avec des gens n'ayant pas de formation Excel, donc il faut que cela soit simple d'utilisation.

[MarcelG]

ces lignes de codes travaillerons de la même façon ?

Oui.
Comme je te l'ai indiqué, l'exécution du code peut être restreint à la dernière feuille.
Il faudrait alors ajouter à la procédure un argument Worksheet, ou String pour désigner son nom.

puis-je installer ce code sous un bouton de contrôle de formulaire

Bien sûr.
Pour ma part, je préfère isoler les procédures.
Autrement dit, j'ai l'habitude d'appeler une procédure plutôt que de lier exclusivement un code à l'activation d'un bouton.
Ici.

Code :

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

1
2
3
Private Sub Btn_Commentaire_Click()
Call ajout_comment
End Sub

(Toujours bien nommer ses contrôles)

[AntoineBQ]

Comme je te l'ai indiqué, l'exécution du code peut être restreint à la dernière feuille.
Il faudrait alors ajouter à la procédure un argument Worksheet, ou String pour désigner son nom.

Bien au delà de mes compétences. Je n'ai vraiment que les bases de la macro Excel. Mes lignes de codes à l'université n'était pas plus grande que 10 lignes et il fallait juste programmer des (si) et des boucles.

Cependant, je vois ce qu'est 'Worksheet' et 'String', mais où les placer dans la lignes de codes pour que cela soit restreint à la dernière feuille...

Pour ma part, je préfère isoler les procédures.
Autrement dit, j'ai l'habitude d'appeler une procédure plutôt que de lier exclusivement un code à l'activation d'un bouton.
Ici.

Qu'est-ce que c'est ce terme "d'isolation des procédures" ? Par exemple, j'ai crée le bouton dans le feuillet 'S03', copier les 2 codes l'un après l'autre (cela fonctionne très bien !).

[MarcelG]

Je n'ai vraiment que les bases de la macro Excel

Rien de grave. Nous sommes,pour beaucoup, passés par ce stade.

Cependant, je vois ce qu'est 'Worksheet' et 'String', mais où les placer dans la lignes de codes pour que cela soit restreint à la dernière feuille...

Une procédure peut recevoir un argument qui en définit l'action.
Exemple

Code :

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

1
2
3
Private Sub Btn_Commentaire_Click()
Call ajout_comment("S03")
End Sub

Cette dernière procédure est appelée pour la feuille S03.
"S03" est le nom de la feuille argument de la procédure ajout_comment.

On écrira donc

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
Option Explicit
 
Public Sub ajout_comment(nomfeuille As String)
 
Dim wkvide As Worksheet, wkpréc As Worksheet
Dim i As Integer, dernvide As Integer
Dim j As Integer, dernpréc As Integer
 
Set wkvide = Worksheets(nomfeuille)
With wkvide
        dernvide = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
'Définition de la feuille précédente d'où reporter le commentaire
Set wkpréc = Worksheets("S" & Format(Right(nomfeuille, 2) - 1, "0#"))
With wkpréc
        dernpréc = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
'Balayage de toutes les lignes à compléter
For i = 2 To dernvide
        'Balayage de toutes les lignes d'où reporter le commentaire éventuel
        For j = 2 To dernpréc
                'Test égalité concaténation - Voir fonction personnalisée ci-dessous.
                If concat(wkvide, i) = concat(wkpréc, j) Then
                        'Information du commentaire
                        wkvide.Cells(i, 16).Value = wkpréc.Cells(j, 16).Value
                        Exit For
                End If
        Next j
Next i
'libération des variables Worksheet
Set wkvide = Nothing
Set wkpréc = Nothing
 
End Sub

De même, l'argument de la procédure aurait pu être un objet Worksheet.

Code :

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

1
2
3
Private Sub Btn_Commentaire_Click()
Call ajout_comment(Worksheets("S03"))
End Sub

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
Option Explicit
 
Public Sub ajout_comment(lafeuille As worksheet)
 
Dim wkpréc As Worksheet
Dim i As Integer, dernvide As Integer
Dim j As Integer, dernpréc As Integer
 
With lafeuille
        dernvide = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
'Définition de la feuille précédente d'où reporter le commentaire
Set wkpréc = Worksheets("S" & Format(Right(lafeuille.name, 2) - 1, "0#"))
With wkpréc
        dernpréc = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
'Balayage de toutes les lignes à compléter
For i = 2 To dernvide
        'Balayage de toutes les lignes d'où reporter le commentaire éventuel
        For j = 2 To dernpréc
                'Test égalité concaténation - Voir fonction personnalisée ci-dessous.
                If concat(lafeuille, i) = concat(wkpréc, j) Then
                        'Information du commentaire
                        lafeuille.Cells(i, 16).Value = wkpréc.Cells(j, 16).Value
                        Exit For
                End If
        Next j
Next i
'libération variable Worksheet
Set wkpréc = Nothing
 
End Sub

Pour une illustration, tu peux consulter,
Dans le précieux espace de Jacques Boisgontier, les procédures avec paramètres
Pour une vue plus générale, l'aide Microsoft sur les procédures argumentées

Qu'est-ce que c'est ce terme "d'isolation des procédures" ? Par exemple, j'ai crée le bouton dans le feuillet 'S03', copier les 2 codes l'un après l'autre (cela fonctionne très bien !).

Je voulais dire ceci.
Au lieu d'écrire

Code :

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

1
2
3
4
Private Sub Btn_commande_Click()
'Tout le code de la procédure
.......
End Sub.

Il est préférable, à mon avis, d'appeler une procédure écrite par ailleurs

Code :

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

1
2
3
Private Sub Btn_commande_Click()
Call ajout_comment
End Sub

Ce qui permet une plus large utilisation de la procédure qui peut être ici appelée au sein du projet VBA dans son intégralité (Depuis une autre procédure, un autre formulaire, etc...)

N'hésite pas à revenir si ce n'est pas clair.

[AntoineBQ]

Très bien, merci pour toutes ces explications et les liens d'informations.

A ce jour, j'ai testé sur mes fichiers Excel ces codes, cela fonctionne très bien. J'ai eu un bug quand les feuillets se nommaient 'S2' au lieu de 'S3'. J'ai changé les noms.

Dans l'ensemble, j'ai crée un module VBA et j'y ai collé les 2 codes publiés par vous à 13:42, succinctement. J'ai crée un bouton, y ait initialiser la macro, cela fonctionne toujours.

Cependant, dans ces fichiers de relance, on a également d'autres feuillets à la suite des semaines ('Synthèse', 'Actions', 'Autres'). La macro fonctionne tout de même malgré une fenêtre pop-up de bug (ce qui n'empêche, à priori, pas le code de s'exécuter).

Ma question est : est-ce que je peux créer un bouton dans l'onglet 'Autres' et quand j'appuie dessus, il fait le travail ? L'objectif est de standardiser le processus pour mes collègues, qu'ils n'aient pas de questions à se poser et qu'ils aient juste à cliquer sur le bouton, qui fera le job.

[MarcelG]

Salut,

Cependant, dans ces fichiers de relance, on a également d'autres feuillets à la suite des semaines

Dans ce cas, il faudrait tester le nom de la feuille (propriété Like)

Exemple

Code :

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

1
2
3
4
5
6
7
8
Sub essai_specialcells()
Dim wk As Worksheet
For Each wk In Worksheets
  With wk
    If .Name Like "S*" Then Debug.Print .Name
  End With
Next wk
End Sub

Cette précaution considérée, oui, bien sûr, tu peux créer ce bouton.
Mais je te conseille d'appeler la procédure dans le code qui lui est associé, plutôt que toute la procédure, comme je l'ai expliqué précédemment.

Pour ma part, tout applicatif s'ouvre automatiquement (évènement Open), si ce n'est sur un formulaire, sur une page d'accueil où se trouvent touts les boutons associés au projet.
J'y ajoute le nom de l'applicatif (propriété Name), sa date de modification (au moment de sa sauvegarde) et son chemin complet (propriété Path).

[AntoineBQ]

D'accord, merci. Je crois que je me suis emmêler les pinceaux avec toutes ces lignes de codes.

Je viens de me rappeler d'une chose, je peux intégrer un code dans une feuille ? et avec 'Open', celui-ci peux s'exécuter tout seul après chaque modifications sur le fichier ?

Dans l'idéal, mon fichier Excel a plusieurs feuillets : [S02, S03, Synthèse, Actions, Autres]. Quand j'ajoute un feuillet entre [S03] et [Synthèse], le code se relancera ?

[MarcelG]

et avec 'Open', celui-ci peux s'exécuter tout seul après chaque modifications sur le fichier

L'évènement Open, s'il est géré, active une ou plusieurs actions (procédure(s), formulaire....) à l'ouverture du classeur

Il existe bien l'évènement Newsheet.
A tester.
Au demeurant, je préfère lancer les procédures à l'ouverture du classeur (Open) ou à sa sauvegarde (BeforeSave).

Tu peux consulter les évènements (Events) sur ces espaces
Objet Worksheet
Objet Workbook

[AntoineBQ]

Ecoutez, merci beaucoup pour tout cela.

J'ai organiser une réunion de "formation" Excel ce matin avec mes collègues pour leur expliquer cela (le code, son utilité, etc.). Ils sont soulager de ne plus rechercher des similitudes sur plus de 200 lignes pour certains fournisseurs.

Cependant, ils ne souhaitent pas que cela se lance automatiquement et préfère le lancer par eux-même. Ainsi, j'ai inséré le code dans le feuillet 'Synthèse', celui arrivant juste après les feuillets de semaine. Le code semble bien s'activer mais il y a ce code erreur (voir ci-dessous).

[MarcelG]

Bonjour Antoine,

Quelles sont les feuilles de ton classeur concernées. Toujours S02 et S03?
Y a-t-il un autre classeur ouvert simultanément à cet applicatif?

Le code du bouton de commande doit appeler une procédure.
Effectue l'exécution de ce code pas à pas et retourne la ligne, surlignée généralement en jaune, qui pose problème.

Reviens avec tous ces éléments.
(On va y arriver)

[AntoineBQ]

Marcel,

Quelles sont les feuilles de ton classeur concernées. Toujours S02 et S03?

Toujours 'S02' et 'S03' pour ce cas présent. Il faut penser qu'à terme, il y aura 52 feuillets.

Y a-t-il un autre classeur ouvert simultanément à cet applicatif?

Non.

Je vais vous expliquer comment j'ai fait avec un autre fichier (.xlmx) vierge de tous codes macro. Cela vous facilitera probablement la compréhension de comment j'utilise les macros.
1. Clique droit sur 'Synthèse', "Visualiser le code" ;
2. Copie-coller des codes donnés à 13:42, hier ;
3. "F5" (ça fonctionne mais ça affiche un pop-up erreur) ;
4. "F8" (pour le pas-à-pas) et toutes les lignes s'affichent en jaune de façon succincte sans blocage (j'en conclue que ça fonctionne ?).

-

[MarcelG]

Antoine,

Code :

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

F5" (ça fonctionne mais ça affiche un pop-up erreur) ;

Je me répète. Sur quelle ligne?

j'ai fait avec un autre fichier (.xlmx) vierge de tous codes macro

Il y a donc 2 classeurs ouverts!?

Pourquoi ne pas considérer le seul classeur où s'exécute la procédure en plaçant un bouton de commande sur une feuille annexe, comme indiqué hier.

Si tu as affecté le code à une feuille, alors celui-ci, je pense, devrait être lié à un évènement (Activate...)

Par précaution, tu peux rattacher les objets Worksheet au classeur où est reporté le code, soit Thisworkbook.

[AntoineBQ]

Bonjour Marcel,

Hier soir, j'ai tout repris depuis 0. Faire du code en entreprise s'avère assez compliqué, j'ai préféré refaire ça chez moi au calme.

Je me suis mis en condition du réel, avec un vrai classeur Excel que moi et mon équipe allons utiliser. Celui-ci se compose ainsi :

Code :

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

S02 - S03 - Synthèse - Actions - Feuil4

Dans Feuil4, j'y ai crée un bouton (comme conseillé par vous, et plus facile à utiliser pour mes collègues). Je lui ai donné un nom "Copier-coller automatique" et je lui ai affecté une macro ajout_commentaire que vous m'aviez envoyé (cf. image).

Code :

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

1
2
3
Private Sub Btn_commande_Click()
Call ajout_comment
End Sub

Mon soucis à présent, quel code, parmi tout ceux partagés, dois-je rentrer sous le nom 'ajout_comment' ?

[MarcelG]

Bonjour Antoine,

Pas de problème
Tu peux te reporter au post #10, et consulter ce que je t'ai reporté quant aux procédures argumentées, y compris les liens indiqués.