Copier / Remplacer / Ajouter entre 2 feuilles

Bonjour à tous,

Etant néophyte et curieux dans le VBA, je travail sur un projet de gestion de base de donnée avec une grosse population de personnes.

Je souhaiterais transférer certaines colonnes de la feuille MAJ vers la feuille GENERAL sous certaines conditions avec un bouton TRANSFERT sur la feuille MAJ.

Voici la situation ci dessous :

Feuille MAJ

ABCDEFGHIJK
DomaineSous-DomaineSous-Sous-DomaineRef clientNom PrenomContratStatutDepartementAdresseCode postalSite
DomaineSous-DomaineSous-Sous-DomaineRef clientNom PrenomContratStatutDepartementAdresseCode postalSite
DomaineSous-DomaineSous-Sous-DomaineRef clientNom PrenomContratStatutDepartementAdresseCode postalSite
............................................

Feuille GENERAL

ABCDEFGH....AA
Ref clientNom PrenomDomaineSous-DomaineSous-Sous-DomaineAdresseSite........Statut
Ref clientNom PrenomDomaineSous-DomaineSous-Sous-DomaineAdresseSite........Statut
Ref clientNom PrenomDomaineSous-DomaineSous-Sous-DomaineAdresseSite........Statut
........................................

Conditions :

  • Transférer si valeurs en D de la feuille MAJ existe en A de la feuille GENERAL.
  • Si VRAI alors transférer (MAJ ==> GENERAL) avec cette logique : D ==> A ; E ==> B ; A ==> C ; B ==> D ; C ==> E ; I ==> F ; K ==> G.
  • Si FAUX alors rajouter les nouveaux de la feuille MAJ à la suite des autres sur la feuille GENERAL en gardant la même transposition que précédemment.
  • Si la valeurs en D de MAJ existe déjà dans GENERAL, alors afficher sur la ligne correspondante dans AA de GENERAL le statut "OK"
  • Si la valeurs en D de MAJ n'existe pas dans GENERAL, alors afficher sur la ligne correspondante dans AA de GENERAL le statut "PARTI" et colorier les cellules de A à G (sur GENERAL) de la ligne correspondante en gris clair
  • Et pour les nouveaux qui ont été rajouté précédemment, mettre le statut "NOUVEAU".

Nouvelle Feuille GENERAL (résultat voulu)

ABCDEFGH....AA
Ref clientNom PrenomDomaineSous-DomaineSous-Sous-DomaineAdresseSite........OK
Ref clientNom PrenomDomaineSous-DomaineSous-Sous-DomaineAdresseSite........PARTI
Ref clientNom PrenomDomaineSous-DomaineSous-Sous-DomaineAdresseSite........OK
........................................
Ref clientNom PrenomDomaineSous-DomaineSous-Sous-DomaineAdresseSite........NOUVEAU
10test1.xlsm (15.58 Ko)

Je vous remercie d'avance de l'aide que vous pourriez m'apporter, je reste à l'écoute de toutes propositions que vous pourriez me faire.

Edit : fichier joint

Bonjour,

Fichier nécessaire !

Et préciser si la refClient est unique (pas de doublon sur ce champ) aussi bien dans MAJ que dans GENERAL.

Cordialement.

Bonjour MFerrand,

Merci de ta réponse.

Je viens tout juste de joindre un exemple de fichier dans l’énoncé "TEST1" car l'original est beaucoup trop lourd..

Et la Réf client est unique sur chaque feuille, il ne peut pas y avoir de doublons. Les valeurs de MAJ doivent remplacer celles de GÉNÉRAL si la réf client est identique.

Re,

Private Sub ButtonTRANSFERT_Click()
    Dim d As Object, aa, k, i&, n&
    Set d = CreateObject("Scripting.Dictionary")
    aa = Me.Range("A1").CurrentRegion.Value
    For i = 2 To UBound(aa)
        d(aa(i, 4)) = WorksheetFunction.Index(aa, i, Array(4, 5, 1, 2, 3, 9, 11))
    Next i
    With Worksheets("GENERAL")
        aa = .Range("A1").CurrentRegion.Resize(, 1).Value
        n = UBound(aa) + 1
        For i = 2 To UBound(aa)
            k = aa(i, 1)
            If d.exists(k) Then
                .Cells(i, 1).Resize(, 7).Value = d(k)
                .Cells(i, 27) = "OK": d.Remove k
            Else
                .Cells(i, 27) = "PARTI"
            End If
        Next i
        If d.Count > 0 Then
            For Each k In d.keys
                .Cells(n, 1).Resize(, 7).Value = d(k)
                .Cells(i, 27) = "NOUVEAU": n = n + 1
            Next k
        End If
        .Activate
    End With
