XL 2016 Erreur sur liste déroulante dynamique

[bambi]

Bonjour à tous

Dans le but d'avancer sur mon problème d'extraction avec filtres élaborés, je bloque sur une erreur dans la création d'une liste déroulante dynamique.

Les éléments de ma liste sont dans un tableau.
J'ai une macro pour reporter les éléments dans une colonne puis une liste nommée avec la fonction DECALER de cette colonne et enfin une validation de liste sur une autre feuille pour la liste déroulante

Mais le report des éléments de ma liste sont systématiquement décalés et je ne parviens pas à résoudre le problème

J'espère que vous pourrez m'aider à trouver l'erreur
Merci d'avance

 

[JHA]

Bonjour à tous,

Un début de piste par formule

JHA

 

[job75]

Bonjour bambi, JHA, le forum,

Voyez le fichier joint et la macro dans la feuille "Filtres" :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$C$24" Then
    With Sheets("Carnet")
        .[A22:AL1500].AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.[BA1], Unique:=True
        With .Columns("BA")
            .Sort .Cells, xlAscending, Header:=xlYes 'tri
            Target.Validation.Delete
            With .Parent.Range(.Cells(2), .Cells(.Rows.Count).End(xlUp))
                If .Row > 1 Then
                    .Cells.Name = "M" 'plage nommée
                    Target.Validation.Add xlValidateList, Formula1:="=M" 'crée la liste de validation
                End If
            End With
        End With
    End With
End If
End Sub

A+

 

[bambi]

Bonjour et merci à tous les deux pour vos propositions
Je vais plus particulièrement me pencher sur celle de job75 car je ne pourrais pas reprendre les formules sur mon fichier final
En attendant des réponses, j'avais néanmoins avancé et compris que c'était les cellules vides qui posaient problème
J'ai donc revu la structure de mon fichier
Cela m'a permis de le remettre avec tous les filtres pour le faire correspondre au mieux à mon projet
Mais j'ai toujours des résultats étranges dans la création des listes, avec des vides inexpliqués

 

[bambi]

J'ai appliqué la macro de job75 à l'ensemble de mon fichier avec tous les filtres dont j'ai besoin
Mais au final, cela génère les mêmes erreurs que le mien
à savoir des cellules vides dans les listes créées

 

[job75]

Re,

Ma macro précédente ne peut pas être utilisée sur votre dernier fichier, très différent du premier.

Voyez alors ces 2 macros qui n'utilisent plus le filtre avancé mais des tableaux VBA :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(ActiveCell, [B24:J24]) Is Nothing Then Exit Sub
Dim col As Variant, t, d As Object, i&, n&, x$
With Sheets("Carnet")
    col = Application.Match(ActiveCell(0, 1), .Rows(22), 0)
    If IsError(col) Then GoTo 1
    If .FilterMode Then .ShowAllData 'si la feuille est filtrée
    With .Range("A23:A" & .Range("B" & .Rows.Count).End(xlUp).Row)
        If .Row < 23 Then GoTo 1
        t = .Columns(col).Resize(.Rows.Count + 1).Value2 'tableau VBA, plus rapide, au moins 2 éléments
    End With
End With
Set d = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(t) - 1
    x = Trim(t(i, 1))
    If x <> "" Then d(x) = ""
Next
n = d.Count
1 ActiveCell.Validation.Delete
With [AM2] 'cellule à adapter éventuellement
    .EntireColumn.NumberFormat = "dd/mm/yyyy" 'format date
    If n Then
        .Resize(n) = Application.Transpose(d.keys)  'avec Transpose maximum 65536 lignes
        .Resize(n).Sort .Cells, xlAscending, Header:=xlNo
        .Resize(n).Name = "Liste" 'plage nommée
        ActiveCell.Validation.Add xlValidateList, Formula1:="=Liste" 'liste de validation
    End If
    .Offset(n).Resize(Rows.Count - n - .Row + 1).ClearContents 'RAZ en dessous
End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [B24:J24]) Is Nothing Then Exit Sub
Dim t, j%, txt$, col, ub%, ncol%, i&, x$, n&
t = [B24:J24].Value2 'date au format nombre
For j = 1 To UBound(t, 2)
    txt = txt & Chr(1) & IIf(t(1, j) = "", "*", t(1, j)) 'utilise le caractère générique *
