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.
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) & ","
'Remove the last comma
sPrecList = Left(sPrecList, Len(sPrecList) - 1)
sPrecList = "No Precedents Found"
By looping through the Areas collection, the sub returns a more readable list of range addresses. Here’s the resulting message box: