Excel VBA nom de liste variable avec formule

Résolu/Fermé

Pab - 3 janv. 2011 à 11:08
lermite222 Messages postés 8702 Date d'inscription dimanche 8 avril 2007 Statut Contributeur Dernière intervention 22 janvier 2020 - 4 janv. 2011 à 16:42

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.

A voir également:

Nommer une plage de cellule variable vba
Aller à la ligne dans une cellule excel - Guide
Frédéric cherche à faire le buzz sur les réseaux sociaux. il a ajouté une image d’ours polaire sur une image de plage. retrouvez l'image originale de la plage. que cache l'ours polaire ? - Forum Loisirs / Divertissements
Excel compter cellule couleur sans vba - Guide
Si cellule contient texte alors ✓ - Forum Excel
Verrouiller cellule excel - Guide

12 réponses

Réponse 1 / 12

lermite222 Messages postés 8702 Date d'inscription dimanche 8 avril 2007 Statut Contributeur Dernière intervention 22 janvier 2020 1 190
3 janv. 2011 à 19:28

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 ?

Réponse 2 / 12

ccm81 Messages postés 10851 Date d'inscription lundi 18 octobre 2010 Statut Membre Dernière intervention 16 avril 2024 2 404
4 janv. 2011 à 10:40

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

Pab
4 janv. 2011 à 11:09

Wahouuuuuu !!!

Merci beaucoup, ça marche nickel !

Super

Pab
4 janv. 2011 à 16:36

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

Réponse 3 / 12

lermite222 Messages postés 8702 Date d'inscription dimanche 8 avril 2007 Statut Contributeur Dernière intervention 22 janvier 2020 1 190
Modifié par lermite222 le 3/01/2011 à 11:59

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)

Pab
3 janv. 2011 à 16:23

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 ?

lermite222 Messages postés 8702 Date d'inscription dimanche 8 avril 2007 Statut Contributeur Dernière intervention 22 janvier 2020 1 190
3 janv. 2011 à 16:53

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

Pab
3 janv. 2011 à 17:21

Il y a apparament toujours une erreure dans la formule ...

lermite222 Messages postés 8702 Date d'inscription dimanche 8 avril 2007 Statut Contributeur Dernière intervention 22 janvier 2020 1 190
3 janv. 2011 à 17:33

J'avais mal compris la question, la solution de Michel_m est la bonne

Réponse 4 / 12

michel_m Messages postés 16603 Date d'inscription lundi 12 septembre 2005 Statut Contributeur Dernière intervention 16 décembre 2023 3 303
3 janv. 2011 à 11:57

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

Pab
3 janv. 2011 à 17:14

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.

Pab
4 janv. 2011 à 08:59

Nous avons bien avancé ..
Vous avez peut etre maintenant la solution ...

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question

Réponse 5 / 12

lermite222 Messages postés 8702 Date d'inscription dimanche 8 avril 2007 Statut Contributeur Dernière intervention 22 janvier 2020 1 190
3 janv. 2011 à 17:42

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+

michel_m Messages postés 16603 Date d'inscription lundi 12 septembre 2005 Statut Contributeur Dernière intervention 16 décembre 2023 3 303
3 janv. 2011 à 17:50

merci, Lermite. j'étais sur un autre truc

lermite222 Messages postés 8702 Date d'inscription dimanche 8 avril 2007 Statut Contributeur Dernière intervention 22 janvier 2020 1 190
3 janv. 2011 à 17:55

Bonjour Michel,
Beh comme d'hab... ont apprend tout les jours.
A+

Réponse 6 / 12

Pab
3 janv. 2011 à 18:11

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 ?

michel_m Messages postés 16603 Date d'inscription lundi 12 septembre 2005 Statut Contributeur Dernière intervention 16 décembre 2023 3 303
3 janv. 2011 à 18:30

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...

Pab
3 janv. 2011 à 19:03

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 ?

Réponse 7 / 12

lermite222 Messages postés 8702 Date d'inscription dimanche 8 avril 2007 Statut Contributeur Dernière intervention 22 janvier 2020 1 190
Modifié par lermite222 le 3/01/2011 à 18:28

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)

Pab
3 janv. 2011 à 18:33

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.

Réponse 8 / 12

Pab
3 janv. 2011 à 22:57

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 ..

Réponse 9 / 12

lermite222 Messages postés 8702 Date d'inscription dimanche 8 avril 2007 Statut Contributeur Dernière intervention 22 janvier 2020 1 190
4 janv. 2011 à 11:57

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.

Réponse 10 / 12

Pab
4 janv. 2011 à 13:35

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.

Réponse 11 / 12

Pab
4 janv. 2011 à 16:35

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 ?

Réponse 12 / 12

lermite222 Messages postés 8702 Date d'inscription dimanche 8 avril 2007 Statut Contributeur Dernière intervention 22 janvier 2020 1 190
4 janv. 2011 à 16:42

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 ??

Discussions similaires

Retrouver l'image originale après avoir fait des modifications ?

programme qui n'affiche pas le mot secret

VBA : Range avec références variables..

VBA Excel : sélection d'une plage variable

Excel VBA : Récupérer la valeur d'une cellule

Discussions similaires

Newsletters