Discussion :

[Pierre Fauconnier]

Mais on s'en fout du critère!!!
Ryu,


Essaie de comprendre le fonctionnement d'un filtre avancé, b***!

Le critère, tu le formules. Si tu veux faire une différence de casse, tu utilises la fonction EXACT dans la formule du critère et basta!

Le VBA n'est pas là pour pallier une méconnaissance d'Excel. Maîtrisez les outils d'Excel et utilisez le VBA uniquement pour l'automatisation des procédures....

En quelle langue faut-il le dire pour que ça percole enfin?

Si demain, son critère change ne serait-ce que d'un iota, ou que les colonnes changent, que ce soit dans la source ou dans la cible, avec vos solutions, il devra mettre les mains dans le cambouis alors qu'il ne maîtrise pas votre code, alors qu'avec les filtres avancés, éventuellement en VBA, il ne devra RIEN CHANGER dans son code!

[Philippe Tulliez]

Bonjour Pierre,

Essaie de comprendre le fonctionnement d'un filtre avancé, b***!

C'est ce que je me tue à écrire depuis le début
Il y a deux discussions actuellement sur le forum avec le même type de demande et je n'arrête pas de dire de faire des tests manuellement, j'ai référencé le tutoriel sur le sujet qui est bourré d'exemples avec un classeur qui l'accompagne.

Je trouve que cela devient de plus en plus pénible. Tout avoir dans l'immédiat sans faire d'effort et sans essayer de comprendre.

Le VBA n'est pas là pour pallier une méconnaissance d'Excel. Maîtrisez les outils d'Excel et utilisez le VBA uniquement pour l'automatisation des procédures....

[Pierre Fauconnier]

Merci Philippe

[RyuAutodidacte]

Relisez svp

Car en post 22 je propose aussi les filtres avancés…

[Philippe Tulliez]

Bonjour,
Un dessin vaut mieux qu'un long discours

Manuellement


Correspondance VBA


Résultat soit manuellement soit en VBA


A lire ABSOLUMENT pour comprendre Les filtres avancés ou élaborés dans Excel

Désolé l'image ci-dessous ne correspond pas à ce qui se trouve au-dessus mais je n'arrive pas à la supprimer

[jpvba]

Bonjour a vous,


Que ce passe-t-il, soyer indulgent et patient ...

Si il y a une personne a blâmer ici c'Est moi ... Tous les gens ici on un but et c'Est de m'apporter de l'aide.


Garder cette esprit en tête cher ami ...

Je veux approfondir mes connaissances, je vais donc voir toute les portes que vous m'avez ouvertes. Oui il y en a des meilleurs que d'autre mais en les testant je vais avoir une "formation" dans chacune des nouvelles portes".


Nul besoin de ce critiquer ... la vie est trop courte pour perdre ce temps qui est précieux ... Prenez cette énergie a me donner des solutions et non vous manger la laine sur le dos.



JE suis désolé de posté ce commentaire et de jouer la police mais je crois que l'on peut s'entendre malgré nos différence (opinion, provenance, language, etc ...)

JE vais "closé" la portion du code que Ryu a posté et après je vois également la portion du filtre avancé.



désolé de posté ce commentaire mon but est de calmé la tension et non faire des reproches ....


J'ai plus que du respect en vers vous cher ami qui prenez du temps personnel pour aidé quelqu'un de l'autre côté de l'atlantique.

Je ne crois pas qu'un d'entre vous a voulu mal faire ...


désolé, je n'aime pas jouer à la police mais je vous respectent trop


amicalement JP

[Philippe Tulliez]

Voyons maintenant illustré le filtre avancé en exportant une liste unique

Manuellement


A l'aide du Code VBA


Résultat final

[RyuAutodidacte]

Je vois pas l'intérêt de mettre un -1 à @jpvba qui est courtois et qui n'as rien demandé de tout cela dans son post et qui ne demande qu'à apprendre en lui montrant plusieurs type de solution si il y'a.

Bien sûr que les filtres avancés est une bonne solution que j'ai aussi proposé,
Mais de là à faire tout cela en donnant des distributions de -1

