Correspondance de colonne

[jhlamoustache]

Bonjour à tous

Issu d'une conversation:

Bonjour Dranreb,

je dois développer une procédure qui mettra à jour une feuille (Destination) mensuellement environ à partir de données extraites sur une autre feuille (Source). L'utilisateur ordonnera ses colonnes comme il le souhaite. Pour développer mon code, j'ai l'habitude d'utiliser des tableaux. Je me dois donc de trouver une procédure qui permette de de faire correspondre le champs de la feuille Source avec ceux de la feuilles destination. De plus je ne suis pas sûr qu'au fil du temps l'ordonnancement des champs de la feuille Source soit toujours le même. J'ai lu qu'il fallait utiliser un dictionnaire, et faire du "mapping", mais je ne sais pas bien comment m'y prendre. Pourriez-vous m'aider svp ?

Ce à quoi Dranreb a répondu.

Si la source est sous forme de tableau les ListColumn du ListObject qui le représente on pour identifications les titres des colonnes, et pas besoin de mapping.
Sinon :
Code (Visual Basic):
Dim DicTit As New Dictionary, TTit(), C As Long
TTit = ActiveSheet.[A1:Z1].Value
For C = 1 To 26
DicTit(TTit(1, C)) = C
Next C

J'ai donc adapté le code, mais je ne vois pas comment je vais modifier les valeurs (jaunes sur Excel) au bon endroit dans le tableau TabD avant de le recoller sur la feuille "Destination"
NB : En principe je devrai passer par un tableau transposé pour ajouter les nouvelles lignes.
Merci de votre aide

 

[Dranreb]

Ben la première chose que je vois c'est que le SsGr s'appelle Voy, ce qui me semble indiquer que le regroupement entre les même éléments des deux situations se faisait sur le numéro de voyage, or il n'est plus en colonne 1, désormais mais en colonne 6, or on demande toujours en dernier paramètre de la fonction Gigogne de faire le regroupement sur la colonne 1, qui est donc l'immatriculation véhicule …
Pas besoin de spécifier les plages entières à la fonction TableUnique: ellel se débrouille très bien avec la 1ère ligne seulement.

 

[jhlamoustache]

Oui, j'ai constaté des anomalies si je déplaçais des colonnes dans la feuille source. Mais en rétablissant les voyages dans la colonne 1 dans Destination, tout à l'air de bien fonctionner. Que l'on déplace des colonnes dans Destination sauf colonne 1 et les "indissociables" et/ou dans Source.
pour la TableUnique, vous aviez spécifié A2:M2 ?
J'ai inactivé la ligne de code qui efface les valeurs anciennes (cas 1)
Autre question, Est-il possible de préparer des mises en forme dans TRésu comme couleur de cellule, ou modifier la police ?

 

[Dranreb]

Je ne parle pas de la feuille Source mais de la feuille Destination. Rappel: c'est elle qui fait la loi ! Le DétMvt suit automatiquement (les colonnes …ancienne valeur y sont vides bien sûr puisqu'elles n'existent pas dans la feuille source).
Si vous vouliez mettre le voyage en colonne 6 il fallait mettre 6 comme dernier paramètre de Gigogne, c'est tout.
Non, pas possible de préparer des mise en forme. Ce ne sont que des valeurs de cellules.
Ce qui serait logique quand ça n'existe que dans la destination ce serait que tout disparaisse des colonnes qui seraient en principe venues de DétMvt, sauf celles qui peuvent passer du coté anciennes valeurs.
Par ailleurs votre code du cas ou ça existe des deux cotés n'est pas bon du tout. Remettez le à peu près comme il était avant en vérifiant simplement si les numéros de colonnes sont les bons.

 

[Dranreb]

Si vous tenez aux numéros de colonnes non figés et si on peut se fier aux titres finissant toujours par "ancienne valeur" comme suivant toujours immédiatement la nouvelle valeur correspondante, il y a ce code qui a l'air de marcher pas mal :

Sub MàJ()
Dim CMax As Long, TSrc(), C As Long, CVoy As Long, DicTitSrc As New Dictionary, TTit(), TMvt(), N As Long, _
   TCNV() As Long, TRésu(), Voy As SsGr, L As Long, Détail, DétMvt(), DétDst(), Cas
TSrc = Feuil2.UsedRange.Rows(1).Value
For C = 1 To UBound(TSrc, 2): DicTitSrc(TSrc(1, C)) = C: Next C
TSrc = ColUti(Feuil2.Cells(2, 1).Resize(, UBound(TSrc, 2))).Value
TTit = Feuil1.UsedRange.Rows(1).Value
ReDim TMvt(1 To UBound(TSrc, 1), 1 To UBound(TTit, 2))
For C = 1 To UBound(TTit, 2)
   If DicTitSrc.Exists(TTit(1, C)) Then
      GarnirColonne TMvt, C, TSrc, DicTitSrc(TTit(1, C))
      If TTit(1, C) = "Voyage" Then CVoy = C
   ElseIf TTit(1, C) Like "*ancienne valeur" Then
      N = N + 1: ReDim Preserve TCNV(1 To N): TCNV(N) = C - 1
      End If
    Next C
