SUPPRIMER LES NOMS DES PLAGES UTILISER DANS LES FORMULES SANS MODIFIER CES FORMULES

[chrisvol]

Bonjour
Svp, j'ai un fichier comportant des plages nommées dont les noms ont été utilisés dans les formules de mes tableaux. j'aimerais supprimer tous ces noms sans modifier les résultat de mon tableau, c'est à dire faire en sorte que ce sont les références des plages sélectionnées qui s'affichent dans les formules et non plus les noms. j'aimerais savoir une méthode qui n'est pas le VBA

Merci

 

[Theze]

Bonjour,

Teste ce code :

Sub Test()
   
    Dim Fe As Worksheet
    Dim Cel As Range
    Dim I As Integer
   
    Set Fe = ActiveSheet
   
    'boucle sur les cellules contenant une formule
    For Each Cel In Fe.Cells.SpecialCells(xlCellTypeFormulas)
       
        'boucle sur les noms qui peuvent être plusieurs dans la formule
        For I = 1 To Names.Count
           
            Cel.Formula = Replace(Cel.Formula, Names(I).Name, Names(I).RefersToRange.Address)

        Next I
       
    Next Cel
   
End Sub

 

[chrisvol]

Merci Theze. Mais n'est il pas possible de résoudre ça sans passer par le VBA stp? Je m'y connais pas trop dans le VBA.

 

[mapomme]

Bonsoir à tous,

Si le nombre de noms est faible et avec quelques précautions, on doit pouvoir, le faire à la main. --> Voir fichier

En VBA la contrainte est aussi présente me semble-t-il. En vba, il faut peut-être sélectionner chaque cellule avant d'y remplacer les noms.

 

[mapomme]

Re,

En VBA, basé sur code de Theze que je salue 🙂, avec sélection ou non de la cellule courante de la boucle.

 

[djidji59430]

Bonjour,
Si tu ne veux pas te servir des propriétés des tableaux, tu n'as qu'a les convertir en plage.

 

[chrisvol]

Bonsoir

mapomme, avec ta methode, je ne finirai jamais. c'est un classeur avec plusieurs feuil comportant plusieurs plages nommées. exactement 29.
djidji59430, ce n'est pas un tableau automatique.

Merci pour vos reponses

 

[chrisvol]

stp mapomme, comment faire pour obtenir la definition du nom d'une plage dans une cellule comme vous l'avez fait dans votre fichier? Vous l'avez saisi manuellement?

 

[Theze]

Bonjour,

Pour ce qui est du VBA, tu fais la combinaison de touches Alt+F11 là, tu te retrouves dans le VBE (Visual Basic Editor), tu cliques sur le menu "Insertion" et tu choisis "Module", à ce moment là, tu as une fenêtre qui s'ouvre sur la droite, c'est dans cette dernière que tu colles le code que je t'ai donné. Une fois ceci fait, tu places le curseur n'importe où dans ce code sans rien changer puis tu appuis sur la touche F5.
Mais attention, fais le test sur une copie de ton classeur et non sur l'original !

 

[chrisvol]

Bonsoir a tous
Theze, ton code VBA marche super bien sauf que ça convertit en référence uniquement les noms dont les formules et la source se trouvent dans la même feuil Excel. Comment faire lorsque la source se trouve dans une feuil autre que celle de la formule?
Merci beaucoup

 

[Theze]

Bonjour,

Teste cette modif :

Sub Test()
  
    Dim Fe As Worksheet
    Dim Cel As Range
    Dim I As Integer
  
    Set Fe = ActiveSheet
  
    'boucle sur les cellules contenant une formule
    For Each Cel In Fe.Cells.SpecialCells(xlCellTypeFormulas)
      
        'boucle sur les noms qui peuvent être plusieurs dans la formule
        For I = 1 To Names.Count
           
            If Names(I).RefersToRange.Parent.Name <> Fe.Name Then
                Cel.Formula = Replace(Cel.Formula, Names(I).Name, Names(I).RefersToRange.Parent.Name & "!" & Names(I).RefersToRange.Address)
            Else
                Cel.Formula = Replace(Cel.Formula, Names(I).Name, Names(I).RefersToRange.Address)
            End If
           
        Next I
      
    Next Cel
  
End Sub

 

[mapomme]

Bonsoir chrisvol

stp mapomme, comment faire pour obtenir la definition du nom d'une plage dans une cellule comme vous l'avez fait dans votre fichier? Vous l'avez saisi manuellement?

Pour cela:

• se placer dans une cellule de plage vide au sein d'une feuille
• choisir dans la bandeau le menu Formules
• choisir le menu UtiliserDsFormule
• dans la liste déroulante de UtiliseDsFormule, sélectionner Coller des noms...
• cliquer sur Coller une liste

 

[mapomme]

Bonjour chrisvol, Theze 🙂,

Une version qui recherche les noms à remplacer parmi les noms de niveau classeur et dans les noms de la feuille.