Macro to find values of each cell referenced in a formula

Posted by Kent Felgner on December 14, 2001 4:58 AM

Any ideas on how to dissect a formula and retrieve the individual referenced cell values.

For example, if Sheet1 is a summary sheet containing hundreds of summarized cells and Sheet2 has the detail (i.e. Sheet1!A1 = Sheet2!B1 + Sheet2!B20), how can I create an outline (i.e. Sheet3!A1 = Sheet1!A1, Sheet3!B2 = first cell referenced in formula, Sheet3!B3 = second cell in formula, Sheet3!B4 = third cell referenced if applicable....)

I can use .hasformula to find the cells to dissect but how do I take apart and find the values of each cell in the formula.

Posted by Strato on December 14, 2001 5:49 AM

Based on your example, try this :-

Sub Dissect_Formula()
Sheets("Sheet1").Rows(1).Insert
With Sheets("Sheet1").Range("A1")
.Value = Chr(39) & Range("A2").Formula
.Replace What:="+", Replacement:=" ", LookAt:=xlPart
.Replace What:="=", Replacement:="", LookAt:=xlPart
.Value = Range("A1").Value
.TextToColumns Destination:=.Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(9, 1))
End With
With Sheets("Sheet3")
.Range("A1").Value = "Sheet1!A1"
.Range("A2:A256") = Application.Transpose(Sheets("Sheet1").Range("A1:IU1"))
End With
Sheets("Sheet1").Rows(1).Delete
End Sub