Comparaison de feuille référence par référence

Bonjour à tous,

A mon tour de poster pour solliciter votre aide.

Je travaille sur une grosse base de donnée de composants.

Le report hebdomadaire généré tout les lundi comporte environ 23000 lignes et 211 colonnes après traitement.

Une entête pour chaque colonnes existe représentant les attributs de chaque référence de la colonne A.

Mon objectif est de prendre le report du jour et le report de la semaine précédente, les mettre en forme puis de comparer références par références les valeurs des attributs associés à ces composants de la semaine S par rapport à la semaine S-1

Tout cela en utilisant un tableau en mémoire car le volume est très important et qu'à terme il est possible que je rajoute d'autres colonnes et en comparant colonnes par colonnes. Il est préférable de faire cela en mémoire car nettement plus rapide en plus.

Attention: comparaison en utilisant les entêtes de colonne car il arrive que le report me décale des colonnes.

Le résultat est la création d'un autre classeur avec les 2 reports mis en forme, une feuille avec les lignes comportant des modifications highlightés en rouge.

J'aimerai réaliser la macro moi-même simplement grâce à vos explications car mon objectif est avant tout de maîtriser le VBA.

Donc voici mes questions :

1) je ne sais absolument pas comment fonctionne les variables tableaux et comment manipuler les données.

2) Comment procéder pour la comparaison de la ligne en tenant compte de l'entête des colonnes afin que les valeurs des cellules correspondent aux attributs de l'entête.

Merci à vous pour vos informations.

A ce jour,

J'ai réalisé le traitement des 2 classeurs en VBA avec mise en forme puis sauvegarde des 2 feuilles dans 1 classeur.

Bonjour,

je ne suis pas à la hauteur pour ça mais il est certain que sans fichier exemple court (30-40 lignes) avec le avant / après il sera difficile de t'aider.

P.

Voici un exemple du classeur final avec donc la feuille N et la feuille N-1.

Merci à toi pour cette précision

Bonsoir,

Je suppose que ton classeur présente tes 2 feuilles mises en forme. La 3e illustrant le résultat à obtenir serait utile....

Cordialement.

Elle reprend la ligne de la feuille N et les cellules modifiées sont coloriées en rouge

Et entre N et N-1 est-on toujours assuré d'avoir le même nombre de colonnes et le même nombre de lignes ?

Voilà une ébauche de procédure, commentée pour que tu t'y retrouves (demain je ne serais snas doute guère disponible pour ce genre de choses...)

