Lister cellules antécédents d'une formule

Bonjour à tous,

Imaginons que dans la case A1, j'ai la formule : =B1+C1

Et dans la formule B1, j'ai la formule : =B2+C2

Et dans la formule B2, j'ai la formule : =Z49-X12

etc pour un grand nombre de cellules....

J'ai besoin d'une solution (macro, formule, excel add-on ???) qui me permette de lister toutes les cellules qui aboutissent au calcul de la valeur contenu dans A1.

Dans l'exemple ci-dessus, le résultat attendu est donc une liste de cellules :

B1

C1

B2

C2

Z49

X12

etc...

Evidemment, cela me va très bien aussi si le résultat apparaît différemment, comme une liste concaténée par exemple :

B1,C1,B2,C2,Z49,X12,etc...

S'il est possible d'instaurer un ordre logique, c'est encore mieux.

Par exemple, s'il est possible de placer entre parenthèses les cellules antécédentes :

B1(B2(Z49,X12),C2),C1,etc...

Certaines cellules peuvent figurer sur d'autres feuillets, donc elles peuvent être listées en incluant leur feuillet d'appartenance (ex : Feuil2!A1).

En revanche, toutes les cellules appartiennent au même fichier.

Savez-vous si une telle solution existe déjà quelque part?

Ou est-il possible de coder une telle macro?

Bonjour,

C'est natif sur Excel après t'être mis sur ta cellule :

antecents

Tu as aussi l'évaluation de formule (sur le même écran) qui est très utile qui t'indique la cellule et le résultat ça chaque étape mais reste limité aux références placées dans ta formule et ne repère pas les antécédents. Il est nécessaire d'assembler tes formules une a une c'est beaucoup plus simple je trouve.

Bonne journée

Bonjour Ergotamine,

Tout d'abord je précise que j'utilise Excel 2007.

Ensuite à ma connaissance, "repérer les antécédents" permet uniquement d'être redirigé rapidement vers une cellule "antécédent direct" de la cellule initiale, n'est-ce pas?

Ce qui me manque, c'est d'une part la possibilité de lister les cellules (pas seulement me diriger vers celle-ci), et d'autre part de lister les cellules antécédentes des "antécédents directs" jusqu'à remonter tout l'arbre "généalogique" de la cellule initiale.

Bonjour,

A priori cette fonction existe déjà sous EXCEL 2007. Il suffit de cliquer autant de fois dessus qu'il y a de niveau pour y voir tous les antécédents de chaque cellule.

Dis moi si cela fonctionne.

Pour un listing textuel je ne saurais t'aider plus, cela dépasse mes compétences désolé.

Bonne journée.

Bonjour,

l'écueil est sur les ref dans des autres onglets.
A-priori, ici il y est arrivé : https://colinlegg.wordpress.com/2014/01/14/vba-determine-all-precedent-cells-a-nice-example-of-recur...
eric

Bonjour nicopat, Salut Ergotamine,

Voici un essai avec une macro (non testée) à placer dans un module normal :

Sub lister()

dim t()

set rdep = range("A1").precedents
for each cell in rdep
    n = n + 1
    redim preserve t(1 to n)
    t(n) = cell.address
next cell

range("Z1").resize(n, 1) = application.transpose(t)

end sub

On cherche les précédents de A1 et la liste est restituée en colonne Z dans le code.

Edit : Et salut Eriiic !

Cdlt,

Bonjour 3GB,

Et merci de ton aide!

Je viens d'essayer ta macro dans un fichier lambda et lorsque je la lance, j'ai un bug.

La ligne en jaune est la suivante :

Set rdep = Range("A1").Precedents

Bonjour Eriic,

Merci, je vais essayer de comprendre ce que propose cette page en anglais

Lorsque j'essaye son premier code, je ne vois pas de message d'erreur, mais il ne se passe rien dans mon fichier excel :

