Discussion :

[Marc-L]

Je n'utilise aucune cellule d'une feuille de calculs dans le code mais juste le moteur interne d'Excel via Evaluate.

Pour éviter de perdre du temps entre la formule d'une version française et sa version native forcément en anglais
en interne, utilisation temporaire d'une cellule pour écrire la formule de concaténation
puis côté VBE afficher directement sa correspondance en anglais, base de la formule utilisée dans le code …
Donc en une ligne de code c'est torché ! (deux ou plus pour ceux aimant la lisibilité)

[pijaku]

Merci Marc.



J'y suis parvenu, sans concaténation, en poursuivant sur mon idée de filtrer en fonction de la première lettre.
La formule de calcul est alors relativement simple car il suffit de Index et Proper.

J'avoue toutefois être curieux de ce avec quoi tu compte concaténer pour pouvoir utiliser Filter...

Le résultat est parfait, en tout cas, de mon côté. J'ai développé un peu, donc j'obtiens 4 lignes de code mais que l'on peut écrire en une seule.
Je donnerais le résultat si quelqu'un d'autre le demande expressément.

Merci encore à toi.

[Marc-L]

Dans la demande enfin formulée il s'agit de retenir uniquement les lignes dont la colonne H est égale à "T".
Donc une simple formule de calcul Si H="T" alors L&"_"&B - ou encore via CONCATENER - et rien si faux puis
côté VBE regarder son équivalence interne en anglais donc via Formula : voilà la formule de base du code …

Si tu me suis, devenant mon élève à la place du demandeur, quelle formule obtiens-tu ?
Sur la base de l'exemple du post #17, quel est le résultat pour la ligne n°9 de la feuille BD
si la cellule est égale à T puis si elle est vide par exemple ?

[pijaku]

Oui, Marc.
Sauf que la demande initiale a évolué depuis...
https://www.developpez.net/forums/d1...x/#post9648002

exemple: mettre dans la combobox1 de la feuille3 que les villes commençant par la lettre "T" issues de la colonne "h" de la feuille2

Mais je vais tout de même me placer en élève et faire l'exercice , d'autant plus que, selon la remarque de Pierre ("le grand") fauconnier, certaines communes merdoient avec PROPER...
je reviens

[Marc-L]

Comme il s'agit d'une simple formule de calculs de niveau grand débutant côté Excel
on va rester juste sur le "T" car ce n'est vraiment pas compliqué pour l'adapter (GAUCHE)
et il n'a qu'à bien présenter son besoin et fournir un classeur cohérent,
répondant toujours au niveau de l'effort fourni par le demandeur !

Récemment sur un autre forum j'ai reçu un MP me demandant
pourquoi je ne lui avais pas répondu au même niveau d'une autre discussion du même jour.
A cela j'ai rétorqué « t'as vu ta présentation, compare donc avec l'autre ! » …

___________________________________________________________________________________________________________

L'effort fait les forts …

[pijaku]

Alors, pour en revenir à la demande d'origine...
La formule "de base" est :

Code :

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

=IF(H1="T",B1&"_"&L1,"")

Que l'on transforme grâce à :

1. Range.Address(External:=True)
2. Range.Offset(0, -6).Address(External:=True)
3. Range.Offset(0, 4).Address(External:=True)
4. doublant les guillemets qui doivent l'être...

Pour obtenir la formule suivante :

Code :

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