Sub Comparaison()
    Dim tit(), lgn, lgnAnt, comp, i&, j&, k%, g%, h%, n%
    Dim wsC As Worksheet, wsAnt As Worksheet, colAnt As Range
    'Feuille N-1: on affecte à une variable, de même que sa col.A à partir A2 (ident.)
    Set wsAnt = .Worksheets("Feuille N-1")
    n = wsAnt.Range("A" & Rows.Count).End(xlUp).Row
    Set colAnt = wsAnt.Range("A2:A" & n)
    'Ajout feuille résultats Compar., affectée variable
    Set wsC = Worksheets.Add(before:=Worksheets(1))
    wsC.Name = "Compar N|N-1"
    'Mise sous bloc With feuille N (à partir de laquelle on va opérer)
    With Worksheets("Feuille N")
        'Récupération des numéros dernière colonne et ligne
        k = .Cells(1, Columns.Count).End(xlToLeft).Column
        n = .Cells(.Rows.Count, 1).End(xlUp).Row
        'Dimensionnement tableau sur colonnes: on lui affecte les en-têtes
        ReDim tit(1 To k)
        For i = 1 To k
            tit(i) = .Cells(1, i)
        Next i
        'On affecte d'abord ce tableau en ligne 1 de la feuille résultat
        'on incrémente variable h qui sera utilisée pour inscrire sur cette feuille
        wsC.Range("A1").Resize(, k).Value = tit: h = 1
        'On définit dernière col. feuille N-1
        'si différent de celui de feuille N, la proc. sera interrompue...
        j = wsAnt.Cells(1, .Columns.Count).End(xlToLeft).Column
        If j = k Then
            'On récupère la ligne d'en-tête N-1 dans une variable sous forme tableau
            lgn = wsAnt.Range("A1").Resize(, k).Value
            'On recherche chaque en-tête N-1 dans tableau des en-têtes N
            'Si trouvé, on remplace l'en-tête N par l'index de colonne N-1
            'Si non trouvé, on le signale aux fins de recherche manuelle...
            '...la proc. devra être relancée après correction du problème
            On Error Resume Next
            For i = 2 To k
                j = WorksheetFunction.Match(lgn(1, i), tit, 0)
                If Err.Number <> 0 Then
                    MsgBox "L'en-tête " & lgn(1, i) & " (N-1, col. " & i & ") n'est pas " _
                     & "trouvée dans N.", vbCritical, "En-tête non repérée"
                    Exit Sub
                End If
                tit(j) = i
            Next i
            On Error GoTo 0
        Else
            MsgBox "Pas le même nombre de colonnes entre N et N-1 !", vbCritical, "Erreur"
            Exit Sub
        End If
        'A ce stade, on attaque la comparaison, on parcourt les lignes de N
        For i = 2 To n
            'On récupère chaque ligne sous forme de tableau dans une variable
            lgn = .Range("A" & i).Resize(, k).Value
            'On recherche la ligne de N-1 correspondante...
            On Error Resume Next
            j = WorksheetFunction.Match(.Cells(i, 1), colAnt, 0) + 1
            If Err.Number = 0 Then
                On Error GoTo 0
                '...et on fait de même pour cette ligne
                lgnAnt = wsAnt.Range("A" & j).Resize(, k).Value
                'On compare chaque élément de ces deux lignes, en utilisant
                'le tableau des index col. N-1 (indicé sur les colonnes de N)
                'On note les éléments différent en concaténant les indices
                'séparés par des ;
                For g = 2 To k
                    If lgn(1, g) <> lgnAnt(1, tit(g)) Then comp = comp & ";" & g
                Next g
                'Si des différences: on incrémente h (ligne feuille résultat),
                'on convertit la chaîne d'indices en tableau,
                'on affecte la ligne à la feuille résultat,
                'au moyen du tableau d'indices on colore les cellules correspondantes
                If comp <> "" Then
                    h = h + 1: comp = Split(comp, ";")
                    ws.Range("A" & h).Resize(, k).Value = lgn
                    For g = 1 To UBound(comp)
                        ws.Cells(h, comp(g)).Interior.Color = vbRed
                    Next g
                    comp = ""
                End If
            Else
                'Si l'on n'a pas trouvé la ligne sur N-1
                'On procéde à une inscription sur la feuille résultat
                'l'identifiant en A et une mention en B, on colore en jaune
                Err.Clear: On Error GoTo 0: h = h + 1
                ws.Range("A" & h) = .Range("A" & i)
                ws.Range("B" & h) = "non trouvée N-1"
                ws.Range("A" & h).Resize(, 2).Interior.Color = vbYellow
            End If
            'NB: si des éléments de N-1 ne figurent pas en N, on ne les détectera pas
            'sauf à colorer sur N-1 tous les éléments comparés, de façon à faire
            'ressortir les manquants... ?
        Next i
    End With
End Sub

Sous réserves, car non testée !

Cordialement.

MFerrand a écrit :

Et entre N et N-1 est-on toujours assuré d'avoir le même nombre de colonnes et le même nombre de lignes ?

Non car certains composants peuvent être supprimés et d'autres créés (+- de lignes)

Et pour les colonnes elles peuvent evoluer dans le cas où on rajoute un nouveau paramètres dans la base ou suite à la présence d'un caractère spécial qui décale les valeurs (déjà arrivé mais uniquement sur une ligne mais cela avait modifié la position de 2 colonnes)


MFerrand a écrit :

Voilà une ébauche de procédure, commentée pour que tu t'y retrouves (demain je ne serais snas doute guère disponible pour ce genre de choses...)

