Excel VBA nom de liste variable avec formule [Résolu/Fermé]

Signaler
-
Messages postés
8700
Date d'inscription
dimanche 8 avril 2007
Statut
Contributeur
Dernière intervention
22 janvier 2020
-
Bonjour,

Je suis débutant en VBA, j'ai passé pas mal de temps sur les forums, mais je n'ai jamais trouvé ma réponse.

Mon problème est le suivant.
J'ai une liste dans une colonne et je veux lui affecter un nom. Cette liste est variable (elle peut être plus ou moins longue). Pour le moment tout va bien, mon code marche.
Mais le problème est que cette liste est en faite une formule =SI qui prend une valeur si une autre case est remplie. Et ma formule me nomme ma liste même pour les cases vides, puisqu'elles contiennent une formule. Je voudrais donc que ma formule s'applique aux cases qui contiennent du texte et pas celle qui ne contiennent que la formule.


Ma formule est la suivante :

Sheets("Feuil3").Select
ActiveSheet.Names.Add Name:="Département", RefersTo:=Range(Range("B4"), Range("B4").End(xlDown).Address)



Merci d'avance.


12 réponses

Messages postés
8700
Date d'inscription
dimanche 8 avril 2007
Statut
Contributeur
Dernière intervention
22 janvier 2020
1 098
1°) Pourquoi employer différentes variables..
Sub Mamacro()
Dim DerLig As Long, Lig As Long
    
    DerLig = Range("C4").End(xlDown).Row
    Lig = Application.CountIf(Range("C4:C" & DerLig), ">""")
    ActiveSheet.Names.Add Name:="Mois", RefersTo:=Range("C4:C" & Lig + 3)

    DerLig = Range("D4").End(xlDown).Row
    Lig = Application.CountIf(Range("D4:D" & DerLig), ">""")
    ActiveSheet.Names.Add Name:="Jour", RefersTo:=Range("D4:D" & Lig + 3)

    DerLig = Range("E4").End(xlDown).Row
    Lig = Application.CountIf(Range("E4:E" & DerLig), ">""")
    ActiveSheet.Names.Add Name:="Note", RefersTo:=Range("E4:E" & Lig + 3)
End Sub

Et ça fonctionne pour les 2 autres ?
1
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 57389 internautes nous ont dit merci ce mois-ci

Messages postés
9523
Date d'inscription
lundi 18 octobre 2010
Statut
Membre
Dernière intervention
24 juillet 2020
1 872
bonjour,

pour la colonne contenant des formules, en reprenant les codes de lermite222 et de michel_m, j'ai tenté ça

DerLig = Range("E4").End(xlDown).Row
Lig = Application.CountIf(Range("E4:E" & DerLig), "*")
ActiveSheet.Names.Add Name:="Note", RefersTo:=Range("E4:E" & DerLig - Lig)

bonne suite
1
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 57389 internautes nous ont dit merci ce mois-ci

Wahouuuuuu !!!

Merci beaucoup, ça marche nickel !