End Sub

Ton bouton étant un ActiveX, la procédure se trouve donc dans le module de la feuille MAJ.

Pour tester, il te suffit d'appuyer sur le bouton.

La méthode n'étant pas tout à fait évidente si tu es vraiment néophyte, explications sur demande... N'hésite pas.

Cordialement.

Re MFerrand,

Je ne sais pas comment te remercier ça fait si longtemps que j'essaie

Alors oui ton code n'est pas évident, mais il fonctionne bien en parti car je l'ai essayé dans le fichier maître et j'ai constaté un oubli

En effet, j'ai remarqué que le statut "NOUVEAU" s'applique à la première ligne mais pas au suivante dans le cas où on a plus d'un nouveau.

Ensuite par rapport à ton code je suis curieux de savoir comment fonctionne chaque lignes, je n'ai encore jamais vu cette façon de procéder

Bonsoir,

Une coquille de ma part ! Désolé.

Remplace : .Cells(i, 27) = "NOUVEAU" par .Cells(n, 27) = "NOUVEAU"

Erreur de variable...

Je reviens pour les explications, mais les 2 particularités sont l'utilisation de l'outil dictionnaire, assez répandue, et l'utilisation de la fonction INDEX d'excel, moins répandue.

Cordialement.

Re,

Un dictionnaire est une collection d'éléments identifiés par une clé, clé sans doublon dans le dico, et une valeur (ou item) associée à cette clé. Ce composant est très utilisé en raison de sa rapidité à l'exécution.

On a donc dans ton fichier une série de données en lignes, bénéficiant d'un identifiant constitué par la ref Client (feuille MAJ) destinées à mettre à jour une base (GENERAL), chaque ligne de données à mettre à jour étant repérée par la même ref Client.

Donc méthode générale : si on prélève les données MAJ sous forme de dictionnaire dont les clés sont constituées par la ref Clent, on pourra une fois muni de ce dico passer en revue la base GENERAL pour savoir à chaque ligne si on dispose d'un élément dico, auquel cas on opére la mise à jour, et on met OK en AA, et si on n'en dispose pas on mettra PARTI en AA ; si on prend soin de supprimer les éléments dico OK, à la fin il nous restera les éléments dico NOUVEAU, et on les rajoute à la base.

Reprenons ces ceux phases dans le détail.

1re phase : constituer le dictionnaire.

  • On fait appel au composant Microsoft au moyen de la fonction CreateObject pour générer une instance de Dictionary affectée à une variable.
  • Pour y ajouter des éléments, on doit parcourir la feuille MAJ : comme il est plus rapide de parcourir un tableau qu'une feuille de calcul, on prélève les données MAJ qui sont sous forme de tableau pour les affecter (les valeurs) à une variable de type Variant dans laquelle on obtiendra un tableau à deux dimensions de base 1
    aa = Me.Range("A1").CurrentRegion.Value

