Contourner la fonction filtre sur Excel 2016

Le 11/07/2024 à 15:14

Bonjour,

Je voudrais utiliser un équivalent de la fonction Filtre pour excell 2016.

Je vous joins le fichier dans lequel je voudrais filtrer les lignes du tableau AA5 - AR36, en insérant du texte en B2.

Merci pour votre aide,

Raphael.

33test-cntr.xlsx (23.81 Ko)
Le 11/07/2024 à 15:24

Hello,

Peut être un peu plus de précision sur ce que vous attendez ?

Peut être préciser la situation initiale versus la situation souhaitée ?

@+

Le 11/07/2024 à 15:40

Bonjour Baroute78,

En fait, je voudrais utiliser une zone de texte (B2 dans mon fichier) pour y insérer une valeur présente dans mon tableau et récupérer en dessous l'ensemble des données correspondantes à mon tableau.

Exemple dans mon fichier :

Si je tape auto 5, je voudrais voir apparaître à partir de C2, la ligne 9 du tableau AA-AQ, idem si je tape fournis 5.

Et si je tape auto 2, je voudrais voir apparaître la ligne 6, 24 jusque 33, Etc... ce qui était normalement possible avec la formule qui se trouve en G2.

Merci,

Raph.

Le 11/07/2024 à 16:24

Hello,

Essaie ça en C5,

Tu auras peut être besoin de faire CTRL+MAJ+ENTREE pour valider

=SIERREUR(DECALER($AA$1:$AQ$1;PETITE.VALEUR(SIERREUR(LIGNE($AA$5:$AA$36)*(TROUVE($B$2; $AA$5:$AA$36)/TROUVE($B$2; $AA$5:$AA$36));"");LIGNE()-4)-1;;;);"")

@+

@Tulipe_4, si jamais tu passes par ici merci pour le tips de la fonction DECALER avec le second argument

Le 12/07/2024 à 08:11

Bonjour Baroute78,

J'ai testé mais il semble que ça ne fonctionne pas (cfr fichier joint), rien ne s'affiche.

Merci,

Raphael.

25test-cntr.xlsm (25.07 Ko)
Le 12/07/2024 à 09:57

Hey,

Et si tu valides juste avec entrée ?

@+

Le 12/07/2024 à 10:59

Hello,

ça ne fonctionne pas non plus...

Le 12/07/2024 à 22:51

Bonsoir à tous,

Un essai :

