Discussion :

[Kevin Brandt]

Bonjour,

Je veux faire un tri avancé avec une macro ou du vba. J'ai une base de données à trois champs, dans les colonnes A, B et C.
J'utilise l'outil filtre élaboré pour extraire des enregistrements, filtrés sur plusieurs critères.

Pour cela, j'utilise la fonction DECALER, avec la syntaxe suivante:

Code :

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

=DECALER(Feuil1!$E$1;;;MAX(SI($E$2:$F$8<>"";LIGNE($E$2:$F$8);0));2)

Dans les colonnes E et F, j'ai mis les champs sur lesquels je fais les filtres qui servent de référence au filtre élaboré
Quand je lance manuellement le filtre élaboré, je remplis la boîte de dialogue "critères" avec la chaîne ci-dessus, et ça marche

Par contre, je veux automatiser mes requêtes par une macro - ce qui évite de modifier le code en fonction du nombre de lignes remplies dans les colonnes E et F
, mais quand j'enregistre manuellement le filtre automatique, le code généré est le suivant:

Code :

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

1
2
    Range("A1:C9").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "E1:F3"), CopyToRange:=Range("J1:L1"), Unique:=False

Le générateur de macro m'a donc écrit en dur ce que je lui avais donné en paramétré.
Comment mettre l'argument de CriteriaRange en variable ?

J'ai essayé comme cela, mais ça ne marche pas:

Code :

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

1
2
3
 Range("A1:C9").AdvancedFilter Action:=xlFilterCopy, _ 
 CriteriaRange:=Range(" = DECALER(Feuil1!$E$1;;;MAX(SI($E$2:$F$8<>"";LIGNE($E$2:$F$8);0));2)"), _ 
 CopyToRange:=Range("J1:L1"), Unique:=False

Ça fait deux heures que je tourne en rond, quelqu'un peut-il m'aider ?
Merci

[Philippe Tulliez]

Bonjour,
Voilà comment, je procède pour exporter des données se trouvant dans une plage commençant en cellule A1 d'une feuille nommée [db] vers la feuille nommée [Export] à partir de la cellule A1, les critères se trouvant dans une plage débutant en cellule E1 de la feuille nommée [Param]

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Sub AdvancedFilter()
 ' Déclaration des variables
 Dim rngSource As Range, rngCriteria As Range, rngTarget As Range
 With ThisWorkbook
  Set rngSource = .Worksheets("db").Range("A1").CurrentRegion
  Set rngCriteria = .Worksheets("Param").Range("E1").CurrentRegion
  Set rngTarget = .Worksheets("Export").Range("A1")
 End With
 rngTarget.Worksheet.Cells.Clear ' Efface toutes les cellules de la feuille cible
 rngSource.AdvancedFilter xlFilterCopy, rngCriteria, rngTarget ' Exporte les données
End Sub

[Kevin Brandt]

Merci, mais je crois que le problème doit se trouver au niveau de la syntaxe.
la difficulté vient de l'argument de CriteriaRange
Habituellement, c'est quelque chose du genre CriteriaRange:= Range ("A1:B1"). Il attend donc un objet Range, que l'on définit avec une chaîne de caractère (A1:B1).

Or ici, je ne maîtrise pas suffisamment la fonction Décaler. Et je ne sais pas ce que me renvoie:

Code :

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

=DECALER(Feuil1!$E$1;;;MAX(SI($E$2:$F$8<>"";LIGNE($E$2:$F$8);0));2)

Je ne trouve rien qui puisse m'aider en VBA.
1. Ça me renvoie une plage de cellule, donc Range ? Ce qui donnerait

Code :

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

CriteriaRange:= DECALER(Feuil1!$E$1;;;MAX(SI($E$2:$F$8<>"";LIGNE($E$2:$F$8);0));2)

mais ça ne passe pas au niveau de la syntaxe VBA

2. Si je considère que ça me renvoie une chaîne de caractère qui identifie une plage, cela donne:

Code :

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

 CriteriaRange:=Range(" = DECALER(Feuil1!$E$1;;;MAX(SI($E$2:$F$8<>"";LIGNE($E$2:$F$8);0));2)"), _

mais la macro plante avec message d'erreur disant que l'objet Range est incorrect

Une autre manière de formuler la question: comment récupérer ce que me renvoie la fonction Décaler.
Merci d'avance

[78chris]

Bonjour

La fonction DECALER est utile quand on travaille par formules.

Le VBA permet d'autres solutions : Philippe, que je salue , te propose

Code :

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

Range("E1").CurrentRegion

.

As-tu essayé ?

Si tu tiens à DECALER passe par une formule nommée et utilise le nom dans VBA.

[Philippe Tulliez]

Bonjour,
Sauf s'il est impératif pour toi d'utiliser la formule DECALER, je trouve que tu te compliques la vie.
Si tu utilises le VBA pour faire une exportation à l'aide de la méthode AdvancedFilter, le code que je t'ai proposé est bien plus approprié.

