Comparer 2 Ranges avec Offset column.

Version imprimable

17/03/2013, 11h30
Invité

1 pièce(s) jointe(s)

Comparer 2 Ranges avec Offset column.

Bonjour le Forum,
Je me permets de poser la question suivante car je bloque et n'ai pas trouvé de posts similaires.

Je cherche - à l'aide d'une macro - à comparer deux ranges sur deux sheets differentes (‘Sheet1’ Range A3 to lastrow,lastcol et ‘Sheet2’ Range A3 to lastrow,lastcol ).

Les conditions sont les suivantes ;

1) Si valeur en Column A sur Sheet1 n’existe pas en Column A sur Sheet2 alors pas d’action et passe a Cell A suivante.

2) Si valeur en Column A sur Sheet1 = Column A sur Sheet2 alors comparaison de toutes les valeurs de la ligne en question (B a E par exemple).

3) Si valeur(s) differente(s), mettre sur Sheet1 la(es) cell(s) avec un fond d’une couleur.

NB : Les valeurs en Column A ne sont pas forcément dans le même ordre sur Sheet1 et Sheet2.

NB : Il peut y avoir des valeurs en Column A sur Sheet1 qui n’existent pas sur Sheet2 et inversement.

NB : Il n’y a pas de doublons en Column A.

NB : Il n’y a pas forcement le même nombre de ligne sur les deux ranges.

NB : le fichier peut être amené à avoir un nombre de Colum plus important (inconnu à ce jour) et j’aimerai éviter de ‘retourcher’ la macro plus tard donc, détecter la lastcol.

Remarque importante : Sur la Sheet2, il y a une columnB qui n’existe pas sur la Sheet1, et là, je bloque pour le code car en fait, il faut comparer le column B de Sheet1 avec la column C de sheet 2. (c'est plus parlant sur le fichier Excel attache). j'ai pensé passer par un offset pour la comparaison mais j'ai peur cela prenne bcp de temps pour tourner.

Ce fichier peut contenir plusieurs milliers de lignes et je ne sais pas s’il est possible de passer par un array pour augmenter la rapidité d’exécution.

Ci-joint un fichier d’exemple.

Pour l’instant, j’arrive à filtrer la Sheet2 s’il y a un match mais je bloque pour le code pour comparer les valeurs de cell Bx à Ex.

Merci pour votre aide et support.
Herve
17/03/2013, 11h50
Daniel.C

Bonjour,

Ajoute au début de la macro :

Code:

sheetOne.[B:B].Insert

et ne fais pas de tests sur la colonne B.

Supprime la colonne en fin de macro.

Merci Daniel,
Effectivement, cette solution est la plus simple; a force de trop reflechir, j'en oubli la simplicite.

par contre, j'arrive bien a filter la Sheet2 s'il y a une equivalence mais pour faire la comparaison de column C a E je bloque.
ma macro filtre bien mais comment puis-je faire pour recuperer l'addresse de la ligne ?

Dans l'exemple ci dessous, je compare les lignes 3 a 15 (donc C a Q) le probleme est qu'il faut que je remplace le 3 (en gras) pas le numero de ligne !?

Merci
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 'Look for match between Sheet1 and Sheet2 in Column A From Cell A3 For i = 3 To sheetOne.Range("a" & Rows.Count).End(xlUp).Row If Application.CountIf(sheetTwo.Range("a:a"), Cells(i, "a")) <> 0 Then Cells(i, "a").Select 'If Match, highlight the cell A in Yellow and Filter in Sheet2 Selection.Interior.ColorIndex = 3 sheetTwo.Select Range("$A$2:$N$14").AutoFilter Field:=1, Criteria1:=Cells(i, "a").Value 'If Value is Different between sheet1 and sheet2, highlight the cell(s) in Red For j = 3 To 15 If sheetOne.Cells(i, j).Value <> "" And sheetOne.Cells(i, j).Value <> sheetTwo.Cells(3, j).Value Then sheetOne.Cells(i, j).Interior.ColorIndex = 3 End If Next j sheetOne.Select End If Next i

Si j'ai bien compris :

Code:

1
2
3
4
5
6
7
8
9
10
        Range("$A$2:$N$14").AutoFilter Field:=1, Criteria1:=Cells(i, "a").Value
        With ActiveSheet.AutoFilter.Range
            Ligne = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)(1).Row
        End With
        'If Value is Different between sheet1 and sheet2, highlight the cell(s) in Red
        For j = 3 To 15
            If sheetOne.Cells(i, j).Value <> "" And sheetOne.Cells(i, j).Value <> sheetTwo.Cells(Ligne, j).Value Then
                sheetOne.Cells(i, j).Interior.ColorIndex = 3
            End If
        Next j

Merci encore Daniel,

Pour accéder a la ligne dans le filtre, j'utilise le Find.
Je suis persuadé qu'il y a moyen d'optimiser ce code mais il repond tres bien a mes attentes.

Et encore merci
Herve

Post Resolu

Code:

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
sheetOne.Select
'Look for match between Sheet1 and Sheet2 in Column A From Cell A3
        For i = 3 To sheetOne.Range("a" & Rows.Count).End(xlUp).Row
            If Application.CountIf(sheetTwo.Range("a:a"), Cells(i, "a")) <> 0 Then
                    Cells(i, "a").Select
'If Match, highlight the cell A in Yellow and Filter in Sheet2
'                    Selection.Interior.ColorIndex = 6
                    sheetTwo.Select
                    Range("$A$2:$IV$2000").AutoFilter Field:=1, Criteria1:=sheetOne.Cells(i, "a").Value
 
 
With sheetTwo.Range("a:a")
x = [A:A].Find(What:=(sheetOne.Cells(i, "a").Value), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns).Row
End With
 
 
'If Value is Different between sheet1 and sheet2, highlight the cell(s) in Red
                    For j = 3 To 256
'                    If sheetOne.Cells(i, j).Value <> "" And sheetOne.Cells(i, j).Value <> sheetTwo.Cells(x, j).Value Then
                    If sheetOne.Cells(i, j).Value <> sheetTwo.Cells(x, j).Value Then
                        sheetOne.Cells(i, j).Interior.ColorIndex = 3
                    End If
                    Next j
                    sheetOne.Select
            End If
        Next i