XL 2013 lister toutes les cellules dont la formule contient au moins une plage nommée

[Jouxte]

Bonjour à toutes et tous,

Dans un classeur aux multiples onglets et aux plages nommées qui se sont multipliées au fil du temps, je souhaite faire du ménage dans ces plages nommées.
A cette fin je souhaite lister toutes les cellules dont la formule contient au moins une plage nommée.

Merci par avance pour votre aide et bonnes fêtes de fin d'année..

 

[Jouxte]

Re,

@Dudu2
erreur d'exécution '9':
l'indice n'appartient pas à la sélection
TabNoms(k) = ThisWorkbook.Names(k).Name

 

[Dudu2]

@Jouxte,
Oui je sais j'ai bugé😉, je voulais avoir mon erreur 9 aussi 😛
Essaie de reprendre le code modifié.

 

[Jouxte]

@Dudu2
avec le code du post 15
erreur 13 incompatibilité de type
à la ligne
[A2].Resize(UBound(formules, 2), UBound(formules, 1)) = Application.Transpose(formules)

 

[Dudu2]

Ah bizarre !
Reprends le code encore une fois dans les post car j'ai du mal à accoucher d'un code qui fonctionne.
En voulant faire la malin avec mes initialisations de tableaux. Je suis revenu aux bases 🙂.
J'ai ajouté .Value à l'instruction que tu signales en erreur.

 

[Jouxte]

Rien n'y fait.
j'ai essayé avec un Range("A2")...avec ou sans .Value
même erreur

 

[Staple1600]

Re,
@Staple1600 j'ai la même erreur en utilisant la procédure ci-dessus.

Jouxte
En dedans de ta PJ, point de code de bibi, mais juste du code à Dudu 😉
Et le code à bibi fonctionnait sans bobo
Et sans être baba, le résultat suivant, il me fournissat
(c'est pour la rime 😉)
Nom feuille Adresse Formule
Feuil3 C2 =Test1+test2

 

[Dudu2]

Décidément, ce truc refuse de fonctionner.
J'ai essayé chez mois avec plus de 1000 formules, ça ne génère pas d'erreur.
Prends le code de @Staple1600, on en finira avec cette affaire.

Sinon essaie quand même de remplacer la dernière instruction par

formules = Application.Transpose(formules)
MsgBox UBound(formules, 1) & " formules listées"
[A1].Resize(UBound(formules, 1), UBound(formules, 2)).Value = formules

Ou encore de faire une déclaration explicitement Variant de formules()

Dim formules() As Variant

 

[Jouxte]

@Staple1600
Toutes mes excuses.
Le code fonctionne avec le fichier de test. (mais pas avec mon fichier)
il y a 15 feuilles. Est-ce là le problème

 

[Staple1600]

=>jouxte
Alors, on va faire dans l'empirique 😉

Sub Lister_Formules_II()
Dim ws As Worksheet, j&, formules(), rng1 As Range, rng2 As Range
j = 1
ReDim Preserve formules(1 To 50000, 1 To 3)
'si erreur remplacer 50000 par une valeur supérieure
For Each ws In ActiveWorkbook.Sheets
Set rng1 = Nothing
    On Error Resume Next
    Set rng1 = ws.Cells.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
    If Not rng1 Is Nothing Then
        For Each rng2 In rng1
        formules(j, 1) = rng1.Parent.Name
        formules(j, 2) = rng2.Address(0, 0)
        formules(j, 3) = "'" & CStr(rng2.FormulaLocal)
         j = j + 1
        Next rng2
    End If
Next ws
Sheets.Add
[A1:C1] = Array("Nom feuille", "Adresse", "Formule")
[A2].Resize(UBound(formules, 1), UBound(formules, 2)) = formules
End Sub

 

[Jouxte]

Bonjour, Staple1600, Dudu2, le Forum,

@Staple1600
Merci beaucoup pour ce code.
Vive l'empirisme !
Il ne reste plus qu'à "auditer" les quelques 9 500 formules que le code a trouvé.

Bonne journée et bonnes fêtes de fin d'année.

 

[Staple1600]

Re

Un petit complément (qui peut aider)

Sub Lister_noms()
Sheets.Add
[A1:B1] = [{"Noms","Fait référence à"}]
[A2].ListNames
Range("A:B").Columns.AutoFit
End Sub

 

[Jouxte]

Re,

Top, ça aide effectivement.
Merci beaucoup.