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

 

[pierrejean]

Re : VBA : fonction Find

Bonjour Excel-lent

En general l'utilisation des tableaux accelere ce genre de travail

Teste cette macro en vraie grandeur

 

[Modeste]

Re : VBA : fonction Find

Bonjour Excel-lent, pierrejean,

Sans contredire en aucune manière l'honorable pierrejean (et juste parce que j'y suis arrivé ) une version avec Find.

@+

 

[ROGER2327]

Re : VBA : fonction Find

Bonjour à tous

... et (bien que je préfère généralement traiter ce genre de problème avec des tableaux) une autre version avec Find :

[COLOR="DarkSlateGray"]Sub TrouverFrsNonRéférencé()
Dim oCelC As Range, oCel As Range, oDat
   Application.ScreenUpdating = False
   Set oDat = Worksheets("Feuil1").Range("A4:" & Range("A4").End(xlDown).Address)
   With Worksheets("Feuil1").Range("C4:" & Range("C4").End(xlDown).Address)
      .Interior.ColorIndex = xlNone
      For Each oCelC In .Cells
         For Each oCel In oDat.Cells
            If oDat.Find(oCelC.Value, LookIn:=xlValues) Is Nothing Then oCelC.Interior.ColorIndex = 6
         Next oCel
      Next oCelC
   End With
   Application.ScreenUpdating = True
End Sub[/COLOR]

ROGER2327
#1997
_

--- Ce code n'est pas correct : voir le message #8 ---​

 

[pierrejean]

Re : VBA : fonction Find

Re

ne le sois pas trop Modeste
j'ais fait quelques essais avec des tableaux plus importants sans avoir pu departager nos 2 macros (j'etais toutefois dans une gamme de temps de moins d'une seconde)

Edit : Salut ROGER

 

[ROGER2327]

Re : VBA : fonction Find

Suite...

Après test de vitesse : ma solution est très, très, très, voire très mauvaise.
Pour les autres procédures, léger avantage à pierrejean (sur ma machine, avec d'autres choses ouvertes qu'Excel...)​

ROGER2327
#1998

 

[skoobi]

Re : VBA : fonction Find

Bonjour Roger, Pierre-Jean, Modeste, Excel-lent,

une variante avec la fonction VBA CountIf:

Sub trouve()
Dim Frns As Range
For Each Frns In Range("C4:C" & [C65536].End(xlUp).Row)
  If WorksheetFunction.CountIf([A4:A10], Frns.Value) = 0 Then Frns.Interior.ColorIndex = 6
Next
End Sub

Un peu plus court en code, peut-être pas en temps d'exécution, à voir.

 

[ROGER2327]

Re : VBA : fonction Find

Suite...

Une GROSSE bourde dans le code que j'ai proposé !
Voici une version plus correcte :

[COLOR="DarkSlateGray"]Sub FrsNonRéférencé()
Dim oCelC As Range, oDat
   Application.ScreenUpdating = False
   Set oDat = Worksheets("Feuil1").Range("A4:" & Range("A4").End(xlDown).Address)
   With Worksheets("Feuil1").Range("C4:" & Range("C4").End(xlDown).Address)
      .Interior.ColorIndex = xlNone
      For Each oCelC In .Cells
            If oDat.Find(oCelC.Value, LookIn:=xlValues) Is Nothing Then oCelC.Interior.ColorIndex = 6
      Next oCelC
   End With
   Application.ScreenUpdating = True
End Sub[/COLOR]

La durée d'exécution se place entre celle du code de Modeste et celle de pierrejean. (Même chose pour le code de skoobi.)​

ROGER2327
#1999

 

[soenda]

Re : VBA : fonction Find

Bonjour le fil, pierrejean, Modeste, Roger2327, skoobi, Excel-lent

Une autre version assez concise et plutot rapide(gain 30%)

Sub b()
    Dim Liste As Range, C As Range, p As Range
 
    Set Liste = Range("A4", [A65536].End(xlUp))
    Set p = [C3]
 
    For Each C In Range("C4", [C65536].End(xlUp))
        If WorksheetFunction.CountIf(Liste, C) = 0 Then Set p = Union(p, C)
    Next
 
    p.Interior.ColorIndex = 6
 
End Sub

Cerise sur le gateau : la cellule [C3] est colorée à la fin de la sub (ça peut servir...)

A plus

 

[skoobi]

Re : VBA : fonction Find

Re,
bonjour soenda,

Oui, l'utilisation de Union traite tout d'un coup.
Le code le plus rapide, parmi touts ceux proposés sera la combinaison des tableaux de pierrejean avec Union.

 

[ROGER2327]

Re : VBA : fonction Find

Re...

Problème : avec un échantillon de 120 fournisseurs en colonne A, 10206 fournisseurs en colonne C (liste avec des doublons), les procédures sans "UNION" (avec "ScreenUpdating" fixé à false) tournent en 1,9 à 2,8 secondes. La procédure avec "UNION" met plus de 2 minutes et 10 secondes.
La question est : Pourquoi ?
(Bien entendu, les essais sont faits dans le même environnement.)
​

ROGER2327
#2002

 

[Modeste]

Re : VBA : fonction Find

Bonsoir à tous, (pierrejean, Roger2327, skoobi, Excel-lent, soenda)

J'ignore ce qu'Excel-lent en pensera, mais en ce qui me concerne, j'ai apprécié vos propositions différentes et vos commentaires. Je mets tout ça de côté pour le décortiquer (quand j'aurai un peu de temps, pour le faire sérieusement )

 

[pierrejean]

Re : VBA : fonction Find

Re
d'apres mes tests de vitesse

c'est la macro de ROGER de loin la plus rapide: 10secondes pour 13000 fournisseurs (colonne C) et 7000 references (colonne A)
la mienne est a 33 secondes
celle de soenda a 50 secondes (et il ne semble pas qu'elle fasse de remise a zero initiale)
a noter que dans ces conditions celle de ROGER offre la petite emotion de voir la feuille s'effacer totalement (quadrillage et fond blanc) avant de donner le resultat

 

[Excel-lent]

Re : VBA : fonction Find

Bonsoir à tous,

Je savais que le patience était toujours récompensé, mais là!!! Cela dépasse toute mes espérances!!!! Que de solutions, toutes plus sympa les unes que les autres!

J'ai bien fais d'attendre et de bosser sur mes UserForm en attendant!

Je testerais tout ça demain au boulot.

Encore un grand merci à tous, et tout particulièrement à JeanPierre qui fut le plus rapide.

Très bonne soirée à tous

 

[skoobi]

Re : VBA : fonction Find

Re,

Encore un grand merci à tous, et tout particulièrement à JeanPierre qui fut le plus rapide.

Tellement rapide que je ne le vois pas dans la discussion .