Sub Comparaison()
    Dim tit(), lgn, lgnAnt, comp, i&, j&, k%, g%, h%, n%
    Dim wsC As Worksheet, wsAnt As Worksheet, colAnt As Range
    'Feuille N-1: on affecte à une variable, de même que sa col.A à partir A2 (ident.)
    Set wsAnt = .Worksheets("Feuille N-1")
    n = wsAnt.Range("A" & Rows.Count).End(xlUp).Row
    Set colAnt = wsAnt.Range("A2:A" & n)
    'Ajout feuille résultats Compar., affectée variable
    Set wsC = Worksheets.Add(before:=Worksheets(1))
    wsC.Name = "Compar N|N-1"
    'Mise sous bloc With feuille N (à partir de laquelle on va opérer)
    With Worksheets("Feuille N")
        'Récupération des numéros dernière colonne et ligne
        k = .Cells(1, Columns.Count).End(xlToLeft).Column
        n = .Cells(.Rows.Count, 1).End(xlUp).Row
        'Dimensionnement tableau sur colonnes: on lui affecte les en-têtes
        ReDim tit(1 To k)
        For i = 1 To k
            tit(i) = .Cells(1, i)
        Next i
        'On affecte d'abord ce tableau en ligne 1 de la feuille résultat
        'on incrémente variable h qui sera utilisée pour inscrire sur cette feuille
        wsC.Range("A1").Resize(, k).Value = tit: h = 1
        'On définit dernière col. feuille N-1
        'si différent de celui de feuille N, la proc. sera interrompue...
        j = wsAnt.Cells(1, .Columns.Count).End(xlToLeft).Column
        If j = k Then
            'On récupère la ligne d'en-tête N-1 dans une variable sous forme tableau
            lgn = wsAnt.Range("A1").Resize(, k).Value
            'On recherche chaque en-tête N-1 dans tableau des en-têtes N
            'Si trouvé, on remplace l'en-tête N par l'index de colonne N-1
            'Si non trouvé, on le signale aux fins de recherche manuelle...
            '...la proc. devra être relancée après correction du problème
            On Error Resume Next
            For i = 2 To k
                j = WorksheetFunction.Match(lgn(1, i), tit, 0)
                If Err.Number <> 0 Then
                    MsgBox "L'en-tête " & lgn(1, i) & " (N-1, col. " & i & ") n'est pas " _
                     & "trouvée dans N.", vbCritical, "En-tête non repérée"
                    Exit Sub
                End If
                tit(j) = i
            Next i
            On Error GoTo 0
        Else
            MsgBox "Pas le même nombre de colonnes entre N et N-1 !", vbCritical, "Erreur"
            Exit Sub
        End If
        'A ce stade, on attaque la comparaison, on parcourt les lignes de N
        For i = 2 To n
            'On récupère chaque ligne sous forme de tableau dans une variable
            lgn = .Range("A" & i).Resize(, k).Value
            'On recherche la ligne de N-1 correspondante...
            On Error Resume Next
            j = WorksheetFunction.Match(.Cells(i, 1), colAnt, 0) + 1
            If Err.Number = 0 Then
                On Error GoTo 0
                '...et on fait de même pour cette ligne
                lgnAnt = wsAnt.Range("A" & j).Resize(, k).Value
                'On compare chaque élément de ces deux lignes, en utilisant
                'le tableau des index col. N-1 (indicé sur les colonnes de N)
                'On note les éléments différent en concaténant les indices
                'séparés par des ;
                For g = 2 To k
                    If lgn(1, g) <> lgnAnt(1, tit(g)) Then comp = comp & ";" & g
                Next g
                'Si des différences: on incrémente h (ligne feuille résultat),
                'on convertit la chaîne d'indices en tableau,
                'on affecte la ligne à la feuille résultat,
                'au moyen du tableau d'indices on colore les cellules correspondantes
                If comp <> "" Then
                    h = h + 1: comp = Split(comp, ";")
                    ws.Range("A" & h).Resize(, k).Value = lgn
                    For g = 1 To UBound(comp)
                        ws.Cells(h, comp(g)).Interior.Color = vbRed
                    Next g
                    comp = ""
                End If
            Else
                'Si l'on n'a pas trouvé la ligne sur N-1
                'On procéde à une inscription sur la feuille résultat
                'l'identifiant en A et une mention en B, on colore en jaune
                Err.Clear: On Error GoTo 0: h = h + 1
                ws.Range("A" & h) = .Range("A" & i)
                ws.Range("B" & h) = "non trouvée N-1"
                ws.Range("A" & h).Resize(, 2).Interior.Color = vbYellow
            End If
            'NB: si des éléments de N-1 ne figurent pas en N, on ne les détectera pas
            'sauf à colorer sur N-1 tous les éléments comparés, de façon à faire
            'ressortir les manquants... ?
        Next i
    End With
