Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 ) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 ) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Result is "V6", need Excel to use cell "V6", not the resul | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
How do you "strip" a hyperlink | Excel Worksheet Functions | |||
Help!!! Enter "7" in a cell and Excel changes the "7" to "11" immediately!!! | Excel Discussion (Misc queries) | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) |