Microsoft 365 Retrouver les dépendants de cellules nommées

FRYS · 5 Février 2024

Bonjour à tous

J'utilise un classeur assez vieux, rempli de noms obsolètes, faisant référence à #REF! ou à d'autres classeurs innaccessibles
pour vérifier que ca ne génère pas d'erreur dans les calculs (et accessoirement néttoyer sans risque toutes ces vielles références) j'aimerais pouvoir retrouver - si il y en a - les cellules dépendantes des cellules nommées obsolètes

Malheureusement, j'ai besoin d'un objet de type RANGE pour utiliser "dependents"
j'ai bien essayer le code si-dessous, mais j'ai une erreur 1004
PS : j'avais aussi essayé avec "Set Cellule = WS.Cells.Find(nm.Name, LookIn:=xlFormulas " mais come certains noms utilise une suite de caractère très commune, j'ai beaucoup de déchets dans les résultats.

Merci de votre aide

Sub Repérage_dependants_noms()

Dim WS As Worksheet
Dim nm As Name
Dim Cellule As Range
Dim inc As Integer

inc = 1

For Each nm In ActiveWorkbook.Names

If InStr(nm.RefersTo, "#REF") Then

For Each WS In ActiveWorkbook.Sheets

nm.RefersToRange.Dependents.Select

For Each Cellule In Selection

Worksheets(1).Cells(inc, 7).Value = nm.Name
Worksheets(1).Cells(inc, 8).Value = WS.Name
Worksheets(1).Cells(inc, 9).Value = Cellule.Address
inc = inc + 1

Next

Next WS
End If

Next nm

End Sub

crocrocro · 6 Février 2024

Bonjour Frys,
la macro modifiée ci-dessous.
Améliorable, je n'ai pas réussi à faire le lien direct entre les noms obsolètes et les cellules qui les utilisent. d'où 2 boucles indépendantes.
Sur la feuille courante (dédiée au traçage) :
colonne A : le Nom du Gestionnaire des noms
colonne B C D : Le nom de la feuille, l'adresse de la cellule, la formule

VB:

Sub Repérage3_dependants_noms()

Dim WS As Worksheet
Dim nm As Name
Dim Cellule As Range
Dim i As Integer
Dim RechRef As String
Dim FinRech As Boolean
Dim RangeRef As Range
Dim RangeRefPrem As Range

    ' Balayage des Noms du Gestionnaire des noms
    i = 1
    For Each nm In ActiveWorkbook.Names
        If InStr(nm.RefersTo, "#REF") Then
            RechRef = nm.RefersTo
            i = i + 1
            ActiveSheet.Cells(i, 1) = "'" & RechRef
        End If
    Next nm
   
    ' Balayage des feuilles
    i = 1
    For Each WS In ThisWorkbook.Worksheets
        If WS.Name <> ActiveSheet.Name Then 
            For Each RangeRef In WS.UsedRange
                If IsError(RangeRef) Then
                    If CVErr(RangeRef) = CVErr(xlErrRef) Then
                        i = i + 1
                        With ActiveSheet
                            .Cells(i, 2) = WS.Name
                            .Cells(i, 3) = RangeRef.Address
                            .Cells(i, 4) = "'" & RangeRef.Formula
                        End With
                    End If
                End If
            Next RangeRef
        End If
    Next WS

End Sub

TooFatBoy · 6 Février 2024

Bonjour,

FRYS à dit:
J'utilise un classeur assez vieux, rempli de noms obsolètes, faisant référence à #REF! ou à d'autres classeurs innaccessibles
pour vérifier que ca ne génère pas d'erreur dans les calculs (et accessoirement néttoyer sans risque toutes ces vielles références) j'aimerais pouvoir retrouver - si il y en a - les cellules dépendantes des cellules nommées obsolètes

Sauf erreur de ma part, si tu as un #Ref! dans la référence, tu ne peux pas retrouver les adresses initiales, et tu peux supprimer le nom puisque sa référence n'est plus valide.

crocrocro · 6 Février 2024

