Home

Besoin Aide MACRO FILTRE AVANCE MULTI CRITERES avec Criteres > & < .

%3CLINGO-SUB%20id%3D%22lingo-sub-776433%22%20slang%3D%22fr-FR%22%3ENeed%20Help%20MACRO%20FILTRE%20AVANCE%20MULTI%20CRITERES%20with%20Criteres%26lt%3B%20.%26gt%3B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-776433%22%20slang%3D%22fr-FR%22%3E%3CP%3EHello%20to%20All!%2C%20I%20have%20EXCEL%202007%2064bits%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E.%20I%20attach%20my%20file%20Example%20..%3A%20MACRO%20FILTRE%20AVANCE%20MULTI%20CRITERES%20with%20Criteres%20Superior%20-%20Inferieur.xlsm%3F%3C%2FP%3E%3CP%3E.%20I%20have%20a%20sheet%20with%20several%20columns%20of%20data%2C%20and%20several%20criteria%20%26lt%3B%26gt%3B%20%20%3CBR%20%2F%3Ewith%20data%20to%20filter%20selected%20in%20and%20.%20When%20I%20make%20filters%20with%20the%20function%20%22advanced%20filters%22%20in%20%22manual%22%20it%20works%2C%20I%20have%20my%20result%20but%20it%20does%20not%20recover%20the%20Value%20to%20the%20box%20concerned%2C%20there%20is%20nothing.%3C%2FP%3E%3CP%3E.%20I'm%20looking%20to%20create%20a%20Macro%20with%20%22advanced%20filter%22%20values%20and%26lt%3B%2C%20mais%20celle%20ci%20ne%20fonctionne%20pas%20%2C%20soit%20quand%20cela%20s'affiche%2C%20il%20manque%20la%20moiti%C3%A9e%20des%20Valeurs%20filtr%C3%A9es%20%2C%20soit%20il%20n'y%20a%20aucune%20valeur%20qui%20se%20s%C3%A9lectionne%20au%20filtrage%2C%20tout%20%22s'%C3%A9fface%20d'affichage%22%20et%20surtout%20rien%20aucun%20r%C3%A9sultat%20affich%C3%A9%20%C3%A0%20la%20c%C3%A9llule%20voulue.%20mais%3D%22%22%20celle%3D%22%22%20ci%3D%22%22%20ne%3D%22%22%20fonctionne%3D%22%22%20pas%3D%22%22%20%2C%3D%22%22%20soit%3D%22%22%20quand%3D%22%22%20cela%3D%22%22%20s'affiche%2C%3D%22%22%20il%3D%22%22%20manque%3D%22%22%20la%3D%22%22%20moiti%C3%A9e%3D%22%22%20des%3D%22%22%20valeurs%3D%22%22%20filtr%C3%A9es%3D%22%22%20%2C%3D%22%22%20soit%3D%22%22%20il%3D%22%22%20n'y%3D%22%22%20a%3D%22%22%20aucune%3D%22%22%20valeur%3D%22%22%20qui%3D%22%22%20se%3D%22%22%20s%C3%A9lectionne%3D%22%22%20au%3D%22%22%20filtrage%2C%3D%22%22%20tout%3D%22%22%20%22s'%C3%A9fface%3D%22%22%20d'affichage%22%3D%22%22%20et%3D%22%22%20surtout%3D%22%22%20rien%3D%22%22%20aucun%3D%22%22%20r%C3%A9sultat%3D%22%22%20affich%C3%A9%3D%22%22%20%C3%A0%3D%22%22%20la%3D%22%22%20c%C3%A9llule%3D%22%22%26gt%3B%3C%2FP%3E%3CP%3E.%20When%20I%20create%20the%20Macro%20everything%20is%20fine%2C%20then%20I%20stop%20the%20recording.%20The%20Module%20is%20created%20all%20is%20well.%20%3CBR%20%2F%3E.%20...%20or%2C%202nd%20test%2C%20in%20code%2C%20I%20create%20the%20module%2C%20and%20I%20enter%20this%20VBA%20code%2C%20then%20I%20record.%3C%2FP%3E%3CP%3E.%20Once%20all%20the%20ranges%20reset%2C%20with%20the%20previous%20manual%20cleared%20result%2C%20it's%20when%20I%20run%20the%20Macro%2C%20that%20there%20it%20doesn't%20work.%20%3CBR%20%2F%3E.%20The%20Excel%20sequencing%20of%20the%20Macro%2C%20does%20not%20work%2C%20and%20gives%20me%20only%20part%20of%20the%20results.%3C%2FP%3E%3CP%3E.%20Simple%20Code%20of%3CBR%20%2F%3EMacro%3A%20Sub%3CBR%20%2F%3EMacro1()%3CBR%20%2F%3E%3CBR%20%2F%3E'%20Macro1%20Macro'%3C%2FP%3E%3CP%3E'%3CBR%20%2F%3ERange%20(%22B1%3AJ37%22).%20AdvancedFilter%20Action%3A%20'xlFilterCopy'%2C%20CriteriaRange%3A%3CBR%20%2F%3E'Range'%20('J65%3AK105')%2C%20CopyToRange%3A%20'Range%3CBR%20%2F%3E('AI66')%2C%20Unique%3A%20'False%20Range(%22B1%22).%20Select%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E..%20This%20code%2C%20it%20does%20not%20work%2C%20it%20always%20gives%201%20single%20result%20of%20the%20filter%20rather%20than%20the%205%20desired.%3C%2FP%3E%3CP%3E.%20In%20short%2C%20a%20Macro%20%22Advanced%20Filter%22%20%26lt%3B%20%2C%3D%22%22%20ne%3D%22%22%20fonctionne%3D%22%22%26gt%3B%20%20%3CBR%20%2F%3Ewith%20Values%20and%20.%20I%20tried%20to%20make%20this%20Macro%20on%20several%20versions%20of%20Excel%20(2007%2C2010%2C2016)%20the%20execution%20of%20the%20Macro%20does%20not%20work.%3C%2FP%3E%3CP%3E.%20I%20don't%20want%20to%20go%20into%20VBA%20Code%2C%20because%20the%20different%20Filters%20and%20%26lt%3B%20%2C%3D%22%22%20seront%3D%22%22%20fixes%3D%22%22%20et%3D%22%22%20immuables%2C%3D%22%22%20sauf%3D%22%22%20si%3D%22%22%20je%3D%22%22%20les%3D%22%22%20reprends%3D%22%22%20les%3D%22%22%20uns%3D%22%22%20apr%C3%A8s%3D%22%22%20les%3D%22%22%20autres%3D%22%22%20en%3D%22%22%26gt%3B%20%20%3CBR%20%2F%3E.%20Off%20I%20have%20a%20hundred%20filters%20to%20do%20like%20this%2C%20it%20forces%20me%20to%20make%20Macros.%3C%2FP%3E%3CP%3E.%20The%20advantage%20of%20having%20a%20Macro%20Excel%2C%20is%20that%20you%20can%20change%20%26lt%3B%2C%20constamment%2C%3D%22%22%20autrement%3D%22%22%20dit%3D%22%22%20les%3D%22%22%20filtres%3D%22%22%20en%3D%22%22%26gt%3B%20%26lt%3B%20sont%20%C3%A9volutifs%20instantan%C3%A9ment%2C%20mais%20pas%20en%20VBA%20ou%26nbsp%3B%20les%20valeurs%20sont%20fig%C3%A9es%20dans%20un%20code.%20sont%3D%22%22%20%C3%A9volutifs%3D%22%22%20instantan%C3%A9ment%2C%3D%22%22%20mais%3D%22%22%20pas%3D%22%22%20en%3D%22%22%20vba%3D%22%22%20ou%26nbsp%3B%3D%22%22%20les%3D%22%22%20valeurs%3D%22%22%20sont%3D%22%22%20fig%C3%A9es%3D%22%22%20dans%3D%22%22%20un%3D%22%22%26gt%3B%20%20the%20Filter%20Values%20-%20and%26nbsp%3B%3C%2FP%3E%3CP%3E.%20Someone%20could%20tell%20me%20what%20is%20wrong%20in%20this%20Macro%2C%20or%20maybe%20I%20have%20not%20checked%20the%20right%20options%20in%20%22tools%22%20or%20supplements%20of%20excel%20...%3F%3C%2FP%3E%3CP%3E.%20Or%20maybe%20switch%20to%20Access%3F%20%3CBR%20%2F%3E.%20Can%20you%20give%20me%20an%20example%20that%20works%20when%20you%20run%20the%20Macro%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-776433%22%20slang%3D%22fr-FR%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-778638%22%20slang%3D%22en-US%22%3ERe%3A%20Besoin%20Aide%20MACRO%20FILTRE%20AVANCE%20MULTI%20CRITERES%20avec%20Criteres%20%26gt%3B%20%26amp%3B%20%26lt%3B%20.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-778638%22%20slang%3D%22en-US%22%3EVBA%20speaks%20American.%20If%20you%20replace%20all%20.%20characters%20in%20the%20filter%20area%20with%20%2C%20the%20macro%20will%20work.%20Unfortunately%20then%20the%20manual%20method%20will%20no%20longer%20work.%20Best%20is%20to%20set%20up%20the%20advanced%20filter%20criteria%20to%20use%20a%20formula%20which%20returns%20TRUE%20or%20FALSE.%20See%20the%20Help%20for%20Advanced%20filter.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-778845%22%20slang%3D%22en-US%22%3ERe%3A%20Besoin%20Aide%20MACRO%20FILTRE%20AVANCE%20MULTI%20CRITERES%20avec%20Criteres%20%26gt%3B%20%26amp%3B%20%26lt%3B%20.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-778845%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383259%22%20target%3D%22_blank%22%3E%40Jeanluc31470%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20like%20the%20expression%20%22VBA%20speaks%20America%22%20...%20that's%20very%20true%3C%2FP%3E%3CP%3ETo%20add%2C%3C%2FP%3E%3CP%3EExtracting%20records%20based%20on%20multiple%20criteria%20can%20be%20done%20in%20so%20many%20ways%3A%3C%2FP%3E%3CUL%3E%3CLI%3EUsing%20advanced%20filter%20in%20VBA%20as%20you%20did%3C%2FLI%3E%3CLI%3EUsing%20the%20Find%20command%20and%20Looping%20over%20the%20records%20in%20VBA%3C%2FLI%3E%3CLI%3ERecording%20a%20Macro%20for%20Advanced%20Filter%20in%20regular%20Excel%3C%2FLI%3E%3CLI%3EUsing%20Formulas%20and%20Functions%20(which%20is%20dynamic)%3C%2FLI%3E%3C%2FUL%3E%3CP%3EI%20am%20attaching%20herewith%20a%20sample%20File%20where%20I%20used%203%20of%20these%20methods%20on%20different%20sheets%20%26gt%3B%26gt%3B%20Change%20the%20criteria%20in%20Row%202%20from%20the%20drop%20list%20and%20records%20are%20automatically%20extracted.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20also%20watch%20my%20tutorial%20on%20how%20I%20did%20it%20by%20following%20the%20link%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9yhbh6l_rag%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9yhbh6l_rag%3C%2FA%3E%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3EBonne%20Chance%26nbsp%3B%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E
Jeanluc31470
Occasional Visitor

