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

Question

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.


Configuration: Windows XP / Internet Explorer 8.0

lermite222 · Answer

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)

michel_m · Answer

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

lermite222 · Answer

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+

Pab · Answer

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 ?

lermite222 · Answer

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)

lermite222 · Answer

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 ?

Pab · Answer

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

ccm81 · Answer

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

lermite222 · Answer

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.

Pab · Answer

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.

Pab · Answer

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 ?

lermite222 · Answer

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

Excel VBA nom de liste variable avec formule

12 réponses

Discussions similaires