ReDim TRésu(1 To 5000, 1 To UBound(TTit, 2))
For Each Voy In Gigogne(TableUnique(Feuil1.Cells(2, 1).Resize(, UBound(TTit, 2)), TMvt), CVoy)
   L = L + 1: Cas = 0
   For Each Détail In Voy.Co
      If Détail(0) = 0 Then DétDst = Détail: Cas = 1 Else DétMvt = Détail: Cas = Cas Or 2
      Next Détail
   Select Case Cas
      Case 1: ' N'existe qu'en Dst
         For C = 1 To UBound(DétDst): TRésu(L, C) = DétDst(C): Next C
         For N = 1 To UBound(TCNV): C = TCNV(N)
            TRésu(L, C + 1) = DétDst(C): TRésu(L, C) = Empty: Next N
      Case 2: ' N'existe qu'en Mvt
         For C = 1 To UBound(DétMvt): TRésu(L, C) = DétMvt(C): Next C
      Case Else: 'Existe des deux cotés
         For C = 1 To UBound(DétMvt): TRésu(L, C) = DétMvt(C): Next C
         For N = 1 To UBound(TCNV): C = TCNV(N)
            If DétMvt(C) <> DétDst(C) Then TRésu(L, C + 1) = DétDst(C)
            Next N: End Select: Next Voy
Feuil3.[A10].Resize(5000, UBound(TRésu, 2)).Value = TRésu ' (emplacement provisoire, en définitive Feui1.[A2])
End Sub

 

[jhlamoustache]

Bonjour Bernard,
C'est excellent. Ca a l'air de fonctionner dans toutes les configurations.
Like est sensible à la casse n'Est-ce pas? Mettre lcase(TTit(1, C)) conviendrait?
Pour les mises en forme, ce n'est qu'une solution. Le problème est : l'utilisateur doit repérer facilement d'une façon les voyages préexistants dans destination (repère sur le voyage lui-même et la ou les anciennes valeurs) (MEF jaune dans mon exemple), et les nouveaux voyages (MEF vert). Je m'aperçois qu'il faut que j'ajoute une colonne date de traitement dans Destination. Donc la MEF par couleur n'est peut-être pas la solution la plus pertinente. Si vous connaissez une astuce je suis preneur.

 

[jhlamoustache]

Voilà, j'ai ajouté une colonne date de traitement dans Destination, et j'ai nommé la première cellule DT. La macro est MAJ2.
Il semble que j'obtienne le résultat voulu. Le voyage 1 n'est pas modifié pas de date, le voyage 2 est modifié, la date est ajoutée. pour les 3 nouveaux voyages la date est ajoutée.

 

[jhlamoustache]

Ah non, au deuxième traitement, je perds les dates

 

[Dranreb]

Bonjour.
Vous n'allez pas chaque fois interroger Excel pour obtenir le numéro de la colonne de la date de traitement quand même ?!
Déclarez plutôt une variable CDTr As Long et initialisez la comme pour la CVoy :

   ElseIf TTit(1, C) = "Date Traitement" Then
      CDtr = C

Gardez la boucle simple sur une ligne qui transfère tout et corrigez les détails après :

      Case 2: ' N'existe qu'en Mvt
         For C = 1 To UBound(DétMvt): TRésu(L, C) = DétMvt(C): Next C
         TRésu(L, CDtr) = Date
      Case Else: 'Existe des deux cotés
         For C = 1 To UBound(DétMvt): TRésu(L, C) = DétMvt(C): Next C
         TRésu(L, CDtr) = DétDst(CDtr)
         For N = 1 To UBound(TCNV): C = TCNV(N)
            If DétMvt(C) <> DétDst(C) Then TRésu(L, C + 1) = DétDst(C): TRésu(L, CDtr) = Date
            Next N: End Select: Next Voy

 

[jhlamoustache]

J'ai modifié comme indiqué. OK
Mais il y a deux anomalies :
1 A la première itération ça fonctionne, mais à la deuxième, s'il existe des valeurs anciennes dans Destination, elles sont effacées. Il faudrait qu'on les conserve.
2 Lorsque Cas =1 , alors les valeurs sont effacées, et reportées à droite. Pourquoi pas ? Cette situation ne devrait jamais se produire en principe. Je vais voir avec les collègues ce qu'ils souhaitent en pareil cas. Mais cela pourrait être dû également à une erreur commise dans la requête d'extraction. J'ai ajouté la ligne de code pour ajouter la date de traitement dans ce cas.

 

[jhlamoustache]

Pour ce qui est des MEF, vu que l'utilisateur effectuera des tri, les MEF doivent être prises en photos avant MAJ des voyages, puis resituées sur les bonnes cellules. Car je constate que les voyages se retrouvent ordonnés. Ensuite faire un traitement sur les variant, nouveaux ou absents. Plutôt que de mettre des couleurs, je pense ajouter une colonne genre "ETAT" avec comme valeurs Nouveau, Constant, Variant, Absent. Pensez-vous que ce serait plus simple ?

 

[Dranreb]

Je n'appelle pas ça une anomalie, mais une mauvaise interprétation de ce que vous souhaitiez. Il ne semblait normal qu'il n'y ait plus d'ancienne valeur ni de date si lors de la dernière mise à jour la source était identique à la valeur portée.
Par ailleurs, tant qu'à ajouter une colonne de statut, je vous assure que ce serait très clair s'il existait audssi un statut "Remplacé" qui reproduit l'ancienne ligne, rendue volontairement difficilement lisible pour qu'on ne la confonde pas avec celle de la situation à jour juste en dessous. Et les MeFC serait très facile à faire pour montrer les différences.