Bonjour à Toutes et à Tous !, j'ai EXCEL 2007 64bits,

 

.Je vous joins mon fichier Exemple ..: MACRO FILTRE AVANCE MULTI CRITERES avec Criteres Supérieur & Inferieur.xlsm ?

.J'ai une feuille avec plusieurs colonnes de données , et plusieurs critères avec des données a filtrer sélectionnées en > et <.
.Lorsque je fais des filtres avec la fonction "filtres avancé" en " manuel " cela marche , j'ai mon résultat mais il ne récupère pas la Valeur à la case concernée, il n'y a rien.

. Je cherche à créer une Macro avec "filtrer avancé " les valeurs > et <, mais celle ci ne fonctionne pas , soit quand cela s'affiche, il manque la moitiée des Valeurs filtrées , soit il n'y a aucune valeur qui se sélectionne au filtrage, tout "s'éfface d'affichage" et surtout rien aucun résultat affiché à la céllule voulue.

.Lorsque je crée la Macro tout vas bien, puis j'arrete l'enregistrement. Le Module est créé tout va bien.
. ...ou , 2 ème essai , en code , je crée le module , et je rentre ce code VBA, puis j'enregistre.

.Une fois toutes les plages remises à zéro , avec le précédent résultat effacé manuel, c'est lorsque j'exécute la Macro , que là elle ne fonctionne pas.
.Le séquençage Excel de la Macro , ne fonctionne pas , et me donne qu'une partie des résultats.

