Cell Precedents

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

Precs1

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:

Precs2

One thought on “Cell Precedents

  1. 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

Leave a Reply

Your email address will not be published. Required fields are marked *