Discussion :

[49rio49]

bonjour,
j’espère trouver ici la solution à mon problème.
je recherche donc une macro pour comparer si les valeurs de la feuille1 sont comprises dans la feuille2(coloration de la valeur de la feuille2 en jaune).
j’ai bien essayé plusieurs macro mais le temps de traitement est extrêmement long.
je joins un fichier exemple, le fichier aura environ 200000lignes (variable) sur un nombre qui sera déterminé en plusieurs colonne.
un grand merci pour votre aide.

[mat955]

Bonjour,

Les contributeurs de ce forum n'ouvrent pas les pièces jointes en général quand elles sont proposées dès le premier post, pour des raisons de sécurité notamment.
Tu devraient privilégier les captures d'écran qui sont dans la plupart du temps suffisante à la compréhension.

Pourrais-tu nous dire ce que tu as testé comme macro ?
Normalement, le plus indiqué pour chercher une valeur dans une plage est la méthode Find.

[49rio49]

bonjour mat955
je te mets en bloc note la macro que j'utilise.
j’espère arriver à mes fin et un grand merci de m'avoir répondu.

[Pierre Fauconnier]

Salut.

A priori, pas besoin de macro pour réaliser cela. Une Mise en Forme Conditionnelle formulée (MFC) suffit. Sur base des valeurs d'un tableau structuré sur lequel on a créé la "plage nommée" Liste, voici une formule de MFC qui pourrait convenir, et qui "allume" en jaune les valeurs de E1:G5 qui sont présentes dans le tableau



J'ai fait une vidéo qui illustre quelque chose de similaire.

[49rio49]

bonjour Pierre
j'ai déjà utilisé ce principe mais sur un classeur de plus de 200000 lignes avec plusieurs colonne, ça plante.
je suis obligé de forcer la fermeture d excel.

[Igloobel]

Bonjour à Tous

pour ceux qui veulent voici le code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
Sub Compare()
Dim Lig1 As Long, Derlig1 As Long, Derlig2 As Long, Cp As Variant
Dim Lig2 As Long
Application.ScreenUpdating=False   'Evite le rafraîchissement de l'écran
    Derlig1 = Sheets("Feuil1").Range("A65535").End(xlUp).Row
    Derlig2 = Sheets("feuil2").Range("A65535").End(xlUp).Row
    DerCol = Sheets("feuil2").Range("Z1").End(xlToLeft).Column
    With Sheets("Feuil2")
    For Col = 1 To DerCol
        For Lig1 = 2 To Derlig1
            Cp = Sheets("feuil1").Cells(Lig1, "A")
            For Lig2 = 2 To Derlig2
                If Cp = .Cells(Lig2, Col) Then
                    .Cells(Lig2, Col).Interior.ColorIndex = 6
                End If
            Next Lig2
        Next Lig1
    Next Col
    End With
Application.ScreenUpdating=True   'Remet le rafraîchissement de l'écran
End Sub

Petites remarques :
- Compare est un mot réservé il vaut mieux ne pas l'utiliser. Proposition : Compar_val
- Cells Fonctionne normalement avec la syntaxe suivante: Cells(N°ligne,N°colonne).methode exemple : Cells(Lig1, "A") ==> Cells(Lig1, 1).value

mais bon comme VBA est très permissif (trop à mon gout) du coup cela marche


pour accélérer ton traitement je te propose d'utiliser la fonction : WorksheetFunction. Find ou Application.WorksheetFunction.VLookup (rechercheV).

de plus tu peux accélérer ton traitement en utilisant des Tableaux en mémoire


Tu nous dis

A +

[Alex020181]

Bonjour,

Voici une façon de faire.

A toi d'adapter si besoin.

Avec le fichier exemple que tu as transmis la comparaison se fait sur mon PC en 2 secondes à peine.