Sub Test()

    Dim rngToCheck As Range
    Dim rngPrecedents As Range
    Dim rngPrecedent As Range

    Set rngToCheck = Range("A1")

    On Error Resume Next
    Set rngPrecedents = rngToCheck.Precedents
    On Error GoTo 0

    If rngPrecedents Is Nothing Then
        Debug.Print rngToCheck.Address(External:=True) & _
                    " has no precedents"
    Else
        For Each rngPrecedent In rngPrecedents
            Debug.Print rngPrecedent.Address(External:=True)
        Next rngPrecedent
    End If

End Sub

Et lorsque je teste sa 2ème macro :

Sub Test2()

    Dim rngToCheck As Range
    Dim dicAllPrecedents As Object
    Dim i As Long

    Set rngToCheck = Sheet1.Range("A1")
    Set dicAllPrecedents = GetAllPrecedents(rngToCheck)

    Debug.Print "==="

    If dicAllPrecedents.Count = 0 Then
        Debug.Print rngToCheck.Address(External:=True); " has no precedent cells."
    Else
        For i = LBound(dicAllPrecedents.Keys) To UBound(dicAllPrecedents.Keys)
            Debug.Print "[ Level:"; dicAllPrecedents.Items()(i); "]";
            Debug.Print "[ Address: "; dicAllPrecedents.Keys()(i); " ]"
        Next i
    End If
    Debug.Print "==="

End Sub

'won't navigate through precedents in closed workbooks
'won't navigate through precedents in protected worksheets
'won't identify precedents on hidden sheets
Public Function GetAllPrecedents(ByRef rngToCheck As Range) As Object

    Const lngTOP_LEVEL As Long = 1
    Dim dicAllPrecedents As Object
    Dim strKey As String

    Set dicAllPrecedents = CreateObject("Scripting.Dictionary")

    Application.ScreenUpdating = False

    GetPrecedents rngToCheck, dicAllPrecedents, lngTOP_LEVEL
    Set GetAllPrecedents = dicAllPrecedents

    Application.ScreenUpdating = True

End Function

Private Sub GetPrecedents(ByRef rngToCheck As Range, ByRef dicAllPrecedents As Object, ByVal lngLevel As Long)

    Dim rngCell As Range
    Dim rngFormulas As Range

    If Not rngToCheck.Worksheet.ProtectContents Then
        If rngToCheck.Cells.CountLarge > 1 Then   'Change to .Count in XL 2003 or earlier
            On Error Resume Next
            Set rngFormulas = rngToCheck.SpecialCells(xlCellTypeFormulas)
            On Error GoTo 0
        Else
            If rngToCheck.HasFormula Then Set rngFormulas = rngToCheck
        End If

        If Not rngFormulas Is Nothing Then
            For Each rngCell In rngFormulas.Cells
                GetCellPrecedents rngCell, dicAllPrecedents, lngLevel
            Next rngCell
            rngFormulas.Worksheet.ClearArrows
        End If
    End If

End Sub

Private Sub GetCellPrecedents(ByRef rngCell As Range, ByRef dicAllPrecedents As Object, ByVal lngLevel As Long)

    Dim lngArrow As Long
    Dim lngLink As Long
    Dim blnNewArrow As Boolean
    Dim strPrecedentAddress As String
    Dim rngPrecedentRange As Range

    Do
        lngArrow = lngArrow + 1
        blnNewArrow = True
        lngLink = 0

        Do
            lngLink = lngLink + 1

            rngCell.ShowPrecedents

            On Error Resume Next
            Set rngPrecedentRange = rngCell.NavigateArrow(True, lngArrow, lngLink)

            If Err.Number <> 0 Then
                Exit Do
            End If

            On Error GoTo 0
            strPrecedentAddress = rngPrecedentRange.Address(False, False, xlA1, True)

            If strPrecedentAddress = rngCell.Address(False, False, xlA1, True) Then
                Exit Do
            Else

                blnNewArrow = False

                If Not dicAllPrecedents.Exists(strPrecedentAddress) Then
                    dicAllPrecedents.Add strPrecedentAddress, lngLevel
                    GetPrecedents rngPrecedentRange, dicAllPrecedents, lngLevel + 1
                End If
            End If
        Loop

        If blnNewArrow Then Exit Do
    Loop

End Sub