End Sub

Sous réserves, car non testée !

Cordialement.

Merci à vous je vais tester cela et essayer de comprendre comment cela fonctionne

Ouch, coton le code.

Je comprend 60% mais le travail sur les tableaux, je suis largué.

De plus il semble y avoir un manque sur la ligne :

lgn = .Range("A" & i).Resize(, k).Value

mais je ne la comprend pas au hasard il manque i car le tableau fait (i,k) en dimension ???

Vraiment un grand merci à toi pour ton travail en tout cas.

Il ne manque rien : i est un numéro de ligne, lgn une variable Variant, k le numéro de la dernière colonne (ou puisqu'on commence à A, le nombre de colonnes).

On affecte à lgn : .Range("A" & i).Resize(, k).Value

si i = 2 et k = 211, on lui affecte donc les valeurs de la plage A2:HC2

lgn contiendra un tableau à 2 dimensions, d'indices 1 à 1, et 1 à 211.

Cordialement.

MFerrand a écrit :

Il ne manque rien : i est un numéro de ligne, lgn une variable Variant, k le numéro de la dernière colonne (ou puisqu'on commence à A, le nombre de colonnes).

On affecte à lgn : .Range("A" & i).Resize(, k).Value

si i = 2 et k = 211, on lui affecte donc les valeurs de la plage A2:HC2

lgn contiendra un tableau à 2 dimensions, d'indices 1 à 1, et 1 à 211.

Cordialement.

Super je comprends mais excel m'indique une erreur sur la ligne ?

Quelle erreur ? Et quelle valeur de i au moment de l'erreur ?

Voici copie de l'écran avec les variables.

Merci encore beaucoup pour votre aide.


A quoi sert resize dans la ligne?

capture

On ne voit pas la ligne surlignée, me confirmer que c'est bien celle indiquée précédemment.

Si c'était bien celle-là, on a donc parcouru N jusqu'à la ligne 246. On a inscrit une ligne modifiée depuis le départ. Après comparaison de la ligne 246 de N avec la 247 de N-1, on bloque sur la ligne 247 de N !

L'erreur 424 n'est sûrement pas la bonne, une telle erreur sur cette ligne n'aurait pas attendu qu'on atteigne 247...

Voir le contenu de la ligne 247, également celui de la 246. Voir quelle inscription a été déjà faite et à quelle ligne elle correspondait.

Cela permettra peut-être de trouver des indices, car pour l'instant ! ?

Je confirme.

je vais tenter un comparatif sur une seul ligne avec les colonnes décalées.

On verra si c'est les colonnes ou la ligne incriminées.

J'ai testé en ayant 2 feuilles N et N-1 identiques : pas de soucis (sur 1 ligne )

avec feuille N différent de N-1 : erreur (modif sur feuille N colonne 3) (sur 1 ligne)

capture erreur

J'ai beau tourner tout cela dans tout les sens je n'arrive pas à comprendre pourquoi cela bloque.

Un peu d'aide serait la bienvenue.

Merci d'avance.

C'est pas la même erreur ça ? On est sur l'affectation !

Erreur 1004 ?

Toujours la même erreur identique à celle de la copie d'ecran

Bonjour,

j'ai trouvé 2 petites erreurs dans le code: mais je n'ai pas poussé plus loin

         If comp <> "" Then
            h = h + 1: comp = Split(comp, ";")
            wsC.Range("A" & h).Resize(, k).Value = lgn     'la feuille est WSC --> nouvelle feuille destination
            For g = 1 To UBound(comp)
               wsC.Cells(h, CDbl(comp(g))).Interior.Color = vbRed     ' comp = "13" par exemple il faut le rendre numérique
               'wsC.Cells(h, comp(g)).Interior.Color = vbRed  '
            Next g
            comp = ""
         End If
      Else

P.

Rechercher des sujets similaires à "comparaison feuille reference"