Next
col = Array(2, 4, 12, 13, 16, 29, 30, 31, 32) 'colonnes à filtrer, à adapter
ub = UBound(col)
With Sheets("Carnet")
    If .FilterMode Then .ShowAllData 'si la feuille est filtrée
    With .Range("A23:AK" & .Range("B" & .Rows.Count).End(xlUp).Row)
        If .Row < 23 Then GoTo 1
        t = .Value2 'tableau VBA, plus rapide
        ncol = UBound(t, 2)
    End With
End With
For i = 1 To UBound(t)
    x = ""
    For j = 0 To ub
        x = x & Chr(1) & Trim(t(i, col(j)))
    Next j
    If x Like txt Then
        n = n + 1
        For j = 1 To ncol
            t(n, j) = t(i, j)
        Next j
    End If
Next i
1 With [A29] 'cellule à adapter éventuellement
    If n Then .Resize(n, ncol) = t
    .Offset(n).Resize(Rows.Count - n - .Row + 1, ncol).ClearContents 'RAZ en dessous
End With
Columns.AutoFit 'ajustement largeur
With UsedRange: End With 'actualise la barre de défilement verticale
End Sub

Je n'ai pas cherché à trier chronologiquement les listes de validation des jours et des mois.

Nouveau fichier joint (je l'ai épuré pour qu'il pèse moins lourd).

A+

 

[bambi]

Merci beaucoup @job75 du temps que tu as consacré à mon fichier
Je vais regarder ça de près, l'adapter à mon fichier final et je te fais un retour complet rapidement

 

[job75]

Re,

Macro Worksheet_Change précédente, j'ai simplifié le calcul de la chaîne x.

Bonne nuit.

 

[bambi]

@job75

Transposition dans mon fichier final effectuée avec succès
J'ai supprimé un ou deux éléments de mise en forme de la Macro Worksheet_Change
Et j'en ai rajouté d'autres.
Le tout est au top et fonctionne parfaitement.
Un énorme merci pour le temps passé et l'efficacité.

Merci aussi à JHA pour sa proposition.
Bien que je ne l'ai pas retenue, j'ai conscience du travail

La macro finale avec mes légères modifications

Private Sub Worksheet_Change(ByVal Target As Range) 'by job75
If Intersect(Target, [B24:J24]) Is Nothing Then Exit Sub
Dim t, j%, txt$, col, ub%, ncol%, i&, x$, n&
t = [B24:J24]
For j = 1 To UBound(t, 2)
    If IsDate(t(1, j)) Then t(1, j) = CDbl(CDate(t(1, j)))
    txt = txt & Chr(1) & IIf(t(1, j) = "", "*", t(1, j)) 'utilise le caractère générique *
Next
col = Array(2, 4, 12, 13, 16, 29, 30, 31, 32) 'colonnes à filtrer, à adapter
ub = UBound(col)
With Sheets("CarnetDeTrades")
    If .FilterMode Then .ShowAllData 'si la feuille est filtrée
    With .Range("A23:AK" & .Range("A" & .Rows.Count).End(xlUp).Row)
        If .Row < 23 Then GoTo 1
        t = .Value2 'tableau VBA, plus rapide
        ncol = UBound(t, 2)
    End With
End With
For i = 1 To UBound(t)
    x = ""
    For j = 0 To ub
        x = x & Chr(1) & IIf(Trim(t(i, col(j))) = "", "*", Trim(t(i, col(j))))
    Next j
    If x Like txt Then
        n = n + 1
        For j = 1 To ncol
            t(n, j) = t(i, j)
        Next j
    End If
Next i
1 With [A29] 'cellule à adapter éventuellement
    If n Then .Resize(n, ncol) = t
    .Offset(n).Resize(Rows.Count - n - .Row + 1, ncol).Delete xlUp 'RAZ en dessous
End With

'mise en forme police et alignement
    Range("A29:AK1500").Font.Size = 8
    Range("A29:AK1500").Font.Name = "Calibri   "
    With Range("A29:AK1500")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
 