Maintenant le vba ne s'arrête pas aux filtres avancés et je ne pense pas que MS a donné tous ces moyens pour faire de la masturbation intellectuelle.
Quand je suis venu sur le forum je n'ai pas voulu m'arrêter à un type de code pour mon apprentissage et je pense que c'est le cas de pas mal de monde venant ici.
Et je remercie tous ceux qui m'ont bien aidé afin d'évoluer comme : Marc-L , Jacques (unparia), Patrick, Robert, Menhir, Franck , Docmarti, Kiki29, Theze, eriiic, Qwazerty, boisgontierjacques via son site , joe.levrai, … j'en oubli (suis dans les transports)

Je pense qu'un forum est fait pour des échanges mais pas de ce type …

[jpvba]

Bonjour a vous,


J'ai modifié le code de Ryu afin d'y aller avec des références relatives et non absolues (bref le nom de la colonne au lieu de la lettre, au cas où quelqu'un bouge une colonne, je ne sias pas si c'est le bon jargon). Tous fonctionne a merveille.

Le processus de changer le X par "Extraction OK", me permet de re-générer le tout et seulement ajouté les nouvelles items sans avoir a recommencer. Cela me plais vraiment.

Je vais tenir compte également des commentaires de monsieur Fauconnier, je suis rendu là et apres j'attaque les filtres suggérés par monsieur Tulliez.



Je vous donne alors un "feedback"

[jpvba]

Bonjour a vous,


J'ai re-re-lu aujourd'hui le document fait par monsieur Tulliez et je me pose une question ou plutôt je presume que je dois absolument avoir une boucle pour créer l'en-tête pour chacun des onglets. Il y a-t-il une façon de dire copie les données ainsi que l'entete dans l'endroit auquel on exporte le tri ???



merci encore une fois les gars !!!


(Je commence a faire le code en question avec le filtre elaboré)

[Philippe Tulliez]

Bonjour,
Il faut effectivement dans la deuxième étape une boucle pour exporter chaque élément de la liste unique qui aura été crée lors de l'étape 1

Les étapes à suivre sont les suivantes (ceci est un exemple avec un pays mais c'est valable pour n'importe quelle colonne)

1. Extraire une liste unique à l'aide de la méthode AdvancedFilter (voir illustration ci-dessous)
   Cette liste étant crée elle deviendra le critère pour extraire ensuite chaque ligne contenant le pays