D'accord avec TooFatBoy,
mais la recherche des cellules avec une erreur de référence ne me semble pas inutile. Une erreur de référence peut être due :
- à la référence à une cellule ou plage nommée qui n'existe plus. Elle peut appartenir au classeur ou être dans un autre classeur.
- mais aussi à la référence directe à une cellule ou plage par son adresse qui a été supprimée.
Supprimer les noms, c'est la moitié du travail de nettoyage, il faut ensuite analyser les cellules en erreur et les corriger.

Le code ci-dessous, par rapport au précédent affiche le nom en plus de la référence à. Il permet de faire le lien à postériori avec les cellules qui s'y réfèrent dans leur formule. La recherche directe dans les formules par le nom ne me semble pas pertinente, une formule peut contenir le nom sans que ce soit une référence.

VB:

Sub Repérage3_dependants_noms()

Dim WS As Worksheet
Dim nm As Name
Dim i As Integer
Dim RangeRef As Range
Dim RangeRefPrem As Range

    ' Balayage des Noms du Gestionnaire des noms
    i = 1
    For Each nm In ActiveWorkbook.Names
        If InStr(nm.RefersTo, "#REF") Then
            RechRef = nm.RefersTo
            i = i + 1
            ActiveSheet.Cells(i, 1) = "'" & nm.Name
            ActiveSheet.Cells(i, 2) = "'" & nm.RefersTo
        End If
    Next nm
    
    ' Balayage des feuilles
    i = 1
    For Each WS In ThisWorkbook.Worksheets
        If WS.Name <> ActiveSheet.Name Then
            For Each RangeRef In WS.UsedRange
                If IsError(RangeRef) Then
                    If CVErr(RangeRef) = CVErr(xlErrRef) Then
                        i = i + 1
                        With ActiveSheet
                            .Cells(i, 3) = WS.Name
                            .Cells(i, 4) = RangeRef.Address
                            .Cells(i, 5) = "'" & RangeRef.Formula
                        End With
                    End If
                End If
            Next RangeRef
        End If
    Next WS

End Sub

Dranreb · 6 Février 2024

Bonjour.

J'aurais écris ainsi la partie centrale de votre code :

VB:

         On Error Resume Next
         Set RangeRef = WS.UsedRange.SpecialCells(xlCellTypeFormulas, 16)
         If Err = 0 Then
            For Each Cel In RangeRef
               If CLng(Cel.Value) = xlErrRef Then
                  I = I + 1
                  With ActiveSheet
                     .Cells(I, 3) = WS.Name
                     .Cells(I, 4) = Cel.Address
                     .Cells(I, 5) = "'" & Cel.Formula
                     End With
                  End If
               Next Cel

FRYS · 6 Février 2024

Merci beaucoup pour vos retours

Si je comprends bien, ca devrait bien marcher pour les noms qui se réfèrent à un #REF!
(même si je crain qu'il y ait un décallage qui se crée entre les deux boucles)

En revanche (je n'ai pas été assez explicite là dessus) je souhaite néttoyer aussi des noms faisant référence à de vielles liaisons.

je peux retrouver les noms concernés avec InStr(nm.RefersTo, ":\")

là ou c'est un peu tricky c'est que les cellules qui les utilisent restent bloquées sur la dernière valeur connue. on peut donc facilement passer à coter sans s'en rendre compte

Merci d'avance

crocrocro · 6 Février 2024

Les 2 boucles sont indépendantes.
Comme je l'ai dit dans ma dernière réponse,
en colonne A et B la liste des plages nommées (donc du gestionnaire des noms) en erreur
en colonne C, D et E, les cellules avec une référence incorrecte (qui n'est pas nécessairement une plage nommée). Chaque cellule en erreur de la 2ème boucle est à traiter au cas par cas. Mais là, ce sont plus des réflexions fonctionnelles qui sont à résoudre.

Microsoft 365 Retrouver les dépendants de cellules nommées

FRYS

XLDnaute Nouveau

crocrocro

XLDnaute Occasionnel

TooFatBoy

XLDnaute Barbatruc

crocrocro

XLDnaute Occasionnel

Dranreb

XLDnaute Barbatruc

FRYS

XLDnaute Nouveau

crocrocro

XLDnaute Occasionnel

Discussions similaires