Comment effacer tous les noms (Names) d'une feuille Excel, mais pas tous ceux du classeur.
A) pour effacer les noms se référant à une plage de la feuille active définis par
NomFeuilleActive!NomPlage
Sub efface1()
Dim N As Name
With ActiveSheet
For Each N In .Names
N.Delete
Next
End With
End Sub
B) pour effacer les noms se référant à une plage de la feuille active définis par
par NomPlage et NomFeuilleActive!NomPlage
Sub Efface2()
Dim nom As String
nom = ActiveSheet.Name
nc = Len(nom)
For Each N In ActiveWorkbook.Names
v = N.RefersTo
If Mid(v, 2, nc + 1) = nom & "!" Then N.Delete
Next N
End Sub
C) si tu désires supprimer seulement les noms contenus dans une plage particulière de la feuille, par exemple Range("C1:C4")
(le Range("c1:C4") doit être totalement comprise dans la plage définie par le "NOM.)
Sub efface3()
With Worksheets("Feuil1")
For Each n In .Parent.Names
If TypeName(Evaluate(n.Name)) = "Range" Then
If Union(.Range("C1:C4"), Range(n.Name)).Address = Range(n.Name).Address Then
n.Delete
End If
End If
Next
End With
End Sub
D) Et si les 2 plages range("C1:C4") et Nom doivent être identiques, tu ajoutes un test comme ceci :
Sub efface4()
With Worksheets("Feuil1")
For Each n In .Parent.Names
If TypeName(Evaluate(n.Name)) = "Range" Then
If Union(.Range("C1:C4"), Range(n.Name)).Address = Range(n.Name).Address And _
Union(.Range("C1:C4"), Range(n.Name)).Address = .Range("c1:C4").Address Then
n.Delete
End If
End If
Next
End With
End Sub
NomFeuilleActive!NomPlage
Sub efface1()
Dim N As Name
With ActiveSheet
For Each N In .Names
N.Delete
Next
End With
End Sub
B) pour effacer les noms se référant à une plage de la feuille active définis par
par NomPlage et NomFeuilleActive!NomPlage
Sub Efface2()
Dim nom As String
nom = ActiveSheet.Name
nc = Len(nom)
For Each N In ActiveWorkbook.Names
v = N.RefersTo
If Mid(v, 2, nc + 1) = nom & "!" Then N.Delete
Next N
End Sub
C) si tu désires supprimer seulement les noms contenus dans une plage particulière de la feuille, par exemple Range("C1:C4")
(le Range("c1:C4") doit être totalement comprise dans la plage définie par le "NOM.)
Sub efface3()
With Worksheets("Feuil1")
For Each n In .Parent.Names
If TypeName(Evaluate(n.Name)) = "Range" Then
If Union(.Range("C1:C4"), Range(n.Name)).Address = Range(n.Name).Address Then
n.Delete
End If
End If
Next
End With
End Sub
D) Et si les 2 plages range("C1:C4") et Nom doivent être identiques, tu ajoutes un test comme ceci :
Sub efface4()
With Worksheets("Feuil1")
For Each n In .Parent.Names
If TypeName(Evaluate(n.Name)) = "Range" Then
If Union(.Range("C1:C4"), Range(n.Name)).Address = Range(n.Name).Address And _
Union(.Range("C1:C4"), Range(n.Name)).Address = .Range("c1:C4").Address Then
n.Delete
End If
End If
Next
End With
End Sub
Denis Michon, Serge Garneau,
Ajouté ou modifié le 05/01/2008 (N°1956)
Ajouté ou modifié le 05/01/2008 (N°1956)
Coment définir une plage dynamiquement pour qu'elle aille de la cellule A2 à la première cellule contenant la date d'aujourd'hui ?
Si tes dates sont en A3:Axxx, dans le menu insertion/nom/définir (ou dans xl2007 dans formules/définir un nom), tape la formule suivante:
=DECALER(Feuil1!$3;;;EQUIV(AUJOURDHUI();Feuil1!:;0)-2)
=DECALER(Feuil1!$3;;;EQUIV(AUJOURDHUI();Feuil1!:;0)-2)
Alain Vallon,
Ajouté ou modifié le 12/08/2007 (N°1874)
Ajouté ou modifié le 12/08/2007 (N°1874)
Via le menu insertion/nom/définir, j'ai donné des noms à des plages de données. Comment utiliser ces noms dans une macro ?
Si MaPlage fait référence à la zone B4:D35, dans ta macro, par exemple pour sélectionner cette sone, tu peux utiliser au choix
range("MaPlage").select
ou
[MaPlage].select
Ceci fonctionne bien sur aussi si tu as utilisé une formule dans insertion/nom/définir pour nommer ta plage
range("MaPlage").select
ou
[MaPlage].select
Ceci fonctionne bien sur aussi si tu as utilisé une formule dans insertion/nom/définir pour nommer ta plage
Flo Cabon,
Ajouté ou modifié le 22/05/2007 (N°1835)
Ajouté ou modifié le 22/05/2007 (N°1835)
Comment nommer la plage de données en colonne B définie par le fait de partager un même critère en colonne A ?
Pour éviter le recours à des fonctions volatiles pour nommer tes plages de données, ce qui est préférable si tu as de nombreuses plages à nommer, fais le par VBA.
Sub Nomme()
Set MonDico = CreateObject("Scripting.Dictionary")
For Each c In Range([A1], [A65000].End(xlUp))
If Not MonDico.Exists(c.Value) Then MonDico.Add c.Value, c.Value Next c For Each c In MonDico.items
ActiveWorkbook.Names.Add Name:="critere" & c, _
RefersTo:="=" & [B1].Offset(Application.Match(c, [A:A], 0) - 1,
0).Resize(Application.CountIf([A:A], c), 1).Address
Next c
[D20].ListNames
End Sub
Sub Nomme()
Set MonDico = CreateObject("Scripting.Dictionary")
For Each c In Range([A1], [A65000].End(xlUp))
If Not MonDico.Exists(c.Value) Then MonDico.Add c.Value, c.Value Next c For Each c In MonDico.items
ActiveWorkbook.Names.Add Name:="critere" & c, _
RefersTo:="=" & [B1].Offset(Application.Match(c, [A:A], 0) - 1,
0).Resize(Application.CountIf([A:A], c), 1).Address
Next c
[D20].ListNames
End Sub
Jacques Boisgontier,
Ajouté ou modifié le 12/05/2007 (N°1829)
Ajouté ou modifié le 12/05/2007 (N°1829)
Comment nommer la plage de données en colonne B définie par le fait de partager un même critère en colonne A ?
La valeur du critère étant en colonne A et celles des données en colonne B,
Le valeur du critère en $2 (on peut aussi l'écrire directement dans la formule)
dans insertion/nom/définir, indique la formule ci-dessous:
=DECALER(Feuil1!$1;EQUIV(Feuil1!$2;Feuil1!$1:$1000;0)-1;0;NB.SI(Feuil1!$1:$1000;Feuil1!$2);1)
Attention, les critères en colonne A doivent être regroupés.
Le valeur du critère en $2 (on peut aussi l'écrire directement dans la formule)
dans insertion/nom/définir, indique la formule ci-dessous:
=DECALER(Feuil1!$1;EQUIV(Feuil1!$2;Feuil1!$1:$1000;0)-1;0;NB.SI(Feuil1!$1:$1000;Feuil1!$2);1)
Attention, les critères en colonne A doivent être regroupés.
Jacques Boisgontier,
Ajouté ou modifié le 12/05/2007 (N°1828)
Ajouté ou modifié le 12/05/2007 (N°1828)
Je voudrais rechercher dans les colonnes A à D le n° de la ligne qui contient un texte donné,
sélectionner alors la plage de A1 à D&numéro de la ligne, et enfin attribuer un nom à la
plage ainsi sélectionnée.
With Range("A:D")
Range("A1:D" & .Find("machaine", .Item(1), , , ,
xlPrevious).Row).Select
ActiveWorkbook.Names.Add Name:="zaza",
RefersTo:=Selection.Address
End With
Range("A1:D" & .Find("machaine", .Item(1), , , ,
xlPrevious).Row).Select
ActiveWorkbook.Names.Add Name:="zaza",
RefersTo:=Selection.Address
End With
Alain Vallon, (N°1158)
Comment renommer un fichier par script VBA ?
Name "C:TempToto.xls" As "C:TempTata.xls"
Laurent Longre, (N°1157)
J'ai créé un classeur dont chaque page contient une cellule portant le nom d'une personne
comment puis-je faire pour que automatiquement la feuille prenne pour nom le nom de la personne
saisie ?
Si tes noms sont en A1 de chaque feuille
On Error Resume Next
For Each sht In ActiveWorkbook.Worksheets
Sheets(sht.Name).Name = Sheets(sht.Name).[A1]
Next
On Error Resume Next
For Each sht In ActiveWorkbook.Worksheets
Sheets(sht.Name).Name = Sheets(sht.Name).[A1]
Next
Alain Vallon, (N°1156)
Comment redimensionner une plage nommée ?
Tu peux utiliser l'instruction Resize si tu pars d'une plage existante.
Sub AgrandirLaPlage()
Dim UnePlage As Range
Set UnePlage = Range("A1:B2")
UnePlage.Name = "Plage"
UnePlage.Resize(UnePlage.Rows.Count + 5).Name = "Plage" ' 5 lignes de +
MsgBox Names("Plage").RefersTo 'A1:B7
End Sub
Sub AgrandirLaPlage()
Dim UnePlage As Range
Set UnePlage = Range("A1:B2")
UnePlage.Name = "Plage"
UnePlage.Resize(UnePlage.Rows.Count + 5).Name = "Plage" ' 5 lignes de +
MsgBox Names("Plage").RefersTo 'A1:B7
End Sub
Daniel Maher, (N°1155)
Lorsque l'on définit un nom (Insertion Définir Nom) et que l'on saisit une formule, il y
a un
problème de recalcul si cette fonction est recopiée sur plusieurs onglets. Pourquoi et
comment
contourner le problème ?
1/ La portée d'un nom peut être relative à chaque onglet sans créer un nom par onglet.
Après avoir créé MaFonc dans la Feuille1, revenir dans Insertion / Nom /Définir pour ne
laisser
que le point d'exclamation : on a =!$1 au lieu de Feuil1!$1
2/ Je peux créer =MaFonc dans l'onglet Feuil2, le $1 fera référence à la cellule A1 de
l'onglet Feuil2 et non à celle de Feuil1
3/ Ceci marchait parfaitement dans Excel 5. Dans Excel 97 et 2000, le recalcul ne se fait
pas.
4/ Avec le "truc" suivant, cela fonctionne :
Dans une cellule, par exemple AutreFeuil!$1, mettre =si(maintenant();0;0) et modifier le
nom
MaFonc par = !$1 + AutreFeuil!$1
Cela force le recalcul.
Attention cependant, parceque la solution de la définition d'un nom par =!$1 est non
volatile !
Pas de mise à jour des calculs utilisant ce nom quand on change la valeur de A1. Pour
garder une solution dynamique,
Insertion > Nom > Définir > Nom dans le classeur :
"varA1" > fait référence à :
=INDIRECT("$1")
Après avoir créé MaFonc dans la Feuille1, revenir dans Insertion / Nom /Définir pour ne
laisser
que le point d'exclamation : on a =!$1 au lieu de Feuil1!$1
2/ Je peux créer =MaFonc dans l'onglet Feuil2, le $1 fera référence à la cellule A1 de
l'onglet Feuil2 et non à celle de Feuil1
3/ Ceci marchait parfaitement dans Excel 5. Dans Excel 97 et 2000, le recalcul ne se fait
pas.
4/ Avec le "truc" suivant, cela fonctionne :
Dans une cellule, par exemple AutreFeuil!$1, mettre =si(maintenant();0;0) et modifier le
nom
MaFonc par = !$1 + AutreFeuil!$1
Cela force le recalcul.
Attention cependant, parceque la solution de la définition d'un nom par =!$1 est non
volatile !
Pas de mise à jour des calculs utilisant ce nom quand on change la valeur de A1. Pour
garder une solution dynamique,
Insertion > Nom > Définir > Nom dans le classeur :
"varA1" > fait référence à :
=INDIRECT("$1")
Xavier, Alain Vallon, (N°1154)
Peut-on construire une zone nommée avec des cellules non adjacentes ?
Oui on peut. Cette zone peut être ensuite utilisée par son nom dans certaines
fonctions comme MOYENNE, SOMME, NBVAL..etc.
Le problème est que certaines fonctions telles que, NB.SI, SOMME.SI, les SommeProd
et matricielles..etc.., ne le supportent pas !
Un interêt assez peu connu et non négligeable :
Lorsqu'on nomme une plage discontinue de "cellules solitaires non
verroullées" en utilisant la touche Ctrl, on peut, (après protection de la
feuille) se balader dans cette plage (avec la touche Tab), dans l'ordre
strictement défini par la chronologie de la sélection.
fonctions comme MOYENNE, SOMME, NBVAL..etc.
Le problème est que certaines fonctions telles que, NB.SI, SOMME.SI, les SommeProd
et matricielles..etc.., ne le supportent pas !
Un interêt assez peu connu et non négligeable :
Lorsqu'on nomme une plage discontinue de "cellules solitaires non
verroullées" en utilisant la touche Ctrl, on peut, (après protection de la
feuille) se balader dans cette plage (avec la touche Tab), dans l'ordre
strictement défini par la chronologie de la sélection.
Alain Vallon, (N°1153)
Par VBA, comment demander à un utilisateur de donner un nom à une feuille qui est crée et
comment contrôler que ce nom n'est pas déjà attribué à une autre feuille ?
Sub NouvelleFeuille()
ActiveWorkbook.Sheets.Add
Do
Nom = InputBox("Entrez un nom pour la nouvelle feuille :")
If Nom = "" Then Exit Sub
On Error Resume Next
Set sht = Sheets(Nom)
If Err <> 0 Then
ActiveSheet.Name = Nom
Err.Clear: Exit Do
Else
MsgBox "Une feuille de ce nom existe déjà !"
End If
Loop
End Sub
****************************
Ou encore pour éviter les goto et vérifier que les noms de feuille choisis sont valides :
Sub NomOnglet()
Dim Rep As String
msg = "Comment voulez-vous nommer la nouvelle feuille ?"
Rep = InputBox(msg, "Saisie du nom")
If Rep = "" Then Exit Sub
On Error Resume Next
Application.ScreenUpdating = False
ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Rep
If Err <> 0 Then
Err.Clear
With Application
.ScreenUpdating = True
.DisplayAlerts = False
End With
ActiveSheet.Delete
msg = "Le nom de feuille que vous avez tapé n'est pas valide !" _
& vbCrLf
msg = msg & vbCrLf
msg = msg & "- Vérifiez que le nom de la feuille ne dépasse " _
& "pas 31 caractères" & vbCrLf
msg = msg & "- Vérifiez que le nom de la feuille ne contient " _
& "aucun des caractères suivants :" & vbCrLf
msg = msg & " \ / : ? * [ ou ]" & vbCrLf
msg = msg & "- Vérifiez qu'une feuille du classeur ne possède " _
& "pas déjà un nom identique" & vbCrLf
Reponse = MsgBox(msg, , "Saisie invalide")
NomOnglet
End If
End Sub
ActiveWorkbook.Sheets.Add
Do
Nom = InputBox("Entrez un nom pour la nouvelle feuille :")
If Nom = "" Then Exit Sub
On Error Resume Next
Set sht = Sheets(Nom)
If Err <> 0 Then
ActiveSheet.Name = Nom
Err.Clear: Exit Do
Else
MsgBox "Une feuille de ce nom existe déjà !"
End If
Loop
End Sub
****************************
Ou encore pour éviter les goto et vérifier que les noms de feuille choisis sont valides :
Sub NomOnglet()
Dim Rep As String
msg = "Comment voulez-vous nommer la nouvelle feuille ?"
Rep = InputBox(msg, "Saisie du nom")
If Rep = "" Then Exit Sub
On Error Resume Next
Application.ScreenUpdating = False
ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Rep
If Err <> 0 Then
Err.Clear
With Application
.ScreenUpdating = True
.DisplayAlerts = False
End With
ActiveSheet.Delete
msg = "Le nom de feuille que vous avez tapé n'est pas valide !" _
& vbCrLf
msg = msg & vbCrLf
msg = msg & "- Vérifiez que le nom de la feuille ne dépasse " _
& "pas 31 caractères" & vbCrLf
msg = msg & "- Vérifiez que le nom de la feuille ne contient " _
& "aucun des caractères suivants :" & vbCrLf
msg = msg & " \ / : ? * [ ou ]" & vbCrLf
msg = msg & "- Vérifiez qu'une feuille du classeur ne possède " _
& "pas déjà un nom identique" & vbCrLf
Reponse = MsgBox(msg, , "Saisie invalide")
NomOnglet
End If
End Sub
Frédéric Sigonneau, ChrisV, (N°1152)
Comment faire pour attribuer un nom à une plage de cellules ?
Méthode de base :
Sélectionne la plage en question puis menu
insertion/nom/définir, entre le nom et ensuite tu peux te référer à cette plage
par son nom dans tes formules. Maintenant si cette plage de cellules est appellée à varier,
regarde du côté de la fonction decaler.
Pour faciliter la saisie du nom, tu peux aussi, à gauche de la barre de formule, là ou
normalement tu vois l'adresse de ta cellule entrer le nom que tu veux pour la plage
sélectionnée. Quand tu passes la souris sur cette barre tu vois d'ailleurs écrit "zone de
nom". Entre le nom et valide avec retour. Attention, l'utilisation de cette zone n'est pas
des plus évidentes ensuite pour supprimer/modifier les noms ou les références. En cas de pb
repasse par le menu.
Tu peux d'ailleurs aussi donner un nom à des graphiques de cette manière. Pour cela attention,
il ne faut pas cliquer sur le graphe pour le sélectionner mais aller dans
édition/atteindre/cellules/objets et ensuite utiliser la touche tab pour sélectionner l'objet
(graphe) de ton choix.
Si c'est par VBA :
ActiveWorkbook.Names.Add Name:="PLAGE1",RefersToR1C1:="=Feuil1!R1C10:R5C10"
Si tu es amené à nommer fréquemment des cellules tu peux utiliser cette macro de Frédéric qui
permet
d'aller directement dans le menu insertion/nom/définir sur un clic droit de souris : Avec ça tu
sélectionnes ta plage à nommer, clic droit, "Définir un nom..." et la boite de
dialogue s'affiche directement :
'dans un nouveau classeur à enregistrer en .xla
'dans le module ThisWorkbook :
Private Sub
Workbook_AddinInstall()
CmdNommer
End Sub
Private Sub
Workbook_AddinUninstall()
DelNommer
End Sub
'================================
'dans un module standard
Public Const Nommer = "Définir un nom..."
Sub CmdNommer()
Dim cBar As CommandBar,
Ctrl As CommandBarButton
Set cBar = Application.CommandBars("Cell")
Set
Ctrl = cBar.Controls.Add(msoControlButton, ID:=878, before:=1)
With Ctrl
.Caption = Nommer
.FaceId = 1838
.Style = msoButtonIconAndCaption
End With
End Sub
Sub DelNommer()
On Error Resume Next
Application.CommandBars("Cell").Controls(Nommer).Delete
End Sub
n'oublions pas la très pratique "définition collective de noms" par *Insertion Nom
Créer* (raccourci CTRL MAJ F3) qui permet de définir d'un coup toutes les colonnes et/ou les
lignes d'un tableau en s'appuyant sur les étiquettes de lignes et/ou de colonnes dudit tableau.
Sélectionne la plage en question puis menu
insertion/nom/définir, entre le nom et ensuite tu peux te référer à cette plage
par son nom dans tes formules. Maintenant si cette plage de cellules est appellée à varier,
regarde du côté de la fonction decaler.
Pour faciliter la saisie du nom, tu peux aussi, à gauche de la barre de formule, là ou
normalement tu vois l'adresse de ta cellule entrer le nom que tu veux pour la plage
sélectionnée. Quand tu passes la souris sur cette barre tu vois d'ailleurs écrit "zone de
nom". Entre le nom et valide avec retour. Attention, l'utilisation de cette zone n'est pas
des plus évidentes ensuite pour supprimer/modifier les noms ou les références. En cas de pb
repasse par le menu.
Tu peux d'ailleurs aussi donner un nom à des graphiques de cette manière. Pour cela attention,
il ne faut pas cliquer sur le graphe pour le sélectionner mais aller dans
édition/atteindre/cellules/objets et ensuite utiliser la touche tab pour sélectionner l'objet
(graphe) de ton choix.
Si c'est par VBA :
ActiveWorkbook.Names.Add Name:="PLAGE1",RefersToR1C1:="=Feuil1!R1C10:R5C10"
Si tu es amené à nommer fréquemment des cellules tu peux utiliser cette macro de Frédéric qui
permet
d'aller directement dans le menu insertion/nom/définir sur un clic droit de souris : Avec ça tu
sélectionnes ta plage à nommer, clic droit, "Définir un nom..." et la boite de
dialogue s'affiche directement :
'dans un nouveau classeur à enregistrer en .xla
'dans le module ThisWorkbook :
Private Sub
Workbook_AddinInstall()
CmdNommer
End Sub
Private Sub
Workbook_AddinUninstall()
DelNommer
End Sub
'================================
'dans un module standard
Public Const Nommer = "Définir un nom..."
Sub CmdNommer()
Dim cBar As CommandBar,
Ctrl As CommandBarButton
Set cBar = Application.CommandBars("Cell")
Set
Ctrl = cBar.Controls.Add(msoControlButton, ID:=878, before:=1)
With Ctrl
.Caption = Nommer
.FaceId = 1838
.Style = msoButtonIconAndCaption
End With
End Sub
Sub DelNommer()
On Error Resume Next
Application.CommandBars("Cell").Controls(Nommer).Delete
End Sub
n'oublions pas la très pratique "définition collective de noms" par *Insertion Nom
Créer* (raccourci CTRL MAJ F3) qui permet de définir d'un coup toutes les colonnes et/ou les
lignes d'un tableau en s'appuyant sur les étiquettes de lignes et/ou de colonnes dudit tableau.
Flo Cabon, Thierry Rural, Frédéric Sigonneau, Bernard Mazas, (N°1151)
Existe-t-il une fonction permettant de renvoyer le nom d'une feuille dans une cellule ?
La formule suivante suppose que le classeur a déjà été enregistré au moins une
fois. Elle repose sur la formule =CELLULE("filename";A1), renvoyant le chemin
d'accès complet de la feuille. Remplacer la référence A1 par celle de la cellule
où est saisie la formule.
=STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;32)
Ou encore :
=DROITE(CELLULE("nomfichier");NBCAR(CELLULE("nomfichier"))-TROUVE _
("]";CELLULE("nomfichier")))
fois. Elle repose sur la formule =CELLULE("filename";A1), renvoyant le chemin
d'accès complet de la feuille. Remplacer la référence A1 par celle de la cellule
où est saisie la formule.
=STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;32)
Ou encore :
=DROITE(CELLULE("nomfichier");NBCAR(CELLULE("nomfichier"))-TROUVE _
("]";CELLULE("nomfichier")))
Laurent Longre, Pierre Fauconnier, (N°1150)
Dans un classeur, j'ai en colonne A des cellules qui toutes sont nommées. Je voudrais que dans
la colonne B apparaisse le nom des cellules correspondantes de la colonne A.
Function NOM(Cellule As Range) As String
NOM = Cellule.Name.Name
End Function
Et ensuite : =NOM(A1)
Si tu renommes des cellules après coup, il faut faire un Ctrl-Alt-F9
pour mettre à jour les noms renvoyés par cette fonction.
NOM = Cellule.Name.Name
End Function
Et ensuite : =NOM(A1)
Si tu renommes des cellules après coup, il faut faire un Ctrl-Alt-F9
pour mettre à jour les noms renvoyés par cette fonction.
Laurent Longre, (N°1149)
Est-ce que quelqu'un pourrait m'éclairer sur la commande Insertion Nom Etiquettes.
Lorsque l'option "Utiliser les étiquettes dans les formules" de l'onglet
"Calcul" de la boite "Options" du menu "Outils" est cochée, il
est possible
d'utiliser des étiquettes de lignes et de colonne dans un tableau.
Ainsi, dans un tableau qui aurait en :
B1 - Janvier
C1 - Février
D1 - Mars
A2 - Stylos
A3 - Papiers
on peut utiliser en E2 la formule "=somme(stylos)". Cette formule peut être
tirée vers le bas et Excel convertit automatiquement en "somme(papiers").
Même chose en vertical avec les mois.
On peut aussi utiliser une la formule "sytlos janvier" (avec l'espace entre
les deux mots), pour obtenir directement la valeur de B2.
Lorsque les étiquettes sont des dates, il faut les entrer dans la formule
avec le signe ' qui encadre l'étiquette.
Si on a en B1 la valeur 13/01, on doit saisir "=somme('13/01')" pour obtenir
une formule valide. Pour éviter cela, on peut utiliser l'insertion de noms
d'étiquettes. Pour l'exemple ci-dessus, on se positionne en B1, on utilise
le menu Insertion, Nom, Etiquette (Excel 2000) ou Insertion, Nom, Intitulé
(Excel 97, si je ne me trompe). Il n'est alors plus nécessaire d'utiliser
les signes ' dans la formule.
"Calcul" de la boite "Options" du menu "Outils" est cochée, il
est possible
d'utiliser des étiquettes de lignes et de colonne dans un tableau.
Ainsi, dans un tableau qui aurait en :
B1 - Janvier
C1 - Février
D1 - Mars
A2 - Stylos
A3 - Papiers
on peut utiliser en E2 la formule "=somme(stylos)". Cette formule peut être
tirée vers le bas et Excel convertit automatiquement en "somme(papiers").
Même chose en vertical avec les mois.
On peut aussi utiliser une la formule "sytlos janvier" (avec l'espace entre
les deux mots), pour obtenir directement la valeur de B2.
Lorsque les étiquettes sont des dates, il faut les entrer dans la formule
avec le signe ' qui encadre l'étiquette.
Si on a en B1 la valeur 13/01, on doit saisir "=somme('13/01')" pour obtenir
une formule valide. Pour éviter cela, on peut utiliser l'insertion de noms
d'étiquettes. Pour l'exemple ci-dessus, on se positionne en B1, on utilise
le menu Insertion, Nom, Etiquette (Excel 2000) ou Insertion, Nom, Intitulé
(Excel 97, si je ne me trompe). Il n'est alors plus nécessaire d'utiliser
les signes ' dans la formule.
ChrisV, (N°1148)
Comment faire apparaître dans une cellule de la feuille le nom d'un onglet, d'un classeur,
d'un
répertoire ...?
Attention, les formules doivent être sur une seule ligne...
1. Chemin d'accès complet :
=CELLULE("filename";A1)
2. Répertoire:
=GAUCHE(CELLULE("filename";A1);TROUVE("[";CELLULE("filename"A1))-2)
3. Nom du classeur seul:
=STXT(CELLULE("filename";A1);TROUVE("[";CELLULE("filename";A1))+1;SOMME(TROUVE({"[";"]"};CELLULE("filename";A1))*{-1;1})-1)
4. Nom de la feuille:
=STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;32)
5. Répertoire + classeur:
=SUBSTITUE(GAUCHE(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))-1);"[";"")
6. Nom du classeur et de la feuille:
=STXT(CELLULE("filename";A1);TROUVE("[";CELLULE("filename";A1));300)
Et... comment ça marche ce truc ?
=STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;32)
la formule =CELLULE("filename";A1) retourne le chemin complet. par exemple:
C:\Documents and Settings\Administrateur\Application Data\Microsoft\Excel\XLSTART\[PERSO.XLS]Feuil4
tu remarques que le nom du classeur est mis entre crochets [ ]
il reste à trouver l'emplacement de "]" ce que fait la fonction
TROUVE("]";CELLULE("filename";A1)
et la fonction STXT
STXT(texte;no_départ;no_car)
avec un no_car à 32 nous donne la fin du texte car il est très rare que le nom d'une feuille comporte plus de 32 caractères.
1. Chemin d'accès complet :
=CELLULE("filename";A1)
2. Répertoire:
=GAUCHE(CELLULE("filename";A1);TROUVE("[";CELLULE("filename"A1))-2)
3. Nom du classeur seul:
=STXT(CELLULE("filename";A1);TROUVE("[";CELLULE("filename";A1))+1;SOMME(TROUVE({"[";"]"};CELLULE("filename";A1))*{-1;1})-1)
4. Nom de la feuille:
=STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;32)
5. Répertoire + classeur:
=SUBSTITUE(GAUCHE(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))-1);"[";"")
6. Nom du classeur et de la feuille:
=STXT(CELLULE("filename";A1);TROUVE("[";CELLULE("filename";A1));300)
Et... comment ça marche ce truc ?
=STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;32)
la formule =CELLULE("filename";A1) retourne le chemin complet. par exemple:
C:\Documents and Settings\Administrateur\Application Data\Microsoft\Excel\XLSTART\[PERSO.XLS]Feuil4
tu remarques que le nom du classeur est mis entre crochets [ ]
il reste à trouver l'emplacement de "]" ce que fait la fonction
TROUVE("]";CELLULE("filename";A1)
et la fonction STXT
STXT(texte;no_départ;no_car)
avec un no_car à 32 nous donne la fin du texte car il est très rare que le nom d'une feuille comporte plus de 32 caractères.
Laurent Longre, Isabelle, (N°1147)
Comment retrouver dans une feuille les adresses correspondant aux cellules nommées ?
sans VBA :
Insertion / Nom / Coller / Coller une liste
fournit la liste des noms du classeur avec leurs références.
Avec une exception majeure : les noms "3D"...
Qu'est ce qu'un nom 3D ?
C'est un nom (toto) affecté à la cellule A2 par exemple de toutes
les feuilles d'un classeur.
Son nom sur la feuil1 est feuil1!toto
la même cellule A2 sur la feuille 2 s'appelle feuil2!toto
ces noms n'apparaissent pas lorsque tu colles la liste comme ci-dessus.
Si tu veux récupérer la liste complète des noms d'un classeur, tu peux
utiliser cette macro à mettre dans un module standard : elle liste les
noms de plage du classeur dans une feuille appelée "Noms de plage"
qu'elle crée si elle n'existe pas.
Sub NomPlages()
Dim Feuille As Worksheet
Dim FeuilleExiste As Boolean
Dim Nom As Name
Dim Cellule As Range
For Each Feuille In ActiveWorkbook.Sheets
If Feuille.Name = "Noms de plage" Then FeuilleExiste = True
Next Feuille
If FeuilleExiste = True Then
Sheets("Noms de plage").Cells.Delete
Else
Set Feuille = ActiveWorkbook.Sheets.Add()
Feuille.Name = "Noms de plage"
End If
Set Feuille = Sheets("Noms de plage")
With Feuille
.Range("a1") = "Nom"
.Range("b1") = "Référence"
For Each Nom In ActiveWorkbook.Names
Set Cellule = .Range("a65536").End(xlUp)(2)
Cellule = Nom.Name
Cellule(1, 2).Value = "'" & Nom.RefersTo
Next Nom
End With
End Sub
Insertion / Nom / Coller / Coller une liste
fournit la liste des noms du classeur avec leurs références.
Avec une exception majeure : les noms "3D"...
Qu'est ce qu'un nom 3D ?
C'est un nom (toto) affecté à la cellule A2 par exemple de toutes
les feuilles d'un classeur.
Son nom sur la feuil1 est feuil1!toto
la même cellule A2 sur la feuille 2 s'appelle feuil2!toto
ces noms n'apparaissent pas lorsque tu colles la liste comme ci-dessus.
Si tu veux récupérer la liste complète des noms d'un classeur, tu peux
utiliser cette macro à mettre dans un module standard : elle liste les
noms de plage du classeur dans une feuille appelée "Noms de plage"
qu'elle crée si elle n'existe pas.
Sub NomPlages()
Dim Feuille As Worksheet
Dim FeuilleExiste As Boolean
Dim Nom As Name
Dim Cellule As Range
For Each Feuille In ActiveWorkbook.Sheets
If Feuille.Name = "Noms de plage" Then FeuilleExiste = True
Next Feuille
If FeuilleExiste = True Then
Sheets("Noms de plage").Cells.Delete
Else
Set Feuille = ActiveWorkbook.Sheets.Add()
Feuille.Name = "Noms de plage"
End If
Set Feuille = Sheets("Noms de plage")
With Feuille
.Range("a1") = "Nom"
.Range("b1") = "Référence"
For Each Nom In ActiveWorkbook.Names
Set Cellule = .Range("a65536").End(xlUp)(2)
Cellule = Nom.Name
Cellule(1, 2).Value = "'" & Nom.RefersTo
Next Nom
End With
End Sub
Pierre Fauconnier, (N°1146)
Dans un classeur j'ai une feuille nommée par VBA dans la fenêtre propriété
"feuille_zaza", le nom de son onglet étant zaza. Qu'elle est la syntaxe pour activer
cette feuille par son nom "feuille_zaza", (qui est le même dans 20 classeurs
différents à traiter) et non pas par son numéro ou son nom d'onglet qui peut être en 3 langues
différentes ?
Workbooks("rien.xls").VBProject.VBComponents("feuille_zaza").Activate
Jacques Chaussard, (N°1145)