Discussion :

[Karow]

Bonjour à tous !

Ceci est mon premier post, j'espère pouvoir bénéficier de votre aide et trouver une solution à mon problème avec vous !

Dans mon entreprise, l'extraction de toute les références des produits vendu se fait sur un fichier excel basique, mais le nombre de références est bien trop volumineux.
Je cherche donc à faire une macro qui, en se basant sur un fichier "base de donnée", recherche dans le fichier extrait toute les lignes contenant ces références et ne montre qu'elles (ou masque toutes les autres au choix).

Pour cela, j'ai créé un fichier dans lequel j'ai indiqué chaque référence existante rangées par produits, puis j'ai nommé chaque plage de donnée pour avoir une plage de recherche contenant chacune des références pour chaque produit.
J'ai ensuite créé un onglet dans lequel je propose à l'utilisateur de choisir le modèle recherché, et affiche un bouton po
A partir de là, j'ai écrit le code que vous pouvez voir ici :

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
Sub Extraction()
'
' Recherche Macro
'
    Application.ScreenUpdating = False
 
    Dim fichier As Variant
    Dim recherche As Range
    Dim model As String
    Dim i As Integer
    Dim DerCol As Integer
 
    'La case C6 contient le modèle choisi par l'utilisateur
    model = Sheets("Extraction").Range("C6").Text
 
    'Va chercher la plage qui correspond au modèle choisi dans les onglets corespondant
    recherche = Range("M_" & model)
 
    'Ouvre le fichier extrait
    fichier = Application.GetOpenFilename("Tous les fichiers (*.*),*.*")
    Workbooks.Open (fichier)
 
    'Montre toute les lignes
    Call Tout_montrer
 
    'Recherche les lignes ne contenant aucune des données de la plage de recherche et les masque
    DerCol = Cells(1, Columns.Count).End(xlToLeft).Column
    For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
        If WorksheetFunction.CountIf(Range(Cells(i, 2), Cells(i, DerCol)), recherche) <= 0 Then
                Rows(i).EntireRow.Hidden = True
        End If
    Next
 
    Application.ScreenUpdating = True
 
End Sub
Sub Tout_cacher()
    Cells.EntireRow.Hidden = True
End Sub
 
 
Sub Tout_montrer()
    Cells.EntireRow.Hidden = False
End Sub

La macro est donc censé rechercher dans le fichier tout les lignes ne contenant aucune des données de la plage de recherche et les masquer.
Cependant au lancement de la macro, un code d'erreur s'affiche :

Erreur d'exécution '91'

Variable objet ou variable With non définie

Et me montre la ligne qui nomme la variable "recherche" (ligne 17) en la coloriant en jaune.

Je pense que c'est probablement pour deux raison :

- Je n'utilise pas les bonnes variables
- La fonction de recherche ne fonctionne pas avec une plage de donnée mais uniquement avec une seule valeur (variable en bleu)

Je ne sais pas du tout comment trouver une solution, je suis resté sur le code jusqu'à 2:30 ce matin sans succès... J'espère que vous en saurez plus que moi !

Merci d'avance !!

Ps : Je n'ai pas mis les fichiers en pièce jointe parce que le règlement du forum demande de ne pas le faire au premier post. Si vous en avez besoin je vous les mettrai a disposition.

---

---

[halaster08]

Bonjour,