la cellule A1 est sélectionnée, mais c'est tout : je ne vois rien d'autre se produire.

Bonjour,

Le plus simple est de joindre un fichier type afin qu'on puisse tester les codes ;)

Bonne journée.

Re,

Ici, j'ai mis A1 sans pare-feu car A1 est supposée avoir des précédents. Si le but est de tester également des cellules pour lesquelles il y a une incertitude, la macro proposée par eriiic est plus adaptée ou celle-ci :

Sub lister()

dim t()

on error goto fin
set rprec = range("A1").precedents
for each cell in rprec.cells
    n = n + 1
    redim preserve t(1 to n)
    t(n) = cell.address
next cell

range("Z1").resize(n, 1) = application.transpose(t)
exit sub
fin:
msgbox "la cellule n'a pas d'antécédent"
end sub

Il faut un minimum adapter les range...

Cdlt,

Bonjour 3GB,

Quand tu dis pare-feu c'est IF pas d'antécédents ?

Merci !

Bonne journée !

Ma macro est testée et fonctionne pour les antécédents sur la feuille de la cellule examinée (ils sont renvoyés en Z1 !). Je pense qu'il faut rajouter ce fameux paramètre external. Je reviens éditer mon commentaire...

Edit : Oui, exactement, je n'ai pas mis de gestion d'erreur sur le premier code. Mais lorsqu'une cellule n'a pas d'antécédent, sa propriété precedents ne renvoie pas nothing mais <pas de cellules correspondantes>, ce qui crée fatalement une erreur, d'où l'utilisation de l'instruction on error plutôt que de if A1.precedents is nothing

Bonjour,

Et si la référence se trouve sur autre feuille, comment est-elle gérée ? Voir pire, sur un autre classeur ?

Bonne journée !

Dans mon cas, il n'y a pas de cellule antécédente à aucun niveau qui ne soit dans un autre classeur.

Pour répondre à ta question sur le fonds, le format d'output proposé par https://colinlegg.wordpress.com/2014/01/14/vba-determine-all-precedent-cells-a-nice-example-of-recur... semble convenable :

[Book1]Sheet1!$B$2

PS : on ne peut plus citer les posts initiaux quand on répond ?

Dans le fichier test que j'ai uploadé précédemment, j'ai placé les macros proposées par 3GB et Eriiic.

De mon côté, aucun ne fonctionne (celle de 3GB ne liste que les cellules du même feuillet que la cellule analysée), mais si vous voulez tester... D'après le lien suggéré par Eriiic, je crois que leur macro est sensée fonctionner

Bonjour nicopat,

Tu ne peux citer les réponses qu'à partir d'un certains nombres de messages (500 il me semble).

Bonne journée.

Oui, en effet, on ne peut obtenir les précédents que sur la feuille active apparemment. Donc ma solution fonctionne (mais pas complètement).

Sinon, il faut utiliser la méthode .navigatearrow (que je ne connais pas) comme sur le lien. Si elle a été postée, c'est qu'elle fonctionne certainement.

En utilisant le fichier test que j'ai uploadé précédemment, pourrais-tu me dire si la macro du lien fonctionne sur ton ordi stp?

Sur le mien, il ne se passe rien quand je lance test() ou test2().

Ni message d'erreur, ni résultat. Avec test2(), la cellule A1 est sélectionnée, mais c'est tout.

C'est parce que les résultats sont renvoyés dans la fenêtre d'exécution.

Peux-tu essayer :

Sub Test2()

    Dim rngToCheck As Range
    Dim dicAllPrecedents As Object
    Dim i As Long

    Set rngToCheck = Sheet1.Range("A1")
    Set dicAllPrecedents = GetAllPrecedents(rngToCheck)

    Debug.Print "==="

    If dicAllPrecedents.Count = 0 Then
        Debug.Print rngToCheck.Address(External:=True); " has no precedent cells."
    Else
        range("Z1").resize(dicAllPrecedents.Count, 1) = application.transpose(dicAllPrecedents.keys)
    end if

End Sub

Cdlt,

Rechercher des sujets similaires à "lister antecedents formule"