On utilise CurrentRegion à partir de A1 pour définir le tableau de données à prélever (lorsqu'on peut l'utiliser, cette méthode dispense de rechercher la dernière ligne pour adresser la plage à définir).

  • Et on le parcourt de 2 (1 étant une ligne d'en-tête) à la dernière ligne (UBound(aa)).
  • Pour chaque ligne la clé d'élément dico à générer figurant en colonne 4, on va donc affecter l'élément d(aa(i, 4)). L'invoquer le crée automatiquement. On n'a donc plus qu'à lui associer une valeur.

- Notre "valeur" est constituée par 7 colonnes de MAJ, dont les valeurs se retrouvent dans les 7 premières colonnes de GENERAL, mais pas dans le même ordre.

Les 7 premières colonnes de GENERAL constituent par ligne une plage à alimenter par les colonnes de MAJ dans cet ordre : 4, 5, 1, 2, 3, 9, 11.

Il faut donc récupérer cette suite de valeurs par ligne de MAJ en les extrayant du tableau aa. VBA ne dispose pas d'instrument pour extraire une ligne d'un de ses tableaux, encore moins pour réaliser une extraction unique d'une partie discontinue de la ligne en plaçant les valeurs prélevées dans un autre ordre... On serait donc amené à extraire ces valeurs une par une.

Mais VBA peut utiliser les fonctions Excel et c'est là qu'intervient la fonction INDEX, qui offre des ressources méconnues dans son usage habituel sur le tableur. INDEX peut renvoyer une valeur d'un tableau à partir de coordonnées ligne, colonne, ce qu'on sait et utilise fréquemment, mais la fonction peut également renvoyer des matrices : un index ligne à 0, et c'est la colonne entière désignée par l'index colonne qui sera renvoyé sous forme de matrice ; un index colonne à 0, la ligne entière désignée par l'index ligne sera renvoyée. Et si on remplace le 0 par une liste de colonnes (tableau), ce sont ces colonnes qui seront renvoyées dans l'ordre de la liste.

Avec INDEX, on peut donc obtenir une matrice (ou tableau) à une dimension constituée de valeurs provenant de colonnes dans l'ordre où on les a listées.

Et comme une valeur de dico accepte un tableau, nous allons affecter à l'élément dico de chaque ligne, une ligne de valeurs telles qu'elle doit être définie pour GENERAL.

    For i = 2 To UBound(aa)
        d(aa(i, 4)) = WorksheetFunction.Index(aa, i, Array(4, 5, 1, 2, 3, 9, 11))
    Next i

Et voilà notre dico constitué !

2e phase : mise à jour de GENERAL.

- Comme précédemment on va devoir parcourir les lignes de GENERAL. On va donc faire le même prélèvement que pour MAJ en réutilisant notre variable aa.

Mais comme on n'a besoin de parcourir que les ref Client, on va réduire notre prélèvement sous forme de tableau à la colonne A, en ciblant la région courante de A1 redimensionnée à 1 colonne :

    With Worksheets("GENERAL")
        aa = .Range("A1").CurrentRegion.Resize(, 1).Value

- On a mis simultanément la feuille sous instruction With, et on y reste car on va devoir intervenir simultanément sur la feuille.

Notons que notre tableau aa représentant la col. A à partir de A1, les indices du tableau correspondront aux ligne de la feuille.

- Au passage on enregistre dans une variable la taille du tableau (son nombre de ligne) +1 dans une variable n.

n sera le numéro de la première ligne où sera affecté un élément NOUVEAU s'il y a lieu.

- On opère une boucle de 2 à la fin sur toutes les lignes du tableau aa, contenant les ref Client.

On affecte la valeur à une variable k pour commodité d'utilisation ultérieure.

On teste si un élément dico d(k) existe.

Si c'est le cas on redimensionne une plage ligne de 7 colonnes à partir de A à laquelle on affecte la valeur de d(k). [La valeur de d(k) est un tableau de 7 éléments à une dimension. Un tel tableau affecté à une plage est horizontal, on peut l'affecter directement à une plage-ligne. Il faudrait le transposer pour l'affecter à une plage-colonne.]

Et on place OK en col. 27, puis on détruit d(k).

Si ce n'est pas le cas, on se contente de placer PARTI en colonne 27.

        For i = 2 To UBound(aa)
            k = aa(i, 1)
            If d.exists(k) Then
                .Cells(i, 1).Resize(, 7).Value = d(k)
                .Cells(i, 27) = "OK": d.Remove k
            Else
                .Cells(i, 27) = "PARTI"
            End If
        Next i

A ce stade on a mis à jour les éléments existants de GENERAL. S'il nous reste des élément dico, ceux-ci sont à insérer comme NOUVEAU à partir de la ligne n, variable déjà préparée pour la première ligne vide et qu'on incrémentera après traitement de chaque élément dico restant pour le suivant.

On parcourt les clés de dico au moyen d'une boucle...

        If d.Count > 0 Then
            For Each k In d.keys
                .Cells(n, 1).Resize(, 7).Value = d(k)
                .Cells(n, 27) = "NOUVEAU": n = n + 1
            Next k
        End If

La feuille GENERAL étant restée non visible durant l'opération, on l'active alors pour présenter le résultat à l'utilisateur.

Cordialement.

Bonjour MFerrand,

Quand je me suis levé ce matin et que j'ai vu ton roman, ça a tout de suite embelli ma journée

Merci énormément pour tes explications bien précise, cela va beaucoup m'aider pour mes futurs projets.

Je n'ai pas encore eu l'occasion de tester ta modification de code, mais je me doute que cela fonctionnera correctement.

Encore merci pour ton boulot et pour le temps que tu m'as consacré. Si je pouvais te mettre une appréciation sur ton profil, elle serait excellente.

Bonne continuation à toi.

Rechercher des sujets similaires à "copier remplacer ajouter entre feuilles"