Les problèmes que tu rencontres
1) Tu écris la formule en français alors qu'en VBA il faut l'écrire en anglais
2) Tu veux renvoyer la valeur d'une formule que seul Excel connaît à un argument de la méthode AdvancedFilter qui attend un objet Range
etc.
Donc soit tu utilises le code que je t'ai proposé soit tu tentes d'écrire la bonne syntaxe avec DECALER que tu utilises en VBA en la traduisant avec la méthode Offset de la propriété WorksheetFunction ou avec la fonction VBA EVALUATE qui permet d'évaluer une formule Excel en VBA.
Je te renvoie vers l'aide en ligne pour cela.

Pour utiliser une plage dynamique dont la première cellule commence en A1 à l'aide de la formule DECALER, voici la syntaxe

Code :

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

=DECALER(A1;0;0;NBVAL(A:A);NBVAL(1:1))

soit placée dans le gestionnaire des noms comme formule nommée maPlage

Code :

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

=DECALER(Export!A1;0;0;NBVAL(Export!A:A);NBVAL(Export!1:1))

Cette fonction ne fonctionne correctement qu'à condition qu'il n'y ait pas d'autres données que celle de la plage concernée sur la première ligne et première colonne de la feuille et que celles-ci n'aient pas de cellules vides.

La formule DECALER dont la syntaxe est DECALER(Ref;lignes;colonnes;[hauteur];[largeur]) renvoie l'adresse d'une plage de cellules qui se décale depuis l'argument Ref d'un nombre de lignes et de colonnes définit par les arguments éponymes. Les arguments hauteur et largeur qui sont facultatifs déterminent le nombre de lignes et de colonnes de la plage (d'où l'utilisation de la formule NBVAL qui calcule le nombre de cellules remplies en ligne 1 et colonne A

Pour renvoyer l'adresse de la plage que renvoie la formule DECALER, tu as la fonction ADRESSE combinée aux formule LIGNE, LIGNES, COLONNE et COLONNES. Je te renvoie vers l'aide en ligne si tu veux les comprendre.

Code :

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

=ADRESSE(LIGNE(maPlage);COLONNE(maPlage)) & ":"  & ADRESSE(LIGNE(maPlage)+LIGNES(maPlage)-1;COLONNE(maPlage)+COLONNES(maPlage)-1)

maPlage est le nom contenant la formule montrée plus haut et qui permet de rendre dynamique celle-ci.

[EDIT]
Bonjour Chris, je n'avais pas vu que tu avais déjà répondu pendant que je rédigeais ma réponse.

[Kevin Brandt]

En fait, je n'ai pas bien compris son message.
Que signifie "passe par une formule nommée" ?

Je ne suis pas expert en VBA....

Ce que je veux faire, tout simplement. J'ai un tableau avec des enregistrements que je veux pouvoir récupérer par des mots-clés.
Du genre: Num d'enreg - Mot-clé 1 - Mot-clé 2 - Mot-clé 3.... Sauf qu'il n'y a pas de priorité dans les mots-clés, un des mots clés peut être un 1 pour un enreg, ou en 2 pour un autre
Donc j'utilise un filtre avancé.
Sur plusieurs cellules de la feuille, on peut saisir des mots-clés pour rechercher ces enregistrements. Sauf que le nombre de mots-clés recherchés n'est pas toujours constant, d'où la fonction DECALER pour savoir jusqu'où on s'arrête.

Sans la macro, je lance un filtre avancé, et dans le Critère je rentre à la main la fonction avec Décaler. Ca marche sans problème.
pour éviter de retaper la fonction à chaque fois, je voulais faire une macro. Mais c'est au niveau du VBA que ça coince.

[Philippe Tulliez]

Bonjour,

Sur plusieurs cellules de la feuille, on peut saisir des mots-clés pour rechercher ces enregistrements. Sauf que le nombre de mots-clés recherchés n'est pas toujours constant, d'où la fonction DECALER pour savoir jusqu'où on s'arrête.

Comme expliqué plus haut, si tu utilises le VBA pour exporter des données avec la méthode AdvancedFilter utilise le code que je t'ai proposé.

Code :

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

Range("E1").CurrentRegion

renvoie la plage courante.
C'est l'équivalent de la combinaison des touches Ctrl+A our Ctrl+*
Imaginons que dans la zone des critères, les étiquettes de colonnes en E1:H1 et les critères en E2:E3, F2, G2:G4 et H2
Tu sélectionnes la cellule E1 et tu fais Ctrl+*, tu vas sélectionner les cellules E1:H4 et bien Range("E1").CurrentRegion

En fait, je n'ai pas bien compris son message.
Que signifie "passe par une formule nommée" ?
Je ne suis pas expert en VBA....

Il ne s'agit pas de VBA mais d'un outil d'excel.
Chris faisait allusion au gestionnaire des noms d'excel (Onglet [Formules], groupe Noms définis, commande Gestionnaire des noms

[Kevin Brandt]

C'est bon, c'est résolu.
Avec le code Range("E1").CurrentRegion, ça marche directement et c'est beaucoup plus simple
Merci beaucoup pour les conseils !