How to print formula and value

[Nicole]

Is there a way to print not only the formula (Tools-Options-View-Formula) but
also the value on the same print so that I can see both the formula and the
result?

Kind regards,

Nicole

[Sri]

check the article below
http://funwithexcel.blogspot.com/2009/04/display-and-print-formulas-and-values.html
hope this helps.

[Gord Dibben]

Sub ListFormulas()
'from John Walkenbach
Dim FormulaCells As Range, Cell As Range
Dim FormulaSheet As Worksheet
Dim Row As Integer
Dim ws As Worksheet
' Create a Range object for all formula cells
On Error Resume Next
Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)

' Exit if no formulas are found
If FormulaCells Is Nothing Then
MsgBox "No Formulas."
Exit Sub
End If

' Add a new worksheet
Application.ScreenUpdating = False
Set FormulaSheet = ActiveWorkbook.Worksheets.Add
FormulaSheet.Name = "Formulas in " & FormulaCells.Parent.Name

' Set up the column headings
With FormulaSheet
Range("A1") = "Address"
Range("B1") = "Formula"
Range("C1") = "Value"
Range("A1:C1").Font.Bold = True
End With

' Process each formula
Row = 2
For Each Cell In FormulaCells
Application.StatusBar = Format((Row - 1) / FormulaCells.Count, "0%")
With FormulaSheet
Cells(Row, 1) = Cell.Address _
(RowAbsolute:=False, ColumnAbsolute:=False)
Cells(Row, 2) = " " & Cell.Formula
Cells(Row, 3) = Cell.Value
Row = Row + 1
End With
Next Cell

' Adjust column widths
FormulaSheet.Columns("A:C").Cells.WrapText = True ''AutoFit
Application.StatusBar = False
End Sub

Gord Dibben MS Excel MVP

[Nicole]

Thank you!

[Nicole]

Many thanks!

[macropod]

Hi Nicole,

Here's another way:

The following macro adds the formulae to the comments for each selected cell, or even the whole worksheet, and displays the comments
in an appropriately-sized box.
Sub AddFormulasToComments()
Application.ScreenUpdating = False
Dim CommentRange As Range, TargetCell As Range
'skip over errors caused by trying to delete comments in cells with no comments
On Error Resume Next
'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
'If the cell contains a formula, turn it into a comment.
For Each TargetCell In CommentRange
With TargetCell
'check whether the cell has a formula
If Left(.Formula, 1) = "=" Then
'delete any existing comment
.Comment.Delete
'add a new comment
.AddComment
'copy the formula into the comment box
.Comment.Text Text:=.Formula
'display the comment
.Comment.Visible = True
End If
End With
Next
MsgBox " To print the comments, choose" & vbCrLf & " File|Page Setup|Sheet|Comments," & vbCrLf & "then choose the required print
option.", vbOKOnly
Application.ScreenUpdating = True
End Sub

Do note that any existing comments in the updated cells are replaced by the formulae.

By default, worksheet comments don’t print. The macro concludes with a message box telling you how to change that.

--
Cheers
macropod
[Microsoft MVP - Word]

"Nicole" <Nic...@discussions.microsoft.com> wrote in message news:653F3A88-924B-4D77...@microsoft.com...