VBA : fonction Find

[Excel-lent]

Bonjour à tous,

J'ai une BD (base de donnée) où est listé tous mes fournisseurs connu (tableau A3:C10), avec les renseignements correspondant.

Je récupére d'un autre côté la liste des fournisseurs d'un autre logiciel (colonne E => E4 à E16).

Je veux m'assurer que tous les fournisseurs récupérés sur l'autre logiciel figure bien dans ma base de donnée!

Je pensais utiliser une boucle basique :

For ligneBD = 4 To [A4].End(xlToDown).Row

Next ligneBD

Ou une boucle plus adapté :

For Each Plage In Range(Range("A4:A" & [A4].End(xlToDown).row))
...
Next

Mais en faisant des recherches, j'ai appris que ces deux boucles avait un temp d'éxécution assez long (et comme j'ai beaucoup de données à analyser...). Il semblerait que l'utilisation de la fonction Find soit mieux adapté à mon cas et surtout plus rapide!

Mais voila, après lecture de la notice Excel, plusieurs recherche sur le net, plusieurs tentatives, je n'arrive pas à l'utiliser!

Voici ci-joint un petit exemple commenté, ainsi qu'un début de macro.

Si quelqu'un pouvait m'aider me rajouter les 2 lignes de code qu'il me manque, il m'enlèverais une belle épine du pied.

A moins qu'il ait une autre approche tout aussi efficace (voir plus)?

Merci d'avance pour votre aide.

Cordialement

 

[skoobi]

Re : VBA : fonction Find

Re,

Re

Nouveaux codes de création de liste

On obtient generalement des temps differents

Sub faire_liste1()
Range("A4:A65536").ClearContents
For n = 4 To 5003
 Range("A" & n) = "Fournisseur " & n - 3
Next n
End Sub
Sub faire_liste2()
nb = 1
Range("C4:C65536").ClearContents
For n = 4 To 5003
 Range("C" & n) = "Fournisseur " & n - 3
 If n Mod 4 = 0 Then
  Range("C" & n) = "Nouveau" & nb
  nb = nb + 1
 End If
Next n
For n = 5004 To 10003
Range("C" & n) = "Fournisseur " & n - 5003
 If n Mod 4 = 0 Then
 Range("C" & n) = "Nouveau" & nb
 nb = nb + 1
 End If
Next n
End Sub

Avec mon dernier code:
"paquet" de 30 Union: 0.48s
classique: 0.88s

Beep beep

 

[pierrejean]

Re : VBA : fonction Find

Re

Alors la je suis sur le c..

chez moi (Office 2000 Excel 9.0)
avec les paquets 0,2 secondes
sans les paquets 23 secondes

Grand bravo au tandem bhbh et skoobi

Quelqu'un releve le défi ?

L'Union par paquets ça ferait un beau slogan non !!

 

[skoobi]

Re : VBA : fonction Find

Re,

je vais breveter mon invention: "PaketUnion" by Skoobi .

 

[Lii]

Re : VBA : fonction Find

Bonjour,

le temps de composer un fichier comparatif et les nouveaux messages se sont succédés à la vitesse V.
Je donne quand même mon idée sur ce problème.
Il est difficile de tirer une loi générale
Par exemple, ’efficacité de la méthode Union se détériore avec le nombre de différences (tester …Mod 2 pour avoir le temps de patienter).
J’avais déjà constaté que « Dictionary » semblait très légèrement plus rapide que « New Collection » donc, comme bhbh*, je m’étais lancé dans les dictionnaires !
Ce n’est pas toujours le cas. La rapidité de l’une ou l’autre des méthodes varie en fonction des nombres (nombres de cas à examiner, nombres de différences).

Les derniers messages ne m’ont pas convaincu de la suprématie de « Find ».
J’ai mis le même contexte pour toutes les situations et il y a des différences sensibles, à moins que je ne me sois trompé dans mon comparatif.
Si c’est le cas faites m’en part s’il vous plait.

 

[MJ13]

Re : VBA : fonction Find

Bonjour Lii.

Merci de faire avancer la science.

Pas mal ton fichier. Au moins on sait où on en est.

Parce que la, je commence à m'y perdre.

 

[skoobi]

Re : VBA : fonction Find

Re,Re et Re ,

Lii a écrit:

J’avais déjà constaté que « Dictionary » semblait très légèrement plus rapide que « New Collection » donc, comme Hasco, je m’étais lancé dans les dictionnaires !
Ce n’est pas toujours le cas. La rapidité de l’une ou l’autre des méthodes varie en fonction des nombres (nombres de cas à examiner, nombres de différences).

Ah bon!
Pourtant les 2 objets sont très similaires ce qui veut dire que l'on peut faire indépendamment avec l'un ou l'autre.
Une simple création de liste sans doublon montre que dictionnary est plus rapide. En reprenant ton fichier colonne 7, voici ce que ce code donne:

Sub collectionVSdico()
  TabF1 = Range(Cells(4, 1), Cells([A65536].End(xlUp).Row, 1))
  TabF2 = Range(Cells(4, 7), Cells(Cells(65536, 7).End(xlUp).Row, 7))
 'collection
  t = Timer
  Set Coll_Fourn = New Collection
  On Error Resume Next
  For L1 = 1 To UBound(TabF2, 1)
    Coll_Fourn.Add TabF1(L1, 1), CStr(TabF1(L1, 1))
    Err.Clear
  Next L1
  Debug.Print "Collection", Timer - t
'dico
  t = Timer
  For L1 = 1 To UBound(TabF2, 1)
    If Not MesFrn.Exists(TabF2(L1, 1)) Then MesFrn2.Item(TabF2(L1, 1)) = TabF2(L1, 1)
  Next L1
  Debug.Print "Dico", Timer - t
  
End Sub

Collection 0,046875
Dico 0,015625

3x plus rapide quand même!
Même pris dans d'autre contexte, je ne vois pas comment collection peut être plus rapide.
Je veux bien que tu me montre un exemple.
En tout cas pour ce cas précis, dans ton fichier, il faut simplement faire la boucle sur le tableau et non la feuille pour le dico et cela est plus rapide (mais je pense que tu le savais).

Private Sub CommandButton4_Click()
  col = 6
  iniA
  iniB
  t = Timer
  Application.ScreenUpdating = False
  Set MesFrn = CreateObject("Scripting.Dictionary")
  Set MesFrn2 = CreateObject("Scripting.Dictionary")
 [COLOR=Red][B] TabF1 = Range(Cells(4, 1), Cells(DerLiA, 1))
  TabF2 = Range(Cells(4, col), Cells(DerLiC, col))[/B][/COLOR]
[B][COLOR=Red]  For n = LBound(TabF1, 1) To UBound(TabF1, 1)
    MesFrn.Item(TabF1(n, 1)) = TabF1(n, 1)
  Next n
  For n = LBound(TabF2, 1) To UBound(TabF2, 1)
    If Not MesFrn.Exists(TabF2(n, 1)) Then MesFrn2.Item(TabF2(n, 1)) = n + 3[/COLOR][/B]
  Next n

 

[Lii]

Re : VBA : fonction Find

Re

...
Une simple création de liste sans doublon montre que dictionnary est plus rapide.
...
Même pris dans d'autre contexte, je ne vois pas comment collection peut être plus rapide.
Je veux bien que tu me montre un exemple.
...

Il est vrai que ta correction rend la procédure plus rapide !
Essaie quand même, après avoir apporté ta modification, 200 1000 et 2.

 

[pierrejean]

Re : VBA : fonction Find

Re

Amusant tout cela

J'ai modifié le fichier de Lii
1) en mettant une nouvelle macro basé sur une seule collection a la place de tableau
2) en mettant sur le bouton Union la macro bhbh - Skoobi

