Comparaison cellules 2 tableaux sur même feuille avec Application.Match

Le 21/09/2020 à 14:48
19test-comparaison-tableaux-1.xlsm (29.11 Ko)

Bonjour à toutes et à tous,

/!\ Fichier en PJ

/!\Je débute en VBA

Dans mon cadre professionnel je dois réaliser un programme VBA permettant tout d'abord de comparer les références de deux tableaux présent sur une même feuille puis afficher les références différentes entre les deux tableaux. Deuxièmement je dois afficher les quantités qui diffèrent pour des références identiques. Bien sur l'ordre dans lequel sont organisées les données ne doit pas influer.

La partie concernant la comparaison des références est réussie, cependant la partie concernant la comparaison des quantités me pose soucis. Voici mon code ci-dessous avec les commentaires permettant de le suivre facilement.

Sub Compare()

'Déclaration variables I est la ligne d'une cellule à comparer du 1er tableau, K est la ligne d'une cellule à comparer du 2eme tableau
'J1 est la ligne d'affichage de la réponse, M est le nombre de ligne qu'il y a dans le tableau
    Dim I As Long, K As Long, J1 As Long, M As Long
    Application.ScreenUpdating = False
    J1 = 2

'En partant du bas de la feuille puis en remontant jusqu'au bas du tableau M obtient le nombre de lignes présentes dans le tableau
    M = Cells(Rows.Count, 1).End(xlUp).Row

'Les prochaiines lignes permettent un affichage propre
    Range("N1:Q1").MergeCells = True
    Range("R1:U1").MergeCells = True
    Range("N1").Value = "Ref BC3 not in BE2"
    Range("R1").Value = "Ref BE2 not in BC3"
    Range("N1").HorizontalAlignment = xlCenter
    Range("R1").HorizontalAlignment = xlCenter
    Range("N1").Font.Bold = True
    Range("R1").Font.Bold = True
    Range("N2").Value = "Des"
    Range("O2").Value = "Ref"
    Range("P2").Value = "Qté"
    Range("Q2").Value = "Cat"
    Range("R2").Value = "Des"
    Range("S2").Value = "Ref"
    Range("T2").Value = "Qté"
    Range("U2").Value = "Cat"

    Range("W1:AA1").MergeCells = True
    Range("W1").Value = "Quantity not equal"
    Range("W1").HorizontalAlignment = xlCenter
    Range("W1").Font.Bold = True
    Range("W2").Value = "Des"
    Range("X2").Value = "Ref"
    Range("Y2").Value = "Cat"
    Range("Z2").Value = "Qty BC3"
    Range("AA2").Value = "Qty BE2"

'Partie comparaison de références
    For I = 3 To M
        If IsError(Application.Match(Range("B" & I).Value, Columns("F"), 0)) Then
            J1 = J1 + 1
            Range("N" & J1).Value = Range("A" & I).Value
            Range("O" & J1).Value = Range("B" & I).Value
            Range("P" & J1).Value = Range("C" & I).Value
            Range("Q" & J1).Value = Range("D" & I).Value
    End If
        If IsError(Application.Match(Range("F" & I).Value, Columns("B"), 0)) Then
            J1 = J1 + 1
            Range("R" & J1).Value = Range("E" & I).Value
            Range("S" & J1).Value = Range("F" & I).Value
            Range("T" & J1).Value = Range("G" & I).Value
            Range("U" & J1).Value = Range("H" & I).Value
        End If
    Next I

'Partie comparaison de qté pour une même référence

    J1 = 2

'On parcourt le 1er tableau grâce à la variable I
    For I = 3 To M
'On parcourt le 2eme tableau grâce à la variable K
        For K = 3 To M
'/!\/!\/!\(c'est ici qu'il y a un soucis) S'il y a la même référence entres les deux tableaux alors:
            If Not IsError(Application.Match(Range("B" & I).Value, Range("F" & K).Value, 0)) Then
'S'il y a une erreur entre les qtés d'une même ref alors:
                If IsError(Application.Match(Range("C" & I).Value, Range("G" & K).Value, 0)) Then
'Partie affichage
                    J1 = J1 + 1
                    Range("W" & J1).Value = Range("A" & I).Value
                    Range("X" & J1).Value = Range("B" & I).Value
                    Range("Y" & J1).Value = Range("D" & I).Value
                    Range("Z" & J1).Value = Range("C" & I).Value
                    Range("AA" & J1).Value = Range("G" & K).Value
                End If

            End If
        Next K
    Next I

    Application.ScreenUpdating = True

End Sub

Lors du débogage je me rend compte que VBA considère que toutes les références sont différentes donc il saute l'étape de comparaison des quantités. Or 3 références sont identiques et je n'arrive pas à résoudre cela.

Si quelqu'un peut m'aider?

Merci à vous!

Le 21/09/2020 à 15:17

Bonjour,

.Match permet de connaitre la position d'une valeur cherchée dans une plage.

S'il s'agit de comparer deux cellules, le plus simple :

If Range("B" & I).Value = Range("F" & K).Value Then 
...

A+

Le 21/09/2020 à 15:37

bonjour,

en utilisant la méthode find et en simplifiant la logique