Sub test()
    Dim x
    Const Prefix As String = "Auto 2"
    With Sheets("Automation")
        x = Filter(.Evaluate("transpose(if(left(aa1:aa100," & Len(Prefix) & _
                             ")=""" & Prefix & """,row(1:100)))"), False, 0)
        If UBound(x) > 0 Then
            x = Application.Index(.Range("aa1:aq100").Value, Application.Transpose(x), [transpose(row(1:17))])
            .[C5].Resize(UBound(x, 1), 17) = x
        End If
        If UBound(x) = 0 Then
            x = Application.Index(.Range("aa1:aq100").Value, Application.Transpose(x), [transpose(row(1:17))])
            .[C5].Resize(, UBound(x, 1)) = x
        End If
    End With
End Sub

klin89

Le 14/07/2024 à 17:18

Re Ralph1976,

Le code revu et corrigé :

Option Explicit
Sub test()
    Dim x, nDimension As Byte, Prefix As String
    'Const Prefix As String = "Auto 77"
    With Sheets("Automation")
        Prefix = .[b2]
        x = Filter(.Evaluate("transpose(if(left(aa1:aa100," & Len(Prefix) & _
                             ")=""" & Prefix & """,row(1:100)))"), False, 0)
        If UBound(x) = -1 Then Exit Sub
        x = Application.Index(.Range("aa1:aq100").Value, Application.Transpose(x), [transpose(row(1:17))])
        nDimension = getDimension(x)
        If nDimension = 1 Then
            .[C5].Resize(, UBound(x, 1)) = x
        Else
            .[C5].Resize(UBound(x, 1), UBound(x, 2)) = x
        End If
    End With
End Sub

Function getDimension(var As Variant) As Byte
    On Error GoTo Err
    Dim i As Long
    Dim tmp As Long
    i = 0
    Do While True
        i = i + 1
        tmp = UBound(var, i)
    Loop
Err:
    getDimension = i - 1
End Function

klin89

Le 14/07/2024 à 17:34

Hello,

Du coup voici la correction, la partie matricielle ne suit pas j'ai l'impression. A mettre en C5 et tirer vers la droite et vers le bas 

=SIERREUR(DECALER($AA$1;PETITE.VALEUR(SIERREUR(LIGNE($AA$5:$AA$36)*(TROUVE($B$2; $AA$5:$AA$36)/TROUVE($B$2; $AA$5:$AA$36));"");LIGNE()-4)-1;COLONNE()-3;;);"")

@+

Le 14/07/2024 à 19:04

bonjour Baroute78, Klin89,Ralph1976,

Klin89, une petite correction, sans cette fonction.

Souvent ce type de solution (si on l'utilise souvent et les plages plus grandes) prend plus de temps que par exemple un dictionaire

Sub test()
     Dim x, Prefix As String, Arr
     'Const Prefix As String = "Auto 77"
     With Sheets("Automation")
          Prefix = .[b2]
          x = Filter(.Evaluate("transpose(if(left(aa1:aa100," & Len(Prefix) & _
                               ")=""" & Prefix & """,row(1:100)))"), False, 0)
          If UBound(x) = -1 Then Exit Sub
          Arr = Application.Index(.Range("aa1:aq100").Value, Application.Transpose(x), [transpose(row(1:17))])
          If UBound(x) = 0 Then
               .[C5].Resize(, UBound(Arr)) = Arr
          Else
               .[C5].Resize(UBound(Arr), UBound(Arr, 2)) = Arr
          End If
     End With
End Sub
Le 15/07/2024 à 08:28

Bonjour Baroute78, Klin89, BsAlv,

La formule de Baroute fonctionne partiellement. Ca à l'air de marcher pour la première ligne du tableau mais si je cherche le code de la deuxième ligne, ça ne marche plus (et j'ai bien tiré la formule ;) ).

Le code VBA fonctionne dans le fichier test. Par contre, j'aurai du préciser dans le texte que les codes de mon tableau ne commence pas par la même syntaxe (Cfr fichier joint). Du coup, j'ai l'impression que le code vba devrait être adapté en conséquence mais là, je vous sollicite de nouveau.

Merci à tous en tout cas, c'est super sympa de m'aider.

17test-cntr.xlsm (27.75 Ko)
Le 15/07/2024 à 09:06

re,

comme ceci (mais vos données sont un tableau structuré pour le moment) ?

Private Sub TextBox1_Change()

     Dim x, y, Prefix As String, Arr, c
     With Sheets("Automation")
          Prefix = .Range("B2").Value
          Set c = Range("Tabel1")
          x = Filter(Evaluate("transpose(if(left(offset(tabel1,,,,1)," & Len(Prefix) & ")=" & Chr(34) & Prefix & Chr(34) & ",row(tabel1)-" & c.Row - 1 & "))"), False, 0)     'les lignes à filtrer
          y = Evaluate(Replace("column(offset(tabel1,,,1,))-#", "#", c.Column - 1))     'toutes les colonnes

          With .Range("C5")
               .Resize(100, UBound(y)).ClearContents     'vider la plage
               If UBound(x) = -1 Then Exit Sub     'rien à filtrer
               Arr = Application.Index(Range("tabel1").Value2, Application.Transpose(x), y)     'le résultat du filtrage
               If UBound(x) = 0 Then         'si le résultat n'est qu'une ligne, il faut transponer cette ligne
                    .Resize(, UBound(Arr)) = Arr
               Else
                    .Resize(UBound(Arr), UBound(Arr, 2)) = Arr
               End If
          End With
     End With
End Sub
17test-cntr-1.xlsm (31.26 Ko)
Le 15/07/2024 à 09:25

Hello,

Hum, ça fonctionne de mon côté avec ton fichier... On a bien les code et les fournis associés, le reste à vide car pas de données renseignées...

Regarde ce fichier et dis moi si c'est bien la même structure

@+

27test-cntr-3.xlsx (29.78 Ko)
Le 15/07/2024 à 10:00

Baroute 78, effectivement dans ton fichier ça fonctionne très bien. Par contre, dans le mien, ça ne veut pas...

J'ai simplement copié la formule en C5 et tirer horizontalement et verticalement. Faut-il faire autre chose ? La cellule liée du bouton est bien B2.

Désolé et encore merci.

Le 15/07/2024 à 10:04

re,

une solution plus simple en formules ...

29test-cntr-3.xlsx (31.67 Ko)
Le 15/07/2024 à 10:36

Hello,

Je ne comprends pas … si tu as les deux mêmes fichiers avec même structure de ligne et colonne ça devrait être bon. Sans le fichier sur lequel tu travailles impossible de te dire pourquoi …

@+

Le 15/07/2024 à 11:26

bonjour

un petit grain de sel

=SIERREUR(INDEX(AA$5:AA$37;SOMMEPROD(PETITE.VALEUR((ESTNUM(CHERCHE(B$2;AA$5:AA$37))*LIGNE($5:$37));(LIGNES($5:$37)-NB(CHERCHE(B$2;AA$5:AA$37))+LIGNE(A1))))-4);"")

cordialement

Le 15/07/2024 à 13:05

Merci à toutes et tous pour votre aide précieuse. Les différentes formules proposées fonctionnent correctement.

Raph1976.

Rechercher des sujets similaires à "contourner fonction filtre 2016"