Precedents are ranges on which the value of another cell relies. If, in cell B1, you have the formula “=A1” then A1 is a precedent of B1. The Range object has a Precedents property that returns another Range object that includes all the precedents for that cell.
Take this formula in A8, for example
To list all the precedents for this formula, you can loop through all the cells in the range returned by the Precedents property. However, it may be more useful to loop through the Areas of the range, as in the example below.
Sub ListPrecedents()
Dim rStart As Range
Dim rPrecCells As Range
Dim cell As Range
Dim sPrecList As String
Set rStart = Sheet1.Range("A8")
'If there are no precedents, and error will occur
On Error Resume Next
Set rPrecCells = rStart.Precedents
On Error GoTo 0
'If there are precedents
If Not rPrecCells Is Nothing Then
'Loop through the Areas collection and string
'together the addresses
For Each cell In rStart.Precedents.Areas
sPrecList = sPrecList & cell.Address(0, 0) & ","
Next cell
'Remove the last comma
sPrecList = Left(sPrecList, Len(sPrecList) - 1)
Else
sPrecList = "No Precedents Found"
End If
MsgBox sPrecList
End Sub
By looping through the Areas collection, the sub returns a more readable list of range addresses. Here’s the resulting message box:
Hello!
I tried to use this sub but as I discovered the Precedents object is not available when the cell references to another worksheet(s) or workbook(s). When the cell contains mixed (in-sheet and out-sheet) references the Precedents object is available but contains only the in-sheet references. Is there any way to find all the precedents in this case?
Thanx.
Tamas