30test-comparaison-tableaux-1.xlsm (26.50 Ko)
Le 21/09/2020 à 21:28

Merci h2so4, c'est exactement ce que je recherchais!

Peux-tu cependant m'expliquer exactement ce que chaque ligne fait? Car étant débutant je ne comprends pas tout..

Merci!

Le 22/09/2020 à 00:02

Bonsoir,

voici quelques commentaires

Sub Compare()

    'Déclaration variables I est la ligne d'une cellule à comparer du 1er tableau, K est la ligne d'une cellule à comparer du 2eme tableau
    'J1 est la ligne d'affichage de la réponse, M est le nombre de ligne qu'il y a dans le tableau
    Dim I As Long, K As Long, J1 As Long, J2 As Long, J3 As Long, M As Long, dl As Long, re As Range
    Application.ScreenUpdating = False
    J1 = 2 'compteur ligne dans A et pas dans B
    J2 = 2 'compteur ligne dans B et pas dans A
    J3 = 2 'compteur ligne qte <>

    'En partant du bas de la feuille puis en remontant jusqu'au bas du tableau M obtient le nombre de lignes présentes dans le tableau

    'Les prochaiines lignes permettent un affichage propre
    Range("N1:Q1").MergeCells = True
    Range("R1:U1").MergeCells = True
    Range("N1").Value = "Ref A not in B"
    Range("R1").Value = "Ref B not in A"
    Range("N1").HorizontalAlignment = xlCenter
    Range("R1").HorizontalAlignment = xlCenter
    Range("N1").Font.Bold = True
    Range("R1").Font.Bold = True
    Range("N2").Value = "Des"
    Range("O2").Value = "Ref"
    Range("P2").Value = "Qté"
    Range("Q2").Value = "Cat"
    Range("R2").Value = "Des"
    Range("S2").Value = "Ref"
    Range("T2").Value = "Qté"
    Range("U2").Value = "Cat"

    Range("W1:AA1").MergeCells = True
    Range("W1").Value = "Quantity not equal"
    Range("W1").HorizontalAlignment = xlCenter
    Range("W1").Font.Bold = True
    Range("W2").Value = "Des"
    Range("X2").Value = "Ref"
    Range("Y2").Value = "Cat"
    Range("Z2").Value = "Qty A"
    Range("AA2").Value = "Qty B"

    'Partie comparaison de références
    dl = Cells(Rows.Count, 2).End(xlUp).Row 'dernière ligne tableau A
    Set plageb = Range("B1").Resize(dl, 1) 'plage des références du tableau A (colonne B), qui part de B1 sur une hauteur de DL lignes
    M = dl
    dl = Cells(Rows.Count, 6).End(xlUp).Row ' dernière ligne tableau B, on fait l'hypothèse qu'elle peut ne pas être la même que tableau A
    Set plageF = Range("F1").Resize(dl, 1) ' 'plage des références du tableau B (colonne F), qui part de F1 sur une hauteur de DL lignes
    M = Application.Max(M, dl) 'M= dernière ligne du tableau A et B (on prend la plus grande des 2 valeurs)
    For I = 3 To M 'on parcourt les tableaux A et B de la ligne 3 à la ligne M
        If Range("B" & I) <> "" Then ' si référence non vide
        ' re contiendra la reférence à la cellule en colonne F qui contient la valeur contenue dans B&I, Re sera nothing si la référence n'est pas trouvée
            Set re = plageF.Find(Range("B" & I), lookat:=xlWhole, LookIn:=xlValues) 'recherche de la reference B&i dans la plage F
            If re Is Nothing Then 'ref A pas trouvée en B
                J1 = J1 + 1
                Range("N" & J1).Value = Range("A" & I).Value
                Range("O" & J1).Value = Range("B" & I).Value
                Range("P" & J1).Value = Range("C" & I).Value
                Range("Q" & J1).Value = Range("D" & I).Value
            ElseIf Range("C" & I) <> Cells(re.Row, "G") Then 'ref A trouvée en B mais qte <>
                J3 = J3 + 1
                Range("W" & J3).Value = Range("A" & I).Value
                Range("X" & J3).Value = Range("B" & I).Value
                Range("Y" & J3).Value = Range("D" & I).Value
                Range("Z" & J3).Value = Range("C" & I).Value
                Range("AA" & J3).Value = Cells(re.Row, "G") ' mettre en colonne AA, la qte qui se trouve sur la ligne de la reference de A (col B) trouvée en B (col F) (re.row)
            End If
        End If
        If Range("F" & I) <> "" Then 'si référence non vide
            Set re = plageb.Find(Range("F" & I), lookat:=xlWhole, LookIn:=xlValues) 'recherche de la reference  F&i dans la plage B
            If re Is Nothing Then 'ref B pas trouvée en A
                J2 = J2 + 1
                Range("R" & J2).Value = Range("E" & I).Value
                Range("S" & J2).Value = Range("F" & I).Value
                Range("T" & J2).Value = Range("G" & I).Value
                Range("U" & J2).Value = Range("H" & I).Value
            End If
        End If
    Next I

    Application.ScreenUpdating = True

End Sub
Rechercher des sujets similaires à "comparaison tableaux meme feuille application match"