2. Créer une boucle équivalent au nombre de pays (le nombre de lignes de la liste unique) et utiliser les deux cellules du haut (Titre + nom du premier pays) comme zone des critères
   
   1. extraire à l'aide de la méthode AdvancedFilter les lignes contenant le pays (ligne 2 de la zone des critères) vers une feuille export
   2. supprimer la ligne deux de la zone des critères (ainsi la ligne 3 et les suivantes remontent et nouvelle ligne 3 devient le prochain pays dont on va extraire les données
   3. copier la feuille contenant les lignes exportées sur une autre feuille du même classeur ou dans un autre classeur
3. Effacer la zone des critères (En fin de boucle, il restera le titre "Pays" et le dernier pays qui aura été exporté)

Etape 1 : Illustration après exportation de la liste unique

[jpvba]

Bonjour monsieur Tulliez,


Je m pose cette question. Pour le point 1, devrais-je copier le tout dans une nouvelle feuille étant donné que je dois réutilisé les données plus tard dans le processus d'autant plus les données peuvent être utile pour l'usage des utilisateurs ??? De façon manuel il est obligatoire mais en code Est-ce le même principe ou puis-je enlevé cette étape et mettre e résultat dans la mémoire ???


Par la suite je filtre avec le nom d'établissement en éliminant les doublons et par la suite, je filtre avec ceux contenant un X ou X afin de réduire le nombre d,établissement (épuré ceux sans données)



Avant de le codé je le fais de façon manuel afin de comprendre la logique mais je ne sais pas comment VBA va réagir ... à suivre



un gros merci pour votre aide !!!



Petite modif:

J'essaye de trouvé comment je peux obtenir le double filtre ie celui du X ainsi que l'extraction de fournisseur unique ... J'enleve les doublons avec le filtre élaboré mais j'ai toujours des doublons au nouveau des nom d'établissements.

Il y a un astuce que je n'ai pas compris je crois ....

[Transitoire]

Bonsoir le forum,
L'ambiance, semble électrique!
Cher JPVBA, effectivement, les filtres élaborés, c'est super, vous pouvez extraire des données d'une Base de données suivant un ou plusieurs critères et cela comprenant des fonctions et ou des fonctions ou. J'en passe et des meilleures.
Vous pouvez extraire même sur d'autres feuilles que celle contenant la base. avec toutefois l'obligation de lancer l'extraction depuis la feuille contenant la zone d'extraction.
Votre zone d'extraction peut contenir l'intégralité des champs de la base, dans n'importe quel ordre. elle peut même se contenter de deux ou trois champs.
Vous pouvez prévoir pour une même base des tas de zones d'extractions différentes que vous nommerez séquentiellement (exemple ZonExtMois) dans votre code et ça marche, c'est magique
Par contre et c'est sans doute la raison pour laquelle Philippe Thulliez recommande le copier collé ci dessous, chaque nouvelle extraction, excel efface toutes les données présente sous la zone et ce jusqu'au bas de la page, pour les remplacer par les nouvelles données extraites.

copier la feuille contenant les lignes exportées sur une autre feuille du même classeur ou dans un autre classeur

Votre base de données ne bouge jamais, elle conserve son intégrité.
J'espère vous avoir apporté un peu de lumière et avoir susciter en vous l'envie de tater du filtre élaboré.
Cordialement

[jpvba]

Merci beaucoup transitoire pour votre réponse ... effectivement le poste a beaucoup d'énergie électrique ... elle contiens beaucoup de Pikachu... lol



LA petite astuce si je comprend bien est d'isolée le nom des établissements ayant un X ou x. Comme je ne les sais pas nécessairement de façon explicite étant donné qu'elles sont variable il faut que je définisse une variable correspondant au nom des établissements.

Donc mon critère il faudrait que je dise prend seulement et uniquement une fois le nom de l'établissement pour la génération de l'onglet.
C'Est a ce moment si que j'accroche.



J'ai essayé de mettre comme critère le fameux X et contenant <> comme etablissement et esay`é d'épuré les doublons mais ca ne fonctionne pas.


J'ai essayé de faire un filtre élaboré sur un filtre élaboré de façon manuel et ça enleve donc le premier filtre.



Il y a un astuce que je ne pige pas. L'autre façon serais d'enlever les doublons avec le fameux .RemoveDuplicates mais je sais que je m'égare des consignes de monsieur Tuilliez ...


Je continue donc a trouver cette petite astuce me permettant d'avoir les critère de sans doublons si contiens des X ...


Donc ca bouge dans ma boîte à poux !!!

[jpvba]

Si à l'étape 1 de monsieur Tulliez sois :

Extraire une liste unique à l'aide de la méthode AdvancedFilter (voir illustration ci-dessous)
Cette liste étant crée elle deviendra le critère pour extraire ensuite chaque ligne contenant le pays

Supposons que le "pays" est le x ou X dans mon cas. Donc j'ai plusieurs fois le même nom d'établissement qui va réapparaître dans la liste filtré. Je risque donc d'avoir de bouclé plusieurs fois la meme information. Il faudrais alors que je re filtre a nouveau le resultat en enlevant les doublons afin de garder seulement et uniquement 1 fois le nom de l'établissement avec critère x ou x. J'ajoute donc un autre filtre si je comprends bien ???



En mode manuel je ne peux fitrer à nouveau a fin d'extraire la première extraction ... sauf si je puisse copier dans un autre endroit l'extraction, que je n'ai malheureusement pas réussis a faire

[RyuAutodidacte]

Bonsoir,

à mon avis il est préférable de commencé par le nom d'établissement en unique (1ère zone de critère) à partir de là, vérifier si la feuille existe avec le nom de l'établissement;
dans le cas où celle-ci n'existe pas, la créer … ,
après je suis d'avis (je peux me tromper), d'utiliser un feuille tampon/réceptrice des données triées par le filtre élaboré dans laquelle on aura mis les en-tête voulu,
on pourra alors faire le filtre pour chaque nom d'établissement, ce qui permettra de copier l'ensemble des données à partir de la ligne 2 jusqu'à la dernière ligne dans la feuille voulue à chaque lancer de filtre

Feuille Tampon :

ID

Séq

Pair Impair

Étab

AcronyKe étab

Item Étab Moulinette

Item Établissement

Description Établissement

Couleur Établissement

Fournisseur Établissement

Fournisseur LAC

Marque Établissement

Catalogue Établissement

Format contrat Établissement

Qté annuelle requise

Prix à contrat Établissement

À valider - Établissements

Commentaire du GACEQ - Questions aux établissements

Feuille Filtre (exemple):

AcronyKe étab	À valider - Établissements
CHOKPQ 03-05	x
CHOKPQ 03-05	X

La Liste des noms d'établissement en unique :

AcronyKe étab

CHOKPQ 03-05

ONTPQ

CEWEP T-R

CHORQEVSOX

3-ROVOERET

PSKKEROOE

THETFSRD

WOCEQ

BEQQEVPE

BEOPCE

H-WOTPETOE

KONWONOE

on peut faire alors une boucle for each sur "La Liste des noms d'établissement en unique" permettant de remplir la "feuille filtre" (avec les vérifications d'existence des feuilles)
et faire les filtres élaborés au fur et à mesure sur chaque nom d'établissement dont les résultats seront copiés sur la 1ère ligne vide des feuilles concernées.

que fait on des "x" ou "X" ?? dans la feuille base on y fait un remplacement par "Extraction OK" une fois tous les noms d'établissement passé au crible

J'ai peut être oublié des choses mais on verra par la suite en tout cas le principal est là

Edit :

Pour Feuille Filtre, cela devrait suffire :

AcronyKe étab	À valider - Établissements
ONTPQ	x

Bien sur pour les 2 feuilles supplémentaires (la feuille Tampon et la feuille filtre) ce la peut être fait manuellement pour la mise en place puisque les paramètres ne bougeront pas,
Sauf => La Liste des noms d'établissements en unique (MAJ si des nouveaux) et les paramètres du filtre => à actualiser par code pour les filtrages

AcronyKe étab				AcronyKe étab	À valider - Établissements
CHOKPQ 03-05				ONTPQ	x
ONTPQ
CEWEP T-R
CHORQEVSOX
3-ROVOERET
PSKKEROOE
THETFSRD
WOCEQ
BEQQEVPE
BEOPCE
H-WOTPETOE
KONWONOE

[Philippe Tulliez]

Bonjour,

Je m pose cette question. Pour le point 1, devrais-je copier le tout dans une nouvelle feuille étant donné que je dois réutilisé les données plus tard dans le processus d'autant plus les données peuvent être utile pour l'usage des utilisateurs ??? De façon manuel il est obligatoire mais en code Est-ce le même principe ou puis-je enlevé cette étape et mettre e résultat dans la mémoire ???

Il y a plusieurs possibilités
Mettre le résultat en mémoire, n'est pas la philosophie dans l'utilisation de la méthode AdvancedFilter qui a une zone source, une zone des critères et une zone cible qui dans les deux cas sont des plages de cellules qui peuvent être situées n'importe où. Sur la même feuille dans des feuilles différentes du même classeur ou situées chacune d'elles dans des classeurs différents (C'est le principe de la Programmation Orientée Objet POO), une plage de cellules étant un objet. Il faut bien entendu préciser la parentalité de celles-ci (La feuille et le classeur)
L'avantage d'utiliser une méthode correspondant à une fonctionnalité d'excel (ici pour AdvancedFilter, le filtre avancé d'excel, c'est que l'on peut faire des tests manuels pour la compréhension du code VBA (même si pour cette méthode malheureusement subsiste un bug quand il s'agit d'exporter vers une autre feuille que la source mais qui peut être palliée en effectuant une importation. C'est expliqué dans le tutoriel Les filtres avancés ou élaborés dans Excel

[EDIT] La suite
Organisation du classeur
Prévoir trois feuilles

1. Feuille source : Contient la liste des données avec en ligne 1 les titres
2. Feuille cible : Servira à exporter chaque élément de la liste unique
3. Feuille paramètre : (nommée par exemple Paramater) Contient les 4 cellules de la zone des critères (par exemple A1;B2, une cellule avec le titre de la colonne dont on doit exporter la liste unique (par exemple D1), une cellule qui contiendra la liste des étiquettes de colonne que l'on doit exportées (séparée par un point virgule)

Idéalement ces cellules devraient être nommées (Gestionnaire des noms)
Idéalement aussi il faudrait utiliser un CodeName pour les feuilles. Lire éventuellement à ce sujet VBA – CodeName d’une feuille Excel

Illustration de la feuille [Parameter]
Les cellules en couleurs sont les cellules à nommer (en vert la zone des critères, en orange la cellule contenant le titre de la liste unique, en bleu la liste des étiquettes dont les colonnes doivent être exportées)


Pour remplir la cellule G1 facilement, voici le code d'une fonction personnalisée qui permet de concaténer les étiquettes de colonne (à placer dans un module standard)

Code :

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

1
2
3
4
5
6
7
8
9
Function JoinText(LabelRange As Range) As String
  ' Concatenation de la valeur de chaque valeur des cellules sélectionnées
  Const Delimiter = ";"
  Dim c As Range
  For Each c In LabelRange
   JoinText = JoinText & c.Value & Delimiter
  Next
  JoinText = Left(JoinText, Len(JoinText) - 1)
End Function

Une fois installée, il suffit de l'utiliser dans Excel comme une fonction classique.
Sélectionner chaque cellule concernée en appuyant sur la touche Contrôle (cellules non contiguës)
Avant de confirmer ne pas oublier d'entourer les références par des parenthèses.
Ensuite il suffit de faire un Copier/Collage spécial-Valeur

Exemple

Code :

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

=JoinText((NOMS!A1:C1;NOMS!F1;NOMS!H1))

[jpvba]

Super, je travail la dessus et je vous reviens d'ici peu !!!



J'ai une question que vous pouvez me répondre ou que je risque de découvrir par moi même ... la vitesse d'exécution va-t-elle etre vraiment optimale car je vais créé quelque feuilles.



à suivre.




Edit

tel que conseillier, j'essaye d'utiliser le codename mais j'arrive avec un message comme quoi la variable est non définie (critere) voir code ci-dessous

Code :

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

1
2
3
4
5
Sheets.Add Before:=Sheets("R_MoulinetteAValider")
ActiveSheet.Name = "critere"
ActiveSheet.Parent.VBProject.VBComponents(ActiveSheet.CodeName).Properties("_CodeName") = "critere"
 
critere.Range("a1").value = Sheets("R_MoulinetteAValider").[acronyme_etab_titre].value

comment alors dire que mon critere n'st pas une variable mais le codename ???


merci encore un fois pour votre aide ... j'apprends énormément !!!


Edit 2


Dans mes tests manuel, je peux seulement copier dans un nouvel emplacement dans une plage de la feuille active sinon j'ai un message d'erreur

Vous pouvez seulement copier des données filtrées vers la feuille active.


Je croyais pouvoir avoir ma feuille source (feuille de travail), une feuille de critere et une feuille de résultats ...

Va falloir que je compose avec cela pour mon exportation. Je comprend maintenant la ligne

3.copier la feuille contenant les lignes exportées sur une autre feuille du même classeur ou dans un autre classeur

au lieu de faire la création lors de la boucle

[jpvba]

Voici ce que j'ai fait a présent

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
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
Sub genere_onglets_etablissement_filtre_elab()
 
'On Error GoTo errorhandler:
 
    Dim x As Long
    Dim LettreVoulue As String
 
    Dim nom_etablissement As Variant
    Dim start As Single
    Dim finish As Single
 
    start = Timer
 
    Application.ScreenUpdating = False
 
    If sheetExists("R_MoulinetteAValider") = False Then
    MsgBox "Erreur d'exécution, feuille R_MoulinetteAValider manquante", vbCritical
    Exit Sub
    End If
 
    Application.DisplayAlerts = False
 
    If sheetExists("travail") = True Then
    Sheets("travail").Delete
    Else
    End If
 
    Application.DisplayAlerts = True
 
'nettoie le nom des etablissements provenant du LAC afin d'éviter d'avoir 2 onglets
    LettreVoulue = TrouveLettreColonne([acronyme_etab])
    Range(LettreVoulue & 2, LettreVoulue & LastLignUsedInSheet("R_MoulinetteAValider")).Select
    Selection.Replace What:=Chr(47), Replacement:=Chr(32)
    Selection.Replace What:=Chr(92), Replacement:=Chr(32)
    Selection.Replace What:=Chr(91), Replacement:=Chr(32)
    Selection.Replace What:=Chr(93), Replacement:=Chr(32)
    nettoyerseul
 
 
'création d'une copie de la feuille afin de garder les information pour plus tard
 
Sheets("R_MoulinetteAValider").Copy Before:=Sheets("R_MoulinetteAValider")
ActiveSheet.Name = "travail"
 
 
 
     With Sheets("R_MoulinetteAValider")
 
        With Union(.Range(.Range(TrouveLettreColonne([ID_titre]) & 1), .Range(TrouveLettreColonne([prix_contrat_titre]) & 1)), _
        .Range(.Range(TrouveLettreColonne([valider_etablissement_titre]) & 1), .Range(TrouveLettreColonne([commentaire_etablissement_titre]) & 1)))
            With .Copy
                With Sheets("travail").Range("bA1")
                .PasteSpecial Paste:=xlPasteColumnWidths
                .PasteSpecial Paste:=xlPasteFormats
                .PasteSpecial Paste:=xlPasteValues
                End With
                With Sheets("travail").Range("bu1")
                .PasteSpecial Paste:=xlPasteColumnWidths
                .PasteSpecial Paste:=xlPasteFormats
                .PasteSpecial Paste:=xlPasteValues
                End With
            End With
        End With
    End With
 
    Range("Ba2").CurrentRegion.Name = "plage_result"
    Range("a2").CurrentRegion.Name = "plage_debut"
 
 Range("av1").value = Sheets("R_MoulinetteAValider").[valider_etablissement_titre].value
 Range("av2").value = "X"
 Range("aw1").value = Sheets("R_MoulinetteAValider").[acronyme_etab_titre].value
 Range("av1:av2").Name = "critere_1"
 
   Application.DisplayAlerts = False
   Range("plage_debut").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
   Range("critere_1"), CopyToRange:=Range("plage_result"), Unique:=False
 
 
Range("BE1:BE47").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "AW1:AW2"), Unique:=True
 
Application.DisplayAlerts = True
 
 
 
 
 
finish = Timer

J'ai eu la partie

Code :

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

1
2
Range("BE1:BE47").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "AW1:AW2"), Unique:=True

avec l'enregisteur de macro mais malheureusement en réexécutant, le tout ne fonctionne pas ...


En terme de temps je suis à 2 seconde, plus lent que le code de Ryu. Oui plus facile à lire mais plus lent.



Je vais quand meme continuer a coder de cette façon afin de comprendre le advancefilter.



amicalement JP

[Philippe Tulliez]

Bonjour,

Dans mes tests manuel, je peux seulement copier dans un nouvel emplacement dans une plage de la feuille active sinon j'ai un message d'erreur
Vous pouvez seulement copier des données filtrées vers la feuille active.

Si tu as lu le tutoriel sur les filtres avancés et tout particulièrement le chapitre VII-C. Exportation vers une autre feuille que la table de données, c'est bien expliqué qu'il y a un bug et que manuellement effectivement il faut partir de la feuille où aura lieu l'exportation.

tel que conseillier, j'essaye d'utiliser le codename mais j'arrive avec un message comme quoi la variable est non définie (critere) voir code ci-dessous

As-tu lu le billet VBA – CodeName d’une feuille Excel que je t'ai conseillé de lire ?