Testez dans les differentes configurations

 

[pierrejean]

Re : VBA : fonction Find

Re

pour tenir compte de la remarque de ROGER sur l'identité des resultats :
Ajouté un bouton une collection_b qui colore tous les nouveaux (le bouton collection colore les nouveaux sans doublons)
Modifié certaines macro pour qu'elles agissent bien sur la colonne voulue ( la 3)
Ajouté egalement un bouton collection_b ( voir une collection)

Bons tests a tous

 

[skoobi]

Re : VBA : fonction Find

Re bonjour tout le monde ,

Passionnante cette discussion n’est-ce pas !

Lii, attention ! Dans ton code tu colores d’abord tout puis tu enlève la couleur pour les fournisseurs qui sont trouvés, et le mien (enfin bhbh+skoobi )) fait l’inverse et c’est ce traitement qui est plus long, forcément !
En appliquant le même principe pour les 2 codes, avec une optimisation pour le mien, tu verras que les 2 sont égaux. Mais « dico » est plus rapide avec 200,10000,2. Dans tout les cas, collection, au mieu, fait aussi bien que dico.
Et plus il y a de données à colorier (ou « décolorier »), plus l’utilisation de Union « en paquets de 30 » sera efficace , avec l’une ou l’autre procédure.

 

[MJ13]

