Re: Formulae Auditing

"Kevin Spiller" wrote...
>Thanks for that. But that is not what I am after. Formula auditing only
>allows you to drill down one level at a time. What I am after is a
>multi-level report that shows all references from a single cell and all
>references below each subsequent formulae.This would hopefully be an
>indented report on a pyramid basis.

Modify to taste.


Sub listdep()
Const BFN As String = "listdep.txt"
Dim fd As Long, ws As Worksheet, c As Range, n As Long
fd = FreeFile
Open Environ("TEMP") & "\" & BFN For Output As #fd
Print #fd, "File: " & ActiveWorkbook.FullName
Print #fd, String(50, "=")
Print #fd,
n = 0
For Each ws In ActiveWorkbook.Worksheets
Print #fd, "Worksheet: " & ws.Name
Print #fd, String(50, "-")
For Each c In ws.UsedRange
If c.HasFormula Then
Print #fd, c.Address(0, 0, xlA1, 0)
listdepdump fd, c, n
Print #fd,
End If
Next c
Print #fd, String(50, "_")
Print #fd,
Next ws
Close #fd
End Sub

Private Sub listdepdump(fd As Long, c As Range, ByVal n As Long)
Dim dc As Range, ci As Range, cj As Range, hf As Long
n = n + 1
Print #fd, String(n, Chr(9));
Print #fd, c.Address(0, 0, xlA1, 1)
Set dc = c.Precedents
For Each ci In dc.Areas
hf = 0
For Each cj In ci
If cj.HasFormula Then
If InStr(2, cj.Formula, "INDIRECT") <= 0 Then
hf = 1
Else
hf = -1
Exit For
End If
End If
Next cj
If hf = 1 Then
listdepdump fd, ci, n
ElseIf hf = -1 Then
Print #fd, String(n, Chr(9));
Print #fd, "This range contains indirect references. YOYO!"
End If
Next ci
End Sub

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.