'mise en forme format colonnes
    Range("B29:B1500").Select
    Selection.NumberFormat = "m/d/yyyy"

    Range("F29:F1500,H29:H1500,O29:O1500,AJ29:AK1500").Select
    Selection.NumberFormat = "h:mm;@"

    Range("N29:N1500,W29:W1500,X29:X1500").Select
    Selection.NumberFormat = "0.00"

    Range("A24:A24").Select

End Sub

 

[job75]

Bonjour bambi, le forum,

Les mises en forme des résultats sont inutiles car elles peuvent être faites manuellement une fois pour toutes et jusqu'à la ligne 1048576.

Par ailleurs les listes pour "JOUR" et "MOIS" peuvent être triées chronologiquement, voyez ce fichier (2).

PS : dans votre dernier fichier vous n'avez pas pris la dernière édition de mes macros...

A+

 

[bambi]

bonjour @job75

Il me semblait bien pourtant avoir pris la dernière version

Le problème c'est que j'ai absolument besoin de conserver la même structure (largeur de colonne, format...)
J'ai en effet un autre tableau sur la même feuille qui doit absolument rester tel quel
Je ne l'ai pas posté initialement car ce n'était pas l'objet de ma demande et qu'il contient beaucoup de données et de formules
J'ai essayé de l'anonymiser pour que vous compreniez au mieux
Les colonnes en jaune doivent absolument garder leur format

Dans la version tab_bambi.xlsm j'ai repris vos macros initiales avec quelques mises en forme que j'ai ajoutées
Les filtres fonctionnent parfaitement et ma feuille filtres garde sa structure complète
Mais en effet, les jours et le mois ne sont pas dans l'ordre

Dans la version tab_job75.xlsm j'ai repris vos dernières macros mais j'ai des bugs et ma page ne reste plus dans son format.

 

[bambi]

Ce que je croyais résolu avec mon tableau Tab_bambi.xlsm ne l'est pas
Car j'ai des tableaux avec formules qui sont impactés par les macro_filtres proposés par @job75

Exemple :
Dans une formule comme =(NB.SI($L$29:$L1500;"S"))/$L$4
la valeur 1500 s'adapte au filtrage en devenant par exemple =(NB.SI($L$29:$L34;"S"))/$L$4
et y reste pour le prochain filtrage au lieu de reprendre en compte toute la colonne de la ligne 29 à 1500

En bref, dans mon fichier, tout ce qui est sur la feuille "Filtre" au-dessus de la ligne 23 ne doit pas du tout être impacté par les macros de filtrage
(je n'ai laissé qu'une seule formule dans celui que j'envoie car c'est compliqué à anonymiser
mais dans mon fichier final , il y a des formules dans toutes les cases mauves)

A part ce problème d'impact sur mes tableaux et mes formules au-dessus de la ligne 23 de la feuille Filtres , côté filtrage, cela fonctionne exactement comme je le souhaitais

Je ne sais pas si cela est possible à résoudre mais merci d'avance à ceux qui prendront le temps de regarder

 

[job75]

Bonjour Bambi,

Bah vous ne suivez pas, encore une fois vous ne prenez pas les dernières éditions de mes macros : elles n'utilisent plus .Delete mais .ClearContents !!!

Et si vous voulez utiliser les "noms propres" de mon fichier (2) pour JOUR et MOIS il faut bien sûr introduire la fonction NOMPROPRE dans les formules des colonnes AC et AD de la feuille "Carnet".

Fichier (3) avec les mêmes macros que pour le fichier (2), les listes en JOUR et MOIS sont triés chronologiquement.

Et bis repetita, pas besoin de mettre en forme la feuille "Filtres" par macro, j'ai formaté les colonnes jusqu'à la ligne 1048576.

A+

 

[bambi]

Bonjour job75
Pardon de cette incompréhension

Dans le dernier fichier que vous venez de poster
la formule semble bien rester constante
Merci pour ça
Je vérifierais si cela fonctionne avec toutes mes formules dès que possible

Mais pour la mise en forme de la feuille filtre après filtrage, j'ai bien un énorme décalage
Je vous poste une image avant / après

 

[bambi]

@job75
Je viens d'installer la macro dans mon fichier final, et les formules semblent bien rester fixes
C'est parfait

Je n'ai donc que ce petit souci de largeur de colonne qui s'adapte à chaque filtrage au lieu de rester fixe
C'est cela qui cré le décalage visible sur les images ci dessus