Super
Tu pourrais adapter cette formule pour nommer non plus une colonne mais maintenant tout un tableau ? Du genre pour nommer la plage B4:E20 (en sachant qu'il y a toujours des formules en colonne E).

Merci
Messages postés
8700
Date d'inscription
dimanche 8 avril 2007
Statut
Contributeur
Dernière intervention
22 janvier 2020
1 098
Bonjour,
C'est pas très clair !!
Peut-être.. supposons tes textes en colonne D
Dim Lig As Long 
    Sheets("Feuil3").Select 
    Lig = Range("D4").End(xlDown).Row 
    ActiveSheet.Names.Add Name:="Département", RefersTo:=Range("B4:B & Lig")

A+
L'expérience instruit plus sûrement que le conseil. (André Gide)
Si tu te cognes à un pot et que ça sonne creux, c'est pas forcément le pot qui est vide. ;-)(Confucius)
Je pense que ta formule peut être la solution. Mais elle n'a pas l'air de marcher chez moi. Voici ce que j'ai tapé :

Sub MaMacro()

Dim Lig As Long
Sheets("Feuil4").Select
Lig = Range("C1").End(xlDown).Row
ActiveSheet.Names.Add Name:="Feuil4!Mois", RefersTo:=Range("C1:C & Lig")

Dim Lig As Long
Sheets("Feuil4").Select
Lig = Range("D1").End(xlDown).Row
ActiveSheet.Names.Add Name:="Feuil4!Jour", RefersTo:=Range("D1:D & Lig")

Dim Lig As Long
Sheets("Feuil4").Select
Lig = Range("E1").End(xlDown).Row
ActiveSheet.Names.Add Name:="Feuil4!Note", RefersTo:=Range("E1:E & Lig")


End Sub





Ou est l'erreure ?
Messages postés
8700
Date d'inscription
dimanche 8 avril 2007
Statut
Contributeur
Dernière intervention
22 janvier 2020
1 098
Je me suis tromper..mauvaise position du dernier guillement
ActiveSheet.Names.Add Name:="Feuil4!Mois", RefersTo:=Range("C1:C " & Lig)
Mais de toute façons, c'est pas ça...
Tu doit prendre la limite de la colonne où sont les texte et pas où sont les formules.
Tu n'a pas remarquer que Lig n'est pas pris sur la même colonne que le nom.
Revoir mon poste précédant.
Mais tu pourrais aussi essayer l'exemple de Michel_m
Il y a apparament toujours une erreure dans la formule ...
Messages postés
8700
Date d'inscription
dimanche 8 avril 2007
Statut
Contributeur
Dernière intervention
22 janvier 2020
1 098
J'avais mal compris la question, la solution de Michel_m est la bonne
Messages postés
16173
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
14 juillet 2020
2 995
si tes formules renvoient "" ou un texte, le nombre de cellules "valables" peut être donné par cette instruction
nbre = Application.CountIf(Range("B4:B" & derlig), ">""")

derlig étant donné par ton instruction
Range("B4").End(xlDown).Address

donc tu additionnes nbre-1 a 4 pour avoir ta cellule de fin
Merci pour cette réponse mais je ne comprends pas tout. Peux tu écrire la totalité du code ?
Je comprend les formules mais ne percoit pas leur articulation entres elles. Merci.
Nous avons bien avancé ..
Vous avez peut etre maintenant la solution ...
Messages postés
8700
Date d'inscription
dimanche 8 avril 2007
Statut
Contributeur
Dernière intervention
22 janvier 2020
1 098
Avec les info de Michel_m..
Sub Test()
Dim DerLig As Long, Lig As Long
    DerLig = Range("B4").End(xlDown).Row
    Lig = Application.CountIf(Range("B4:B" & DerLig), ">""")
    ActiveSheet.Names.Add Name:="Département", RefersTo:=Range("B4:B" & Lig + 3)
End Sub

A+
Messages postés
16173
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
14 juillet 2020
2 995
merci, Lermite. j'étais sur un autre truc
Messages postés
8700
Date d'inscription
dimanche 8 avril 2007
Statut
Contributeur
Dernière intervention
22 janvier 2020
1 098
Bonjour Michel,
Beh comme d'hab... ont apprend tout les jours.
A+
On s'approche !!

Voila la formule actuelle :


Sub Mamacro()

Dim DerLig As Long, Lig As Long
DerLig = Range("C4").End(xlDown).Row
Lig = Application.CountIf(Range("C4:C" & DerLig), ">""")
ActiveSheet.Names.Add Name:="Mois", RefersTo:=Range("C4:C" & Lig + 3)
Dim DerLign As Long, Lign As Long
DerLign = Range("D4").End(xlDown).Row
Lign = Application.CountIf(Range("D4:D" & DerLign), ">""")
ActiveSheet.Names.Add Name:="Jour", RefersTo:=Range("D4:D" & Lign + 3)
Dim DerLigne As Long, Ligne As Long
DerLigne = Range("E4").End(xlDown).Row
Ligne = Application.CountIf(Range("E4:E" & DerLigne), ">""")
ActiveSheet.Names.Add Name:="Note", RefersTo:=Range("E4:E" & Ligne + 3)

End Sub


Nouveau problème, comme dans ma cellule E4 (et les suivantes E5, E6 ...) il y a une formule du type =SI(OU(H4="";I4="");"";H4+I4), je pense qu'il y a un problème par rapport à ca. Quand je lance ma macro, ma liste "Note" s'arrete à la premiere case (E4) alors qu'il y en a d'autres après ...

Une idée du pourquoi comment ?
Messages postés
16173
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
14 juillet 2020
2 995
bin oui
DerLigne = Range("E4").End(xlDown).Row
indique la première ligne vide rencontrée

D'ailleurs, si tu as des cellules vides suivies de cellules pleines, tu as un sacré pb car la plage nommée est établie suivant le nombre de cellules pleines contigues...
Je n'ai pas de cellule vide intercalés !
Pourtant, lorsque je vais dans gestion des noms après avoir lancé la macro, le nom "Note" correspond à la plage E3:E4 ! Je ne comprends pas pourquoi.
Mes cellules de la colonne E (a partir de E4) contiennent toute la formule =SI(OU(H4="";I4="");"";H4+I4) et les cellules H4 et I4 (et les suivantes) sont toute remplis jusqu'à la ligne 20. J'ai donc des valeurs en colonne E jusqu'à la ligne 20. Et ensuite c'est le "" de la formule qui s'applique.
Pourquoi cela ne marche donc t-il pas ?
Messages postés
8700
Date d'inscription
dimanche 8 avril 2007
Statut
Contributeur
Dernière intervention
22 janvier 2020
1 098
Si tu a des cellules vide intercallées ça va pas marcher et il n'y aura pas de solution.
L'expérience instruit plus sûrement que le conseil. (André Gide)
Si tu te cognes à un pot et que ça sonne creux, c'est pas forcément le pot qui est vide. ;-)(Confucius)
Aucune cellule vide. Ma plage est remplie de la cellule E4 a la E20. Et ensuite la formule renvoie "", c'est donc vide. Mais je veux que la plage soit nommée jusqu'à la cellule E20, mais ça ne marche pas.
Ca ne marche toujours pas meme avec ta formule lermite222.

Je ne comprends vraiment pas. Oui ça marche pour les 2 autres car ce ne sont pas des formules mais uniquement du texte.
Seule la troisieme (colonne E) est une formule, et la formule donne un nom, mais uniquement à la plage E3:E4. Je ne comprends vraiment pas. Pourtant il n'y a rien en E3. Tout commence à partir de E4 jusqu'à E20 sans interruption.

En tout cas merci de prendre ce temps pour moi, j'espere qu'on va y arriver ..
Messages postés
8700
Date d'inscription
dimanche 8 avril 2007
Statut
Contributeur
Dernière intervention
22 janvier 2020
1 098
Grrr.. suivant tes dernières explicaions...
Colonne C et D, c'est pas des formules mais du texte ?
Colonne E, c'est des formules mais il n'y a aucune cellule qui contient quelque chose ? si oui..
Pour la colonne E
DerLig = Range("E4").End(xlDown).Row
Lig = Application.CountIf(Range("E4:E" & DerLig), ">""")
ActiveSheet.Names.Add Name:="Note", RefersTo:=Range("E4:E" & Lig + IIF(Lig = 0, 4 , 3))

si c'est bien comme ça, les 2 autres formules devraient êtres modifiées.
Mais non pas du tout. Je te réexplique encore une fois si tu veux, mais la solution est trouvée de toute façon (up).

En colonne C et D j'avais du texte.
Et en colonne E j'avais la formule =SI(OU(H4="";I4="");"";H4+I4).
Et donc vu que les colonnes H et I sont remplis jusqu'à la ligne 20, ma colonne E était aussi remplie jusqu'à la ligne 20. Et ensuite c'était remplie par "" (voir ma formule, c'est la réponse si la condition est vraie).
Donc voila. Mais ccm81 a trouvé la solution. Sa formule marche !

Merci bien.
Nouvelle dernière question ... (j'en ai jamais marre).

Si je veux maintenant adapter cette formule pour donner un nom à tout mon tableau (qui est variable et qui contient des formules).

Est-ce possible ?
Messages postés
8700
Date d'inscription
dimanche 8 avril 2007
Statut
Contributeur
Dernière intervention
22 janvier 2020
1 098
Dim D as long, DerLig1 as long, Derlig2 as long
DerLig1 = Range("C65536").End(xlup).Row 
DerLig2 = Range("D65536").End(xlup).Row 
If Derlig1>=Derlig2 then D = Derlig1 else D = Derlig2
ActiveSheet.Names.Add Name:="Totale", RefersTo:=Range("C4:E" & D)


Si toi t'en a marre, qu'en est-il de nous ??