Macro for displaying formula

[Craig]

Hi,

Any code appreciated...I want to be able to run a dynamic function that
would display the formula as text beside a cell that contains a formula...not
every formula , but only those I run the function on it..

for example, in cell B20 if there is a formula =sum(B1:B19)...that displays
the total of those cells above it... ...then just beside it in cell C20 if I
type a function like = formula display (b20)...it would display =sum(B1:B19)
and if I insert 5 or so more rows up top with 5 additional value cells in
them, which would change the result in cell B24, the function in C25 would
change automatically as well to display the new formula as text (such as
=sum(B2:B24)
thank you!!!

Craig

 

[Jim Cone]

Instead, how about Ctrl + ~
--
Jim Cone
Portland, Oregon USA



"Craig" <[email protected]>
wrote in message
Hi,
Any code appreciated...I want to be able to run a dynamic function that
would display the formula as text beside a cell that contains a formula...not
every formula , but only those I run the function on it..

for example, in cell B20 if there is a formula =sum(B1:B19)...that displays
the total of those cells above it... ...then just beside it in cell C20 if I
type a function like = formula display (b20)...it would display =sum(B1:B19)
and if I insert 5 or so more rows up top with 5 additional value cells in
them, which would change the result in cell B24, the function in C25 would
change automatically as well to display the new formula as text (such as
=sum(B2:B24)
thank you!!!
Craig

 

[Harald Staff]

Hi Craig

In a standard module (not sheet module):

Public Function CellFormula(Rng As Range) As String
CellFormula = Rng(1).Address(False, False, Application.ReferenceStyle) _
& " formula: " & Rng(1).FormulaLocal
End Function

In a worksheet:

=CellFormula(A1)

HTH. Best wishes Harald

 

[CurlyDave]

Another option would be to place the formulas in the Cell Comments
using vba...

With Range("B20")

.ClearComments
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Range("B20").Formula
.Comment.Shape.TextFrame.AutoSize = True

End With