Vous utilisez un navigateur obsolète. Il se peut que ce site ou d'autres sites Web ne s'affichent pas correctement. Vous devez le mettre à jour ou utiliser un navigateur alternatif.
Rechercher/Marquer une liste de termes d'un tableau donné (autre que cells.find)
J'espère que je vais arriver à expliquer clairement ma problématique.
Donc j'ai un classeur de 2 feuilles, l'un contient des informations en vrac, et l'autre une liste (très longue) de termes. Je dois rechercher chaque terme et les mettre en couleur dans la feuille qui contient les informations en vrac.
J'ai très bien réussi à faire cela avec cells.find (j'ai fait une boucle avec activecell.range.offset pour passer à chaque fois au terme suivant), mais c'était avant que ma liste de terme fasse plus de 10 000 lignes.
J'aimerai simplement savoir s'il existe sous VBA quelquechose de moins lourd que cells.find pour rechecher une colonne complète.
PS: J'ai essayer une formule excel simple avec RechercheV, mais mon soucis c'est que dans ce cas la recherche doit se faire dans l'onglet qui contient les infos en vrac, et ce n'est malheureusement pas faisable, car le terme est très souvent entourer d'autres infos dans là même cellule.
Re : Rechercher/Marquer une liste de termes d'un tableau donné (autre que cells.find)
Salut macrbr et le forum
J'ai très bien réussi à faire cela avec cells.find (j'ai fait une boucle avec activecell.range.offset pour passer à chaque fois au terme suivant), mais c'était avant que ma liste de terme fasse plus de 10 000 lignes.
Malheureusement pour toi, la méthode .Find est la plus rapide (d'après les infos données).
Heureusement pour toi, la méthode Activecell.offset() est loin d'être la plus rapide .
Re : Rechercher/Marquer une liste de termes d'un tableau donné (autre que cells.find)
Merci beaucoup pour ta réponse Gorfael.
Effectivement je n'est pas donné le code, parceque je le trouve tellement tortilleux et ingérable que je ne veux pas faire fuir les gens (enfin je trouve perso, peut-être parceque je n'ai pas beaucoup d'expérience dans les macros).
Je te le mets quand même ci-dessous si vraiment tu es motivé. Mais j'ai fini par trouvé un moyen de contourner le soucis. Dans l'onglet qui contient la liste, j'ai ajouté une colonne avec la formule NB.SI((Compilation!$B$2:$L$5000;"*"&A2&"*"), compilation étant l'onglet des infos en vrac. Et puis je filtre et supprime tout ce qui est égal à 0. Ca me permet ensuite d'appliquer la macro sur ce qui reste, c'est à dire les termes qui apparaissent effectivement dans l'onglet vrac. Oufff.
Bon pour ceux que ça intéresse, je ne filtre pas sur la colonne des formules directement (pour éviter le temps de recalcul qui est énorme) mais sur une nouvelle colonne dans laquelle j'ai fait un copier - collage spécial de valeur uniquement, et je supprime ensuite la colonne des formules qui ne sert plus à rien. Il y a probablement plus simple mais c'est tout ce que mon cerveau a bien voulu pondre.
Voila la douloureuse macro Gorfael, si tu as à une idée pour appliquer ce type de recherche à une liste de 10 000 lignes avec un temps de recherche raisonnable, je prends avec plaisir.
Bonne soirée.
Sub MacroTriPrincipal()
Sheets("Liste").Select
Range("a01").Select
'Réinitialise l'onglet contenant la liste sur la première cellule A1
Do
Call MacroTriBoucle
Loop Until IsEmpty(ActiveCell)
'Appelle la macro qui va identifier en boucle toutes les infos jusqu'à la fin de la liste (1ère cellule vide)
Sheets("Compilation").Select 'Se replace sur l'onglet contenant les infos compilées et triées
ActiveSheet.Range("$A$1:$N$40000").AutoFilter Field:=13, Criteria1:="<>"
'Filtre pour n'avoir que les lignes non vides
Rows("2:40000").Select
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
Selection.AutoFilter Field:=13
'Enlève le filtre
End Sub
Sub MacroTriBoucle()
ActiveCell.Offset.Range("a02").Select
'Descend d'une ligne soit pour commencer au 1er terme (après menu) soit pour passer au terme suivant lors de la boucle
If ActiveCell = "" Then Exit Sub
'Revient à la MacroTriPrincipal si cellule vide (c-à-d fin de la liste)
Do
Sheets("Liste").Select 'se place sur l'onglet de la liste
Dim a As Range
b = ActiveCell
'défini b comme étant la cellule active de l'onglet de liste
Sheets("Compilation").Select 'va sur l'onglet contenant les infos compilées
Set a = Cells.Find(What:=b, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False)
'recherche la cellule active de l'onglet liste (part et non whole)
If Not a Is Nothing Then
a.Activate 'active la cellule si trouvée
Ligne = ActiveCell.Row
Cells(Ligne + 0, 1).Activate
Sheets("Liste").Select
ActiveCell.Offset.Range("b01").Copy 'copie la cellule suivant la cellule active de la liste
Sheets("Compilation").Select
ActiveCell.Offset.Range("M01").PasteSpecial
Sheets("Liste").Select
ActiveCell.Offset.Range("c01").Copy 'copie la cellule suivant la cellule active de la liste
Sheets("Compilation").Select
ActiveCell.Offset.Range("b01").PasteSpecial
ActiveSheet.Range("$A$1:$N$40000").AutoFilter Field:=13, Criteria1:="=" 'filtre sur les cellules vides afin que la recherche ne se fasse pas sur l'info déjà identifiée (loop sans fin)
Range("b2").Select
End If
Loop Until a Is Nothing 'Boucle la recherche du même terme jusqu'à ne plus trouver
Sheets("Liste").Select 'Se replace sur l'onglet de liste avant la prochaine boucle sur MacroTriBoucle
End Sub
Re : Rechercher/Marquer une liste de termes d'un tableau donné (autre que cells.find)
Salut macrbr et le forum
Effectivement je n'est pas donné le code, parce que je le trouve tellement tortilleux et ingérable que je ne veux pas faire fuir les gens (enfin je trouve perso, peut-être parce que je n'ai pas beaucoup d'expérience dans les macros).
Faut pas avoir peur : c'est anonyme et donc ce qu'on pense de toi t'est complétement indifférent (c'est l'avantage d'Internet ).
Et tout le monde a commencé un jour !
Demander de mettre un code n'est pas anodin : on peut souvent avoir des surprises par les modifications que peuvent y apporter des personnes qui le manie moins bien, mais qui ont une vision différente du problème.
Tes macros cumulent les méthodes de ralentissement du code :
- Appel Macro : étant un "vieux", je me suis intéressé à la programmation quand on calculait les instructions en "cycle d'horloge". On pouvait répéter plusieurs fois les mêmes lignes de code, plutôt que de faire un sous-programme, parce que c'était moins gourmand en temps de travail du microprocesseur. Un appel à sous programme demande au micro de stoker les infos qui sont en train d'être traitées, de charger le processeur avec le sous-programme, de l'exécuter, puis de faire l'inverse. Tu rajoute au traitement toutes les opérations de chargement/déchargement, qui sont effacées si tu traites directement dans la macro.
- Select/Selection : tu changes de feuilles à chaque donnée : ce n'est pas utile et ça ralentit énormément le code.
- ScreenUpDating : tu ne bloques pas la gestion écran : non seulement l'écran saute, mais le processeur perd du temps à remettre à jour l'affichage écran, alors que ça ne t'intéresse en rien.
Tout ça fait qu'on doit pouvoir améliorer assez facilement l'algorithme et les instructions. Enfin, à condition de savoir exactement ce que font (ou, sont supposées faire) tes macros. Et, désolé, mais vu l'heure, il vaut sans doute mieux que je remette à demain (enfin, tout à l'heure) un travail de ce style, surtout avec des instructions comme :
Code:
ActiveCell.Offset.Range("a02").Select
ActiveCell => la cellule qui est active (celle qui est sélectionnée quand il n'y en a que 1) :je comprends Offset => normalement, sert à un décalage, mais là, on fait un décalage de 0 ligne, 0 colonne, ce qui revient à dire que l'Offset ne sert à rien. Range("a02") => Revient vraisemblablement Range("A2") : sans en être totalement sûr, je dirais que ça signifie que c'est la cellule de gauche décalée d'une ligne. mais c'est tellement alambiquée... que ça me semble bizarre. Si c'est bien ça :
Re : Rechercher/Marquer une liste de termes d'un tableau donné (autre que cells.find)
Salut macrbr et le forum
Même après une (courte) nuit de sommeil, je n'arrive pas à extraire ton algorithme de ton code .
Dans l'onglet qui contient la liste, j'ai ajouté une colonne avec la formule NB.SI((Compilation!$B$2:$L$5000;"*"&A2&"*"), compilation étant l'onglet des infos en vrac. Et puis je filtre et supprime tout ce qui est égal à 0. Ca me permet ensuite d'appliquer la macro sur ce qui reste, c'est à dire les termes qui apparaissent effectivement dans l'onglet vrac.
connaissant le fichier, ayant longtemps travaillé dessus, c'est très clair. Mais ce n'est pas mon cas !
Ce que (je crois) déduire :
Un onglet avec une liste en A : Liste
Un onglet avec un ensemble de termes quelconques (dont la précédente liste) : Compilation
Je dois rechercher les tous termes de Liste qui figure dans Compilation
Jusqu'à là, je pense avoir compris. c'est après que ça se corse
- Tu fais du copier/coller spécial, sans définir les attribut de collage
- Tu filtres une plage arbitraire et tu colories toutes les lignes de cette plage (A1:N40000).
Remarques sur ton code :
- Tu masques les lignes de 2 à 8 et tu fais
Code:
Rows("1:10").interior.colorIndex=3
Si tu affiches les lignes, tu verras que les lignes masquées sont colorées.
- Tu travailles avec une plage que tu fixes arbitrairement : A1:N40000 => laisse Excel calculer lui-même sa plage. Soit en utilisant UsedRange, soit en limitant la plage à la dernière ligne utilisée.
- Tu fais une "erreur" d'nterprétation :
Code:
b = ActiveCell
'défini b comme étant la cellule active de l'onglet de liste
b n'est pas la cellule active : b est la valeur de la cellule active.on peut lire ton code comme
Code:
b = ActiveCell.Value
- Regarde l'aide de Offset : je trouve que tu l'emploies de manière bizarre. Mais ça ne veut pas dire que c'est faux
Code:
Range("F2").Range("F2").Select
permet de sélectionner... K3. Mais je pense qu'il y a nettement moins de risque d'erreur avec
Code:
Range("F2").Offset(1, 5).Select
Mais ce n'est qu'une opinion personnelle.
Chez moi, ça donnerait un code du genre :
Code:
Sub MacroTriPrincipal()
'Déclaration =================================
Dim F_L As Worksheet, F_C As Worksheet
Dim Cel_R As Range, Cel As Range, Cel_X As Range
'MEI =========================================
Set F_L = Sheets("Liste")
Set F_C = Sheets("Compilation")
'Traitement ==================================
For Each Cel In F_L.Range(F_L.[A2], F_L.Cells(Rows.Count, "A").End(xlUp))
'Pour chaque cellule de A2 à dernière non vide de A de feuille Liste
If Cel <> "" Then
'si cel n'est pas vide (en cas de cellule vide dans la plage)
Set Cel_R = F_C.Cells.Find(What:=Cel, After:=F_C.Range("A1").SpecialCells(xlCellTypeLastCell), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
'Cel_R = cellule de F_C qui contient la valeur de Cel sans s'occuper du casse
If Not (Cel_R Is Nothing) Then
'si cel_r existe
Set Cel_X = Cel_R
'cel_x=cel_r
Do
'boucler
If Not (Cel_X Is Nothing) Then
'si cel_x existe
Cel_X.Copy
'copier cel_X
F_L.Cells(Cel.Row, Columns.Count).End(xlToLeft).Offset(0, 1).PasteSpecial Paste:=xlPasteValues
'collage spécial (valeur) à droite de cel
End If
Set Cel_X = F_C.Cells.FindNext(After:=Cel_X)
'rechercher cel_x suivante à partir de cel_x
Loop Until Cel_R.Address = Cel_X.Address
'boucler jusqu'à ce qu'on retrouve cel_r
End If
End If
Next Cel
End Sub
Attention : je ne l'ai pas testé, ne sachant pas si je suis dans le vrai
on parcourt la plage colonne(A) de Liste
à chaque cellule non vide, on recherche si la valeur existe dans la feuille compilation : on part après la dernière cellule de la feuille, ce qui revient à commencer en A1
si on a une cellule, on copie sa valeur et on recherche la suivante dans une boucle dont on sort dès qu'on retrouve la première occurrence.
Normalement, mon algorithme tient la route . Cependant je conseille quand même de faire du pas-à-pas sur les deux, trois premières Cel, en regardant comment évoluent les variables (cel, Cel_R et Cel_X), et si ça va bien au bon endroit... on ne sait jamais !
Normalement, il devrait y avoir une légère amélioration dans le temps de traitement.
A+
Re : Rechercher/Marquer une liste de termes d'un tableau donné (autre que cells.find)
Salut Gorfael,
Merci beaucoup. Effectivement ta formule est super et beaucoup plus simplifiée que la mienne. En fait, j'ai fait des sous programmes parceque j'ai plusieurs boucles (comme je maitrise plus les boucles que les next), et je n'ai pas réussi à faire une boucle dans un boucle. Mais comme ta macro n'a qu'une boucle...
Ensuite moi je colorais (juste pour faire jolie) et masquais la ligne trouvée pour pas qu'il ne retombe dessus. Mais avec ta formule, plus besoin.
D'ailleurs en passant grâce à toi, je me rends compte que je confonds la sélection de plusieurs cellule avec activecell.offset.range et le saut de cellule vers la droite de activecell.offset. Merci
Pour la formule excel, en fait mon but est de faire un premier tri dans l'onglet liste, et donc =NB.SI(Compilation!$B$2:$L$5000;"*"&A2&"*") me permet d'avoir 0 sur les termes qui ne se trouvent pas dans l'onglet compilation et de les supprimer dans l'onglet liste. Sauf que j'ai découvert que supprimer plus de 5000 cellules qui contiennent des formules de ce type, ça mouline à l'infini. Donc j'ai fait une petite pirouette, certes maladroite, j'ai copier les valeurs uniquement dans la colonne suivante, pour ensuite supprimer celle avec les formules.
Donc j'ai testé, il y a juste un soucis mais j'ai du mal expliquer. Il me prend le terme trouvé dans compilation et me le copie à côté de la liste. Alors que ce que je veux c'est qu'il prenne le terme de la colonne B de la liste pour le coller dans la colonne M de la compilation (où je suis sûre qu'il n'y a rien). Dans ma macro, je n'ai pas trouvé d'autre solution que de revenir en début de ligne (dans l'onglet compilation) avec Ligne = ActiveCell.Row Cells(Ligne + 0, 1).Activate et ensuite de décaler jusque M avec activecell.offset. Je vois que dans la tienne, tu prends la fin du tableau à gauche, sauf la fin n'est jamais la même en fonction de la ligne (mais s'arrête toujours avant M).
Enfin bref, j'ai modifié et testé mais j'ai récidivé avec les activecell et offset et compagnie et le temps de traitement est encore long (500 termes recherché en 4 minutes, alors que j'en ai 10500), donc je pense que j'ai vais en plus garder mon système avec la formule NB.SI. Voila ce que ça donne (et j'ai un petit bug aussi) :
Code:
'Déclaration =================================
Dim F_L As Worksheet, F_C As Worksheet
Dim Cel_R As Range, Cel As Range, Cel_X As Range
'MEI =========================================
Set F_L = Sheets("Liste")
Set F_C = Sheets("Compilation")
'Traitement ==================================
For Each Cel In F_L.Range(F_L.[A2], F_L.Cells(Rows.Count, "A").End(xlUp))
'Pour chaque cellule de A2 à dernière non vide de A de feuille Liste
If Cel <> "" Then
'si cel n'est pas vide (en cas de cellule vide dans la plage)
Set Cel_R = F_C.Cells.Find(What:=Cel, After:=F_C.Range("A1").SpecialCells(xlCellTypeLastCell), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
'Cel_R = cellule de F_C qui contient la valeur de Cel sans s'occuper du casse
If Not (Cel_R Is Nothing) Then
'si cel_r existe
Set Cel_X = Cel_R
'cel_x=cel_r
Application.ScreenUpdating = False
Do
'boucler
If Not (Cel_X Is Nothing) Then
'si cel_x existe
Cel.Offset(0, 1).Copy
'copier colonne à côté de cel
Ligne = Cel_X.Row
Cells(Ligne + 0, 1).Activate
ActiveCell.Offset(0, 12).PasteSpecial Paste:=xlPasteValues
'ici j'ai un bug sur ce active.cell, si je ne suis pas l'onglet compilation, il prends l'onglet liste pour faire le collage, et je ne vois comment préciser F_C dans cette formule
'collage spécial (valeur) à droite de cel
End If
Set Cel_X = F_C.Cells.FindNext(After:=Cel_X)
'rechercher cel_x suivante à partir de cel_x
Loop Until Cel_R.Address = Cel_X.Address
'boucler jusqu'à ce qu'on retrouve cel_r
End If
End If
Next Cel
Application.ScreenUpdating = True
'
End Sub
Merci beaucoup pour le temps que tu consacres à mon casse-tête.
@+
Re : Rechercher/Marquer une liste de termes d'un tableau donné (autre que cells.find)
Re-Bonjour,
Eureka... Voici mon code final (ou presque)
Merci encore pour ton aide Gorfael.
Mais si je peux abuser, j'ai une autre question toujours sur .find. Je voudrais améliorer les résultats de ma recherche.
Sachant que le terme de mon onglet liste, se trouve dans des parties de cellules de l'onglet compilation sous ces formes là (supposons que Cel soit le terme recerché) :
"*" & " " & Cel
"*" & " " & Cel & " " & "*"
"*" & " " & Cel & "," & "*"
"*" & " " & Cel & "/" & "*"
"*" & " " & Cel & "-" & "*"
Cel
Cel & " " & "*"
Cel & "," & "*"
Cel & "/" & "*"
Cel & "-" & "*"
(C'est-à-dire qu'avant le terme il peux y avoir un espace ou rien, et après le terme, rien, ou un espace et plus ou les caractères ,/-)
Y a t'il un moyen de définir par exemple Cel_Variable = toutes ces possibilités et de faire ensuite cells.find sur Cel_Variable ?
J'ai essayé en mettant Or, ça ne marche pas, et en les mettant à la suite il ne me prend que le dernier.
Code:
'Déclaration =================================
Dim F_L As Worksheet, F_C As Worksheet
Dim Cel_R As Range, Cel As Range, Cel_X As Range
'MEI =========================================
Set F_L = Sheets("Liste Clients")
Set F_C = Sheets("Compilation")
'Traitement ==================================
For Each Cel In F_L.Range(F_L.[A2], F_L.Cells(Rows.Count, "A").End(xlUp))
'Pour chaque cellule de A2 à dernière non vide de A de feuille Liste
If Cel <> "" Then
Set maplage = F_C.Range("b2:c5000")
'si cel n'est pas vide (en cas de cellule vide dans la plage)
Set Cel_R = maplage.Cells.Find(What:=Cel, After:=F_C.Range("b2"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
'Cel_R = cellule de F_C qui contient la valeur de Cel sans s'occuper du casse
If Not (Cel_R Is Nothing) Then
'si cel_r existe
Set Cel_X = Cel_R
'cel_x=cel_r
Do
'boucler
If Not (Cel_X Is Nothing) Then
'si cel_x existe
Cel.Offset.Range("b01:C01").Copy
F_C.Activate
Ligne = Cel_X.Row
Cells(Ligne + 0, 1).Offset.Range("l01:M01").PasteSpecial Paste:=xlPasteValues
End If
Set Cel_X = maplage.Cells.FindNext(After:=Cel_X)
'rechercher cel_x suivante à partir de cel_x
Loop Until Cel_R.Address = Cel_X.Address
'boucler jusqu'à ce qu'on retrouve cel_r
End If
End If
Next Cel
Re : Rechercher/Marquer une liste de termes d'un tableau donné (autre que cells.find)
Bonsoir le fil
Juste un avis en passant
Pourquoi ne pas utiliser le filtre élaboré en VBA ?
Le code serait beaucoup plus rapide.
Un exemple pour illustre mon propos (à tester sur un classeur vierge)
Code:
Sub test()
Dim p As Range
credon
Feuil2.[A1] = "DONNEES"
Feuil2.[A2:A6] = [TRANSPOSE({2,5,17,22,30,40})]
Set p = Feuil2.[A1:A6]
p.Name = "crit"
'filtre élaboré en VBA
Range("A1:A50").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=["crit"], Unique:=False
End Sub
Code:
Sub credon() 'macro pour créer les données de test
With Feuil1
.[A1] = "DONNEES"
With .[A2:A50]
.Formula = "=ROW()"
.Value = .Value
End With
End With
End Sub
LookAt:=xlPart : partie de la cellule => correspond à un rechercher (<Ctrl>+<F>) sur la feuille de calcul dont l'option "Totalité de la cellule" est décochée <=> contient.
A+
Re : Rechercher/Marquer une liste de termes d'un tableau donné (autre que cells.find)
Hello tout le monde,
Merci pour votre aide. J'ai contourner la difficulté en faisant un premier tri avec la formule suivante, en même temps ça me permet de lancer la recherche .Find sur une liste moins longue :
Ce site utilise des cookies pour personnaliser le contenu, adapter votre expérience et vous garder connecté si vous vous enregistrez.
En continuant à utiliser ce site, vous consentez à notre utilisation de cookies.
.
