CODEWITHSUNDEEP
excelvbaexcel-formula
ali kiani
ali kiani

Reputation: 887

How to extract the cells used in a formula?

I want to know which cells are engaged with formula in VBA.

For example, suppose E12 has this formula =AVERAGE(D12:D17) and I want extract the list of cells D12...D17 from this cell, how can I achieve that?

Dim fCell Az Range
Set fCell = Range("E12")
' know how to extract list

Upvotes: 2

Views: 69

Answers (1)

41686d6564
41686d6564

Reputation: 19641

You're looking for the DirectPrecedents property. Try something like:

Dim r As Range
For Each r In ActiveSheet.Range("E12").DirectPrecedents
    Debug.Print r.Address(False, False) & " = " & r.Value
Next

Output:

D12 = 5
D13 = 10
D14 = 15
D15 = 14
D16 = 9
D17 = 20

Upvotes: 3

Related Questions