IF(" & .Address(External:=True) & "=""T""" & "," & .Offset(0, -6).Address(External:=True) & "&""_""&" & .Offset(0, 4).Address(External:=True) & ", """")

Qu'il "suffit" ensuite de :

1. évaluer (Evaluate),
2. Transposer (Application.Transpose),
3. Filtrer (Filter)

Ce qui nous donne la très jolie fonction suivante (à placer dans le module de la feuille ou se trouve la ComboBox) :

Code :

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

1
2
3
4
5
6
Private Function Vazy(Plage As Range)
    With Plage
        Vazy = Application.Transpose(Evaluate("IF(" & .Address(External:=True) & "=""T""" & "," & .Offset(0, -6).Address(External:=True) & "&""_""&" & .Offset(0, 4).Address(External:=True) & ", """")"))
    End With
    Vazy = Filter(Vazy, "_")
End Function

Remarque : on pourrait passer plus de paramètres à cette fonction, et, notamment, les deux valeurs d'Offset + le symbole permettant le filtre...

Que l'on appelle comme ceci :

Code :

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

1
2
3
4
Private Sub Worksheet_Activate()
    ComboBox1.List = Vazy(Sheets("Feuil2").Range("H1:H10"))
    'ComboBox1.ListIndex = -1 'purement esthétique
End Sub

[pijaku]

Comme j'aime à me citer :

Remarque : on pourrait passer plus de paramètres à cette fonction, et, notamment, les deux valeurs d'Offset + le symbole permettant le filtre...

Voici donc le résultat :
paramètres :

1. Plage As Range, la plage ou sont situées les données initiales,
2. Filtre As String, la lettre, ou la chaîne de caractère, que l'on doit trouver dans la plage Plage,
3. Symbole As String, le symbole permettant la concaténation et le filtrage (ne doit pas figurer dans les données),
4. CarDeConcat As String, le symbole par lequel remplacer Symbole après le filtre pour une restitution "invisible",
5. OffsetNegatif As Long, la colonne située à la droite de la Plage, source de la concaténation,
6. OffsetPositif As Long, la colonne située à la gauche de la Plage, source de la concaténation.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Private Sub Worksheet_Activate()
    ComboBox1.List = Etabli_Liste(Sheets("Feuil2").Range("H1:H" & Sheets("Feuil2").Range("H" & Rows.Count).End(xlUp).Row), "T", "--¤¤$_$¤¤--", " ", -6, 4)
End Sub
 
Private Function Etabli_Liste(Plage As Range, Filtre As String, Symbole As String, CarDeConcat As String, OffsetNegatif As Long, OffsetPositif As Long)
Dim Formule As String, Vazy
    With Plage
        Formule = "IF(" & .Address(External:=True) & "=""" & Filtre & """," & .Offset(0, OffsetNegatif).Address(External:=True) & "&""" & Symbole & """&" & .Offset(0, OffsetPositif).Address(External:=True) & ", """")"
        Vazy = Application.Transpose(Evaluate(Formule))
    End With
    Vazy = Filter(Vazy, Symbole)
    Etabli_Liste = Split(Replace(Join(Vazy, Chr(1)), Symbole, CarDeConcat), Chr(1))
End Function

Je crois avoir bien fait mes devoirs...

[Marc-L]

Pas mal !     Mais restons simple avec cette formule comme un débutant !

A partir de la formule française cela donne bien nativement pour la ligne n°9 :   =IF(H9="T",L9&"_"&B9)   …

• Si H est égale à T alors la concaténation est renvoyée sinon c'est la valeur FAUX.
   En VBA cette valeur False doit être éliminée de la matrice (*) renvoyée par la formule via la fonction Filter.

• Cette formule étant intégrée en VBA dans une chaîne de caractères, tout guillemet de la formule doit y être doublé.

• Le classeur pouvant comporter plusieurs lignes, dans l'exemple jusqu'à la ligne n°14,
   il faut adapter la formule en conséquence, du genre H9:H14 etc …
   Mais comme le nombre de lignes n'est pas figé, il faudra au préalable déterminer le n° de la dernière ligne
   et donc ce numéro dans la formule codée en VBA sera identifié par le caractère # par exemple,
   remplacé lors de l'évaluation de la formule par le numéro réel de la dernière ligne.

• Comme la fonction VBA Filter n'accepte qu'une matrice unidimensionnelle comme une ligne par exemple,
   les données étant en colonne donc en deux dimensions, il faut adapter la formule via TRANSPOSE.

De mon côté ayant ouvert l'exemple sur une version 2003 qui parfois convertit les CodeName des feuilles de calculs d'un .xlsx
- anglais vers français par exemple ou même de français à français avec une renumérotation des CodeName -
dans les codes suivants Feuil2 représente la feuille source BD et Feuil3 celle nommée T contenant la liste déroulante,
à vérifier et à adapter de votre côté si besoin …


Code alimentant la liste déroulante à placer dans le module de la feuille T :

Code :

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

1
2
3
4
Friend Sub Worksheet_Activate()
                 V = Application.Max(Feuil2.Cells(Rows.Count, 8).End(xlUp).Row, 10)
    ComboBox1.List = Filter(Feuil2.Evaluate(Replace("TRANSPOSE(IF(H9:H#=""T"",L9:L#&""_""&B9:B#))", "#", V)), False, False)
End Sub

V contient le numéro de la dernière ligne; je n'aime pas utiliser une variable pour une unique utilisation
mais à des fins de lisibilité du code, comme j'aurais pu aussi placer la formule dans une constante …


Code lors de l'ouverture du classeur donc à placer dans le module du classeur ThisWorkbook :

Code :

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

1
2
3
4
Private Sub Workbook_Open()
    Feuil3.ComboBox1.Value = ""
    If Me.ActiveSheet.Index = Feuil3.Index Then Feuil3.Worksheet_Activate
End Sub

(*) Comme ne l'indique pas l'aide VBA la puissante fonction Evaluate accepte le calcul matriciel !

___________________________________________________________________________________________________________

Pour apprendre quelque chose aux gens, il faut mélanger ce qu’ils connaissent avec ce qu’ils ignorent. (Pablo Picasso)

[esse2016]

bonjour

le post #20 par la remarque de Marc-L fonctionne. Donc la combobox1 de la feuille3 se vide à chaque réouverture d'excel. via combobox1.value ="", cette ligne est inscrite dans le worksheet_Activate().

déjà merci. je regarde la suite

[esse2016]

Bonjour,

Cette fonction répond à mon problème et elle est au plus proche de ce que je cherchais. J'ai pris cette méthode parcequ'elle fonctionne sur mon programme.

Code :

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

1
2
3
4
5
6
Private Function Vazy(Plage As Range)
    With Plage
        Vazy = Application.Transpose(Evaluate("IF(" & .Address(External:=True) & "=""T""" & "," & .Offset(0, -6).Address(External:=True) & "&""_""&" & .Offset(0, 4).Address(External:=True) & ", """")"))
    End With
    Vazy = Filter(Vazy, "_")
End Function

- par contre si dans la colonne de l'offset(0,4) il n'y a rien, alors la fonction m'ajoute dans la liste du combobox1 un item "vide"... mais bon je ferai attention de la présence d'un champ non vide.
- pas classé dans l'ordre. mais bon je m'en passerai. (je vais voir si cela n'est pas indiqué dans la méthode de Pierre avec le "sort" qqchose)

Sincères remerciements à Pierre, pijaku, Marc-L et les autres oubliés, ça répond à mon problème. Merci !