Try this:
Sub ListDependents()
Dim wks As Worksheet
Dim rngFormulas As Range, rngCell As Range
Dim objDict As Object
Dim varDeps As Variant, varItem As Variant
Dim lngRow As Long, x As Long, y As Long
Dim wksOut As Worksheet
Application.ScreenUpdating = False
Set rngFormulas = Selection
' this Dictionary will hold the addresses
Set objDict = CreateObject("Scripting.Dictionary")
If Not rngFormulas Is Nothing Then
For Each rngCell In rngFormulas
ListCellDependents rngCell, objDict
Next rngCell
Set rngFormulas = Nothing
End If
Set wksOut = Worksheets.Add
wksOut.Range("A1:B1").Value = Array("Original Cell", "Dependents")
lngRow = 2
For Each varItem In objDict.Keys
varDeps = Split(objDict.Item(varItem), "|")
For y = LBound(varDeps) To UBound(varDeps)
wksOut.Cells(lngRow, "A").Value = varItem
wksOut.Cells(lngRow, "B").Value = varDeps(y)
lngRow = lngRow + 1
Next y
Next varItem
Application.ScreenUpdating = True
End Sub
Sub ListCellDependents(rngCheck As Range, dict As Object)
Dim lngSheetCounter As Long, lngRefCounter As Long
Dim strKey As String, strAddy As String
strKey = "'" & rngCheck.Parent.Name & "'!" & rngCheck.address(0, 0)
lngSheetCounter = 1
On Error Resume Next
With rngCheck
.ShowDependents False
Do
lngRefCounter = 1
Do
.NavigateArrow False, lngSheetCounter, lngRefCounter
strAddy = "'" & Selection.Parent.Name & "'!" & Selection.address(0, 0)
If Err.Number = 0 Then
If strAddy = strKey Then
rngCheck.ShowDependents True
Exit Sub
Else
If dict.Exists(strKey) Then
dict(strKey) = dict(strKey) & "|" & strAddy
Else
dict(strKey) = strAddy
End If
End If
lngRefCounter = lngRefCounter + 1
Else
Err.Clear
Exit Do
End If
Loop
lngSheetCounter = lngSheetCounter + 1
Loop
End With
End Sub
1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: 43: 44: 45: 46: 47: 48: 49: 50: 51: 52: 53: 54: 55: 56: 57: 58: 59: 60: 61: 62: 63: 64: 65: 66:





by: roryaPosted on 2009-09-07 at 00:28:42ID: 25273254
2 is easy - just change line 15 to:
a = cell.DirectDependents.Addr ess
1 will take a little more effort I think. Will be back!