.Code simple de la Macro:
Sub Macro1()
'
' Macro1 Macro
'

'
Range("B1:J37").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"J65:K105"), CopyToRange:=Range("AI66"), Unique:=False
Range("B1").Select
End Sub


..Ce code , cela ne marche pas , cela donne toujours 1 seul résultat du filtre plutôt que les 5 souhaités.

.En bref une Macro "Filtre avancé" avec des Valeurs > et < , ne fonctionne pas….
.J'ai tenté de faire cette Macro sur plusieurs versions d'Excel (2007,2010,2016) l'execution de la Macro ne marche pas.

.Il ne faut pas que je passe en Code VBA , car les différents Filtres > et < , seront fixes et immuables, sauf si je les reprends les uns après les autres en codage.
.Hors j'ai une centaine de filtres à faire comme cela , cela m'oblige à faire des Macros.

.L'avantage d'avoir une Macro Excel , est que l'on peut modifier les Valeurs des Filtres > et <, constamment, autrement dit les Filtres en > et < sont évolutifs instantanément, mais pas en VBA ou  les valeurs sont figées dans un code. 

.Quelqu'un pourrait il me dire ce qui ne va pas dans cette Macro , ou peut etre n'ai je pas coché les bonnes options dans les "outils" ou compléments d'excel… ?