Re : VBA : fonction Find

Bonjour à tous

Ok, il y a beaucoup de manières de procéder.

Mais quelque soit la manière de faire, on ne peut pas dire que telle ou telle procédure est mieux qu'une autre.

Seul des tests en conditions réelles pourront vous donner la meilleure solution.

Moi, par exemple je le ferais avec nb.si mais bon, pas sur que ce soit moins long.

Il faudrait peut-être attendre la réponse de Excel-lent (à moins qu'il soit sorti du jeu).

 

[Lii]

Re : VBA : fonction Find

Re,
sKooby,
J’ai repris les procédures transmises sans essayer de les optimiser. J’avais déjà assez à faire avec la mienne, entre autre, penser à inverser les couleurs.
Dernière remarque, en relançant les codes de façon aléatoire, dans les mêmes conditions bien sûr, leur durée peut varier (même si c’est de peu).
Avec ton fichier, j’ai eu :

0,03125---------------- 0,0625------------------- 0,046875
Collection-------------- Dico sans U ---------------Dico avec U

La seule conclusion que je pourrais en tirer, c’est qu’il me faut être prudent dans mes conclusions.
Affirmer quelque chose tant qu’on n’a pas trouvé de contre-exemple est une pratique que je ne pratique pas ou, tout du moins, je n'accepte pas d'énoncer qu'elle est toujours vraie.

MJ13,
le but premier de mon intervention était de souligner que le contexte est primordial pour effectuer ses choix (pour autant qu’on en dispose).

 

[skoobi]

Re : VBA : fonction Find

Re,

Re,
sKooby,
J’ai repris les procédures transmises sans essayer de les optimiser. J’avais déjà assez à faire avec la mienne, entre autre, penser à inverser les couleurs.

Je m'en doutais bien .
Je voulais juste rectifier, c'est tout .

Affirmer quelque chose tant qu’on n’a pas trouvé de contre-exemple est une pratique que je ne pratique pas ou, tout du moins, je n'accepte pas d'énoncer qu'elle est toujours vraie.

D'où ces échanges très instructif n'est-ce pas?

Excel-lent, j'éspère que tu t'y retrouveras dans tous ces messages .

Bon week-end à tous .

 

[soenda]

Re : VBA : fonction Find

Bonjour le fil

Je dirais même plus : Très instructif !

Merci à tous

 

[skoobi]

Re : VBA : fonction Find

Moi, par exemple je le ferais avec nb.si mais bon, pas sur que ce soit moins long.

Comme ici?