Display formula on the cell

  • Thread starter Thread starter journey
  • Start date Start date
J

journey

Hi

I wanted to display the formulas permanently besides a calculated
value. When I go to Tools>Options>view, click on the window options
Formulas, I achieve the result however the value with the formulas in
it also turn to formulas which is not the result i wanted.

Could someone pls advice.

Thanks
 
On fly:

Create an UDF
Public Function ShowFormula(MyRange As Range)
ShowFormula = MyRange.Formula
End Function

P.e. into cell A1 enter the some formula
Into cell B1 enter the formula
=ShowFormula(A1)


Arvi Laanemets
 
Hi journey,

You could add the formula to the cell comments and display those. Here's a macro to add the formulae to the cell comments for the
selected range:

Sub AddFormulasToComments()
Application.ScreenUpdating = False
Dim CommentRange As Range, TargetCell As Range, Cmt As Comment
'If the whole worksheet is selected, limit action to the used range.
If Selection.Address = Cells.Address Then
Set CommentRange = Range(ActiveSheet.UsedRange.Address)
Else
Set CommentRange = Range(Selection.Address)
End If
'Delete comments from cells containing formulae.
For Each Cmt In ActiveSheet.Comments
For Each TargetCell In CommentRange
If TargetCell.Address = Cmt.Parent.Address Then
Cmt.Delete
Exit For
End If
Next
Next
'If the cell contains a formula, turn the formula into a comment.
For Each TargetCell In CommentRange
With TargetCell
If Left(.Formula, 1) = "=" Then
'add a new comment
.AddComment
'copy the formula into the comment box
.Comment.Text Text:=.Formula
'display the comment
.Comment.Visible = True
'autosize to fit
.Comment.Shape.TextFrame.AutoSize = True
'position the comment adjacent to its cell
If .Column < ActiveSheet.Columns.Count - 1 Then .Comment.Shape.IncrementLeft -11.25
If .Row > 1 Then .Comment.Shape.IncrementTop 8.25
End If
End With
Next
Application.ScreenUpdating = True
MsgBox " To print the comments, choose" & vbCrLf & _
" File|Page Setup|Sheet|Comments," & vbCrLf & _
"then choose the required print option.", vbOKOnly
End Sub
 
Back
Top