.Ou peut etre passer en Access ?
.Pouvez vous me donner un exemple qui marche lorsque vous éxecutez la Macro ?

2 Replies
VBA speaks American. If you replace all . characters in the filter area with , the macro will work. Unfortunately then the manual method will no longer work. Best is to set up the advanced filter criteria to use a formula which returns TRUE or FALSE. See the Help for Advanced filter.
Highlighted

@Jeanluc31470 

I like the expression "VBA speaks America" ... that's very true

To add,

Extracting records based on multiple criteria can be done in so many ways:

  • Using advanced filter in VBA as you did
  • Using the Find command and Looping over the records in VBA
  • Recording a Macro for Advanced Filter in regular Excel
  • Using Formulas and Functions (which is dynamic)

I am attaching herewith a sample File where I used 3 of these methods on different sheets >> Change the criteria in Row 2 from the drop list and records are automatically extracted.

 

You can also watch my tutorial on how I did it by following the link:

https://www.youtube.com/watch?v=9yhbh6l_rag

Hope that helps

Bonne Chance   

Nabil Mourad

The most common Task in Excel is to Extract Records from a list based on Multiple conditions... Then use it for creating reports for Sales, Clients, Products, Students, Patients... Just name it. Although I show you it could be done (easily) in VBA, however, in this tutorial I walk you step by step
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies