"Strip" Cell Formula
 

Anybody can let me have a macro which would list all individual links
of a "trace precedent box" next to (on the right) my cell containing
the formula.

Simple E.g
cell A1 = sheet!!A1 + sheet2!b2

Result:
Cell B1 would contain "= sheet!!A1"
Cell C1 would contain "= + sheet2!b2"

The macro has to be flexible & applicable to any complex cell formula

Thxs
Al
(I'm sure Tom or someoneelse can help me on this )

"Strip" Cell Formula
 

Why not just show the formula itself in the cell to the right? The VBA
function to do that has been posted in the last few days. Search Google, Excel
forums, for the text CellFormula.

On 5 Oct 2005 13:09:48 -0700, "al" wrote:

Anybody can let me have a macro which would list all individual links
of a "trace precedent box" next to (on the right) my cell containing
the formula.

Simple E.g
cell A1 = sheet!!A1 + sheet2!b2

Result:
Cell B1 would contain "= sheet!!A1"
Cell C1 would contain "= + sheet2!b2"

The macro has to be flexible & applicable to any complex cell formula

Thxs
Al
(I'm sure Tom or someoneelse can help me on this )

"Strip" Cell Formula
 

Hi Al

If you want it in one cell you can copy this function in module and use this in your worksheet

=GETFORMULA(A1)

Function GETFORMULA(cell As Range) As String
'=GETFORMULA(A1) in a sheet for example
'Various but especially Dave Peterson and Dave McRitchie
Dim myFormula As String
Dim myAddress As String
GETFORMULA = ""
With cell.Cells(1)
'Grab Formulas first
If .HasFormula Then
'Note that we have R1C1 or A1 reference possibilities
If Application.ReferenceStyle = xlA1 Then
myFormula = .Formula
myAddress = cell.Address(0, 0, xlA1)
Else
myFormula = .FormulaR1C1
myAddress = cell.Address(, , xlR1C1)
End If
'Insert prefix "{" and post fix "}" for array formulas
If cell.HasArray Then
GETFORMULA = myAddress & ": {=" & _
Mid(myFormula, 2, Len(myFormula)) & "}"
Else
GETFORMULA = myAddress & ": " & myFormula
End If
Else
'Bog standard data entries
If Application.ReferenceStyle = xlA1 Then
myFormula = cell.Formula
myAddress = cell.Address(0, 0, xlA1)
Else
myFormula = cell.FormulaR1C1
myAddress = cell.Address(, , xlR1C1)
End If
GETFORMULA = myAddress & ": " & myFormula
End If
End With
End Function

--
Regards Ron de Bruin
http://www.rondebruin.nl


"al" wrote in message oups.com...
Anybody can let me have a macro which would list all individual links
of a "trace precedent box" next to (on the right) my cell containing
the formula.

Simple E.g
cell A1 = sheet!!A1 + sheet2!b2

Result:
Cell B1 would contain "= sheet!!A1"
Cell C1 would contain "= + sheet2!b2"

The macro has to be flexible & applicable to any complex cell formula

Thxs
Al
(I'm sure Tom or someoneelse can help me on this )