Pour afficher le code avec les lignes il suffit de mettre les balise code (bouton # dans l'interface de réponse)

recherche étant de type range il faut écrire

Code :

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

set recherche = Range("M_" & model)

Cependant je pense que tu te complique la vie:

Je cherche donc à faire une macro qui, en se basant sur un fichier "base de donnée", recherche dans le fichier extrait toute les lignes contenant ces références et ne montre qu'elles (ou masque toutes les autres au choix).

Un filtre avancé devrait faire l'affaire: https://philippetulliez.developpez.c...dvancedfilter/

[Karow]

Merci pour ta réponse rapide halaster08 !

J'ai modifié le code comme tu me l'as dit, maintenant c'est la ligne 29 qui se colorie en jaune :

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
Sub Extraction()
'
' Recherche Macro
'
    Application.ScreenUpdating = False
 
    Dim fichier As Variant
    Dim recherche As Range
    Dim model As String
    Dim i As Integer
    Dim DerCol As Integer
 
    'La case C6 contient le modèle choisi par l'utilisateur
    model = Sheets("Extraction").Range("C6").Text
 
    'Va chercher la plage qui correspond au modèle choisi dans les onglets corespondant
    Set recherche = Range("M_" & model)
 
    'Ouvre le fichier extrait
    fichier = Application.GetOpenFilename("Tous les fichiers (*.*),*.*")
    Workbooks.Open (fichier)
 
    'Montre toute les lignes
    Call Tout_montrer
 
    'Recherche les lignes ne contenant aucune des données de la plage de recherche et les masque
    DerCol = Cells(1, Columns.Count).End(xlToLeft).Column
    For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
        If WorksheetFunction.CountIf(Range(Cells(i, 2), Cells(i, DerCol)), recherche) <= 0 Then
                Rows(i).EntireRow.Hidden = True
        End If
    Next
 
    Application.ScreenUpdating = True
 
End Sub
Sub Tout_cacher()
    Cells.EntireRow.Hidden = True
End Sub
 
 
Sub Tout_montrer()
    Cells.EntireRow.Hidden = False
End Sub

Et ce code d'erreur qui s'affiche :

Erreur d'exécution '13':

Incompatibilité de type

Je regarde de suite ton lien pour le filtre avancé. Mais c'est très important de le faire avec une macro.

[BENNASR]

Bonjour
dans l'hypothèse que tes données sont colonne A de la feuille 1 et la liste à ne pas tenir en compte pour filtrer en colonne A de la feuil2 tu peux faire avec :
IMPORTANT : Un code fourni pas un ami de site et je m'excuse d'avoir oublier le nom (si je souviens c'est Monsieur philippe tulliez ), j'ai apporté quelque modifications non significatives et à toi d'adapter à tes besoins

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
Sub FiltreInverseListe()
  Set f1 = Sheets("feuil1")
   Set f3 = Sheets("feuil2")
  Set d = CreateObject("scripting.dictionary")
  d.CompareMode = vbTextCompare
 
  Dim liste As New Collection
   Dim i As Integer
   On Error Resume Next
   For Each cel In f3.Range("A2:A" & [A65000].End(xlUp).Row)
    If Cells(cel.Row, 2) = "" Then
    liste.Add cel.Value, CStr(cel.Value)
    End If
   Next cel
 
  For Each c In liste: d(c) = "": Next c
  Set f2 = Sheets("feuil1")
  Set d2 = CreateObject("scripting.dictionary")     ' liste complémentaire
  d2.CompareMode = vbTextCompare
  For Each c In f2.Range("A2:A" & f2.[A65000].End(xlUp).Row)
   If Not d.exists(c.Value) Then d2(c.Value) = ""
  Next c
  ActiveSheet.Range("$A$1:$A$10").AutoFilter Field:=1, Criteria1:=d2.keys, Operator:=xlFilterValues
End Sub

[halaster08]

recherche est de type range, countif attend pour son deuxième paramètre une valeur, ça ne peux pas marcher, à condition que recherche ne soit composé que d'une cellule, tu peux utiliser recherche.value pour en récupérer le contenu

Code :

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

If WorksheetFunction.CountIf(Range(Cells(i, 2), Cells(i, DerCol)), recherche.value) <= 0 Then

Mais c'est trés important de le faire avec une macro.

Les filtres avancées s'utilisent aussi via macro

[Karow]

Bonjour
dans l'hypothèse que tes données sont colonne A de la feuille 1 et la liste à ne pas tenir en compte pour filtrer en colonne A de la feuil2 tu peux faire avec :
IMPORTANT : Un code fourni pas un ami de site et je m'excuse d'avoir oublier le nom (si je souviens c'est Monsieur philippe tulliez ), j'ai apporté quelque modifications non significatives et à toi d'adapter à tes besoins

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
Sub Extraction()
'
' Recherche Macro
'
    Application.ScreenUpdating = False
 
    Dim fichier As Variant
    Dim model As String
    Dim DerCol As Integer
    Dim f1 As Variant
    Dim f3 As Variant
    Dim d As Object
    Dim d2 As Object
 
    model = Sheets("Feuil1").Range("A16").Text
 
    fichier = Application.GetOpenFilename("Tous les fichiers (*.*),*.*")
    Workbooks.Open (fichier)
 
    Set f1 = ActiveSheet
    Set f3 = Workbooks("Extraction.xlsm")
    Set d = CreateObject("scripting.dictionary")
    d.CompareMode = vbTextCompare
 
    Dim liste As New Collection
    Dim i As Integer
    On Error Resume Next
    For Each cel In f3.Range("M_" & model)
    If Cells(cel.Row, 2) = "" Then
    liste.Add cel.Value, CStr(cel.Value)
    End If
    Next cel
 
    For Each c In liste: d(c) = "": Next c
    Set f2 = fichier.Sheets(1)
    Set d2 = CreateObject("scripting.dictionary")     ' liste complémentaire
    d2.CompareMode = vbTextCompare
    For Each c In f2.Range("A2:G").End(xlUp).Row
    If Not d.exists(c.Value) Then d2(c.Value) = ""
    Next c
    ActiveSheet.Range("$A$1:$A$10").AutoFilter Field:=1, Criteria1:=d2.keys, Operator:=xlFilterValues
 
    Application.ScreenUpdating = True
 
End Sub

Query1.xlsm Extraction.xlsm

Le fichier Extraction étant f3 (là ou se trouve les références à trouver) que l'utilisateur ouvre au lancement de la macro et le fichier Query1 étant f1 (là ou se trouve les données à trier).

Aucun trie ne se fait... =(
Je pense que c'est parce que le code que tu m'a donné masque les ligne contenant les données de ma plage de recherche alors que mon but et de les afficher et d'effacer toute les autres !


PS : Dernière modification de mon post et des fichiers en pièce jointe à 14:05 ! (Désolé pour les inconvénient sir vous étiez déja dessus...)

[BENNASR]

Code :

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

ActiveSheet.Range("$A$1:$A$10")

ça filtre que de A1 à A10 non ??
en tout cas dans quelle onglet et quelle colonne se trouve les éléments à ne pas prendre en considération pour filtrer??

[Karow]

Code :

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

ActiveSheet.Range("$A$1:$A$10")

ça filtre que de A1 à A10 non ??
en tout cas dans quelle onglet et quelle colonne se trouve les éléments à ne pas prendre en considération pour filtrer??

Oui c'est vrai je vais changer la range !

Pour ce qui est de la plage à ne pas prendre en considération, elles se trouve dans les 4 onglets du fichier "SAS";"UCF";"UF;"Extracteur".

J'ai nommé les plage selon les modèles.
C'est la variable "model" du code qui récupère le modèle sélectionné par l'utilisateur.
J'atribue ensuite à la recherche : f3.Range("M_" & model) pour qu'il sélectionne la bonne plage selon le modèle choisi.

[Karow]

Malheureusement toujours aucun trie...

Il n'efface aucune ligne donc quelque chose ne va pas.

Je précise à nouveau que je cherche à montrer les lignes contenant au moins une des données de la plage de recherche.

Par exemple, si l'utilisateur sélectionne le produit TD100 dans le fichier Extraction, puis lance la macro, alors celle-ci définie la plage "M_TD100" dans l'onglet "SAS" comme la plage de données recherchées, puis va trier le fichier Query1 que l'utilisateur sélectionne en affichant toute les lignes qui comprennent une des données présente dans la plage.

J'espère être assez claire ! Merci pour votre aide !

[BENNASR]

attention ami on met un code dans un module standard et non pas dans la feuille elle même
voila ton dossier regroupé
à tester

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
Sub FiltreInverseListe()
  Set f1 = Sheets("Query1")
  Set d = CreateObject("scripting.dictionary")
  d.CompareMode = vbTextCompare
 
  Dim liste As New Collection
   Dim i As Integer
   On Error Resume Next
 
   For j = 1 To Worksheets.Count
        If Worksheets(j).Name <> "Query1" Then
             For Each Cel In Worksheets(j).Range("B2:B" & [B65000].End(xlUp).Row)
                If Cel.Value <> "" Then
                liste.Add Cel.Value, CStr(Cel.Value)
                End If
            Next Cel
        End If
  Next j
 
  For Each c In liste: d(c) = "": Next c
  Set f2 = Sheets("Query1")
  Set d2 = CreateObject("scripting.dictionary")     ' liste complémentaire
  d2.CompareMode = vbTextCompare
  For Each c In f2.Range("D2:D" & f2.[A65000].End(xlUp).Row)
   If Not d.exists(c.Value) Then d2(c.Value) = ""
  Next c
  f2.Range("D2:D" & f2.[D65000].End(xlUp).Row).AutoFilter Field:=4, Criteria1:=d2.keys, Operator:=xlFilterValues
End Sub

[halaster08]

Juste pour te convaincre de l'utilité du filtre avancé:

A gauche avant le filtre, a droite l'image du résultat après le filtre

A noté que le filtre avancé se code sans boucle et en 3 lignes

[Karow]

Merci beaucoup halaster08 !

Désolé pour ma réponse tardive, je suis partit sur d'autres projets.
J'ai suis finalement parti sur un filtre avancé comme tu me l'as conseillé !

Merci encore !