Teste (il suffit d'appuyer sur le bouton) et dis nous.

[Pierre Fauconnier]

Bonjour à Tous

pour ceux qui veulent voici le code[...]

C'est toujours plus intéressant d'avoir le code, éventuellement en plus du classeur. Ouvrir les classeurs de solution pour analyser la solution proposée, c'est pénible!

[Alex020181]

de plus tu peux accélérer ton traitement en utilisant des Tableaux en mémoire

Hello Igloobel

Yes. Les tableaux sont parfaits pour des gains de vitesse considérables.
Dans mon code proposé plus haut j'ai utilisé le dictionnaire. C'est encore plus rapido.

[Pierre Fauconnier]

Perso, je travaillerais avec des tableaux structurés, tant sur la feuille 1 que sur la 2. D'ailleurs, sur ta feuille 2, les données ont été en tableau, manifestement. L'avantage des tableaux structurés est que tu ne dois pas te préoccuper de leur emplacement/Déplacement/taille, tu peux ajouter ou supprimer des colonnes, l'ajout ou la suppression de lignes dans l'un ou l'autre des tableaux, etc... Le code continue à fonctionner sans aucun "hard coding" (le fait, par exemple, de mettre le nom des feuilles dans le code, ou le nombre de colonnes,...).

Du coup, avec Tableau1 qui est le tableau avec les 10 colonnes et tableau 2 qui est le tableau de la feuille 1 avec les quelques valeurs à trouver, voici un code qui met moins d'une seconde en transférant les données des tableaux structurés dans des arrays (tableaux VBA) et en colorant les cellules correspondantes à celles de l'array t lorsqu'une correspondance est trouvée. Ce transfert de données entre une plage et un array crée un array à deux dimensions d'indice 1, ce qui permet une mise en relation directe d'une "cellule" de l'array avec une cellule de la feuille ou de la plage.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Sub Coloration()
  Dim t
  Dim v
  Dim r As Long, c As Long, i As Long
 
  Application.ScreenUpdating = False
  t = Range("tableau1").Value
  v = Range("tableau2").Value
  For r = 1 To UBound(t)
    For c = 1 To UBound(t, 2)
      For i = 1 To UBound(v)
        If t(r, c) = v(i, 1) Then Range("tableau1")(r, c).Interior.Color = vbYellow
      Next
    Next
  Next
  Application.ScreenUpdating = True
End Sub

Attention que j'ai dû forcer certaines valeurs dans la feuil2 car le premier test semblait dire qu'il n'y avait pas de correspondances.


Je suis toutefois étonné de ton constat d'un plantage Excel lors de l'utilisation d'une mise en forme conditionnelle formulée, à tout le moins sur le fichier fourni, car la MFC formulée telle que je te l'ai donnée tout à l'heure fonctionne en temps réel sans aucun plantage
J'ai essayé les deux solutions sur base d'un tableau de 1000 lignes en feuil1 et d'un tableau de 200000 lignes et 10 colonnes en feuille 2. La solution par macro prend plusieurs secondes vu la taille du tableau, la solution par MFC est instantanée et ne plante aucunement Excel. Je persiste donc dans l'idée de la MFC, surtout qu'elle accepte l'ajout de lignes, dans dans le tableau de check (celui de la feuille 1 car la "plage nommée" grandira avec le tableau) que dans la feuille 2.

[49rio49]

bonjour Alex020181
j ai essayé le fichier que tu m'as joins et il est fonctionnel mais j'ai voulu recopier la macro sur un autre classeur test et là ??? ça ne fonctionne plus.
je pense que c'est le "dictionary" qui pose problème et je ne sais plus comment faire?
je tenais aussi à remercier les autres personnes qui mon donné des solutions (je n'ai pas eu le temps de les vérifier)
Merci,merci et encore merci à vous.

[Pierre Fauconnier]

Pour utiliser le code d'Alex, tu dois cocher la référence Microsoft Scripting Runtime. Sans la référence, tu dois travailler en Late Binding avec les lignes suivantes:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
  Dim d As Object
 
  Set d = CreateObject("Scripting.Dictionary")

Cela dit, je ne comprend pas bien à quoi sert le dico dans ce cas-ci, on peut supposer que les valeurs de la colonne de test sont uniques, et quand bien même elles ne le seraient pas.

Je ne comprends pas trop tous les On Error, non plus... ^^

[Alex020181]

Pierre ( ) a raison.

Si tu veux utiliser ce code dans un autre fichier tu dois cocher la référence "Microsoft Scripting Runtime". Elle se trouve dans le menu "outils/references" du VBE.

Quant à l'utilisation des "On error" on aurait pu s'en passer en ajoutant des tests en amont. J'ai pris le parti d'utiliser cette facilité pour gagner du temps de codage. Il y a d'autres manières de faire "plus propre" mais je ne suis pas certain que cela ne desserve pas la vitesse d'exécution du code (à vérifier si vous avez le courage).

Par exemple on sait que ShowAllData plante le code s'il n'y a pas de filtres automatiques sur la feuille donc il faudrait tester si les filtres y sont pour éventuellement utiliser ShowAllData.

Mon code

Code vba :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
On Error Resume Next
Sheets("Feuil1").ShowAllData
On Error GoTo 0

pour annuler l'utilisation des filtres automatiques sans supprimer les filtres eux-mêmes peut être remplacé par

Code vba :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
With Sheets("Feuil1")
    If .FilterMode = True Then .ShowAllData
End With

ou encore par

Code vba :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
If Sheets("Feuil1").FilterMode = True Then
   Sheets("Feuil1").ShowAllData
End if

Mais du coup le code doit traiter un "if" de plus et donc du temps de traitement.

Je sais bien que dans ce cas précis le gain est peanuts puisque je ne l'utilise que 2 fois pour tester les filtres mais de façon plus général j'évite ainsi de devoir traiter tous les cas d'erreur.

Même principe pour les attributions des valeurs de "Feuil1" dans le dictionnaire. Comme vous le savez un dictionnaire ne peut pas contenir de doublons de clés. Pour m'assurer de ne pas essayer d'enregistrer des doublons j'aurais pu utiliser if et notexist mais là encore je pense que cela prend du temps à Excel pour traiter cette condition et vérification.

En plus c'est plus simple à écrire et à retenir pour ne pas me prendre la tête.

[Pierre Fauconnier]

Salut Alex,

C'est chouette quelqu'un qui ne prend pas une remarque technique (qui n'est jamais que l'expression d'une préférence) pour une attaque personnelle

Dans ton code, c'est vrai que les On Error sont clairement circonscrits et qu'en plus, lorsqu'il y a erreur, c'est que la situation est celle désirée, en gros (en tout cas pour les filtres). Pour le dico, c'est moins évident, à mon sens. J'ai déjà vu des gens se bagarrer sur nos forums à coups de tests "minutés" pour tenter de prouver que leur code allait plus vite qu'un autre, mais ces personnes faisaient tellement peu de séquences de test qu'obtenir une moyenne parlante des temps de traitement relevait de la gageure.

En ce qui concerne le temps de traitement d'un If ou d'un Exists qui serait plus coûteux que la gestion d'erreur, je suis de toute façon dubitatif car le traitement d'une erreur (une exception, en fait) a lui aussi son coût. Il ne s'agit pas simplement de passer à la ligne suivante et basta. La levée d'une exception amène le thread actif à être arrêté et la patate chaude est passée au processeur de gestion d'erreur qui doit redescendre la pile des appels à la recherche du branchement de gestion d'erreur puis repasser l'adresse du branchement au thread principal qui va pouvoir reprendre le traitement. Même si dans le cas présent, le branchement est juste après la ligne d'erreur, il y a tout de même ce boulot à réaliser. Je ne suis pas certain que ce soit moins coûteux en temps de traitement. Et personnellement, je pense qu'en VBA, avant de devoir réaliser du Fine Tuning à ce point-là, il y a bien d'autres points à prendre en compte pour "gagner du temps". Pour finir, je trouve que la lecture d'un code truffé de On Error est plus laborieuse. J'ai dû un jour lire du code C# bourré de Try...Catch...Finally. Ca m'a donné envie de jeter l'ordi à la tête du codeur qui avait pondu ce bazar

Cela dit, c'est juste mon avis perso, et je peux parfaitement admettre que d'autres voient les choses différemment

Par contre, là où je suis "moins conciliant" , c'est dans le fait de ne pas utiliser les tableaux structurés, dans la mesure où je trouve que l'écriture du code est nettement simplifiée, sans autre hard coding que le nom du tableau et des

• colonnes qui le composent. Ca évite:
• la recherche de la dernière ligne *;
• le nommage de la feuille;
• la déclaration de la parentalité des plages;
• l'adressage "en dur" des cellules pivot de la feuille;
• ...

J'essaie dans les codes que je propose d'aller systématiquement dans le sens de cette utilisation en espérant que cela percole et que petit à petit, cette façon de coder devienne la norme, car je suis réellement convaincu que c'est vers cela que l'on doit aller.

* Dans ton code, tu bases la dernière ligne sur A, ce qui suppose que la dernière ligne DOIT toujours être remplie en A, mais dans l'exemple donné par 49rio49, toutes les colonnes ne s'arrêtent pas à la même ligne. Avec une plage comme illustré ci-dessous (pas un tableau structuré donc), un .End(xlUp) sur A va faire remonter en A4, alors qu'avec un tableau structuré (listobject en vba) ou des références structurées, on a aucun souci car on peut boucler sur les lignes du tableau ou les cellules de n'importe quelle colonne sans se tracasser de devoir trouver la dernière ligne. Dans ce message sur une autre discussion, mon code illustre la facilité avec laquelle on boucle dans le tableau, et dans cette discussion, je montre comment on rend générique l'ajout de données sur une nouvelle ligne d'un tableau structuré... A prendre ou à laisser, bien entendu.

[Alex020181]

Pour les "on error" c'est plus simple à écrire et ça fonctionne alors...
Après il est vrai qu'on peut faire des tests et des tests pour comparer mais dans le cas présent c'est inutile et franchement ça me ferait chmir pour ne pas utiliser de termes grossiers et ne pas me faire jeter par les modérateurs.

Pour les tableaux structurés je n'en connais pas toutes les subtilités (j'ai déjà vu mais pas réellement pratiqué) et même si je fournis un fichier exemple avec des tableaux il faudrait également que l'utilisateur demandeur du coup de main modifie son fichier pour créer ces tableaux. Pas sûr qu'il sache, veuille ou puisse le faire. En plus j'ai remarqué qu'ils font grossir les fichiers et donc le temps d'ouverture et de calcul à chaque fois. C'est une remarque générale qui ne s'applique pas réellement ici puisqu'il n'y en aurait pas 50.
J'ai déjà eu entre les mains un fichier avec une bonne dizaine d'onglets contenant chacun 1 seul tableau avec +500 000 lignes (budget de gestion de bâtiments et calculs nucléaires) contenant du texte brut et des formules. Le fichier faisait plusieurs Mo. J'ai repassé le tout en zone de cellules standards et des formules simples (sans code) qui faisaient la même chose et le fichier est tombé à quelques Ko. Il y avait du Power Query également dans le fichier d'origine.

Je ne dis pas que les tableaux structurés ne sont pas ni utiles ni rapides mais bon je préfère la bonne vieille méthode plus avenante.

Après comme tu dis chacun a ses habitudes

Pour la prise en compte du nombre de lignes à traiter je me suis basé uniquement sur la colonne A de Feuil1 puisque son fichier exemple ne comportait qu'une seule colonne sur cette feuille et que 49rio49 ne précisait pas plus. Par contre j'ai bien pris en compte le nombre de lignes maximum par colonne pour Feuil2.

Regarde.

Code vba :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

...

'Pour chaque colonne de 1 à 10
For colonne_cours = 1 To 10

   ...

    'Enregistrer en variable la dernière ligne
    nbre_max_lignes = Sheets("Feuil2").Cells(1048576, colonne_cours).End(xlUp).Row

'Pour toutes les lignes comprises entre 1 et le nombre de lignes max de la colonne en cours (on aurait aussi pu partir de 2 et non pas de 1 pour ne pas tenir compte des titres)
    For ligne_cours = 1 To nbre_max_lignes
        
   ...
    
    Next ligne_cours

Next colonne_cours

[Pierre Fauconnier]

[...]

Merci pour ce débat technique.

[Alex020181]

J'ai regardé les liens donnés. Va falloir que je regarde de plus près.
Merci.

[49rio49]

rebonjour,
bon après plusieurs test je pense que je vais garder la solution que Pierre m'a donné grâce à une fonction d'excel.
j'ai néanmoins une autre problématique dû à la MFC, mon but étant de supprimé les doublons que j'ai en feuille2 et les macro de suppression en fonction d'une couleur de cellule ne fonctionne pas.
existerait il une autre fonction ou une macro que me permettrait de supprimer ces cellule qui sont sous forme conditionnelle?
..j'avance grâce à vous, un grand merci.

[Pierre Fauconnier]

Le problème, c'est qu'en feuille 2, certaines cellules qui ne sont pas en jaune sont aussi en doublon. Par exemple, 6020 (H2) s'y trouve deux fois. Quels doublons veux-tu supprimer et comment: en faisant remonter ceux qui sont en dessous? en décalant à gauche?

Une bonne définition des besoins amène à des bonnes solutions

[Alex020181]

Tu utilises donc les tableaux si tu prends la solution de Pierre.
Je suis novice en la matière. Je te laisse donc à ses bons soins.

Bonne continuation à vous 2.