Printing formulas in worksheet

  • Thread starter Thread starter 05_CHASBRO
  • Start date Start date
0

05_CHASBRO

I have been able to display & print the results (values) of the formulas that
I have developed, but what I really need is to be able to display & print the
formulas that spawned these results. Thus far I have not been able to print
the actual formulas in the worksheets and I would greatly appreciate any
advise that will help me accomplish this.
 
Press Ctrl + ~ to have the formula auditing mode..and then try printing

If this post helps click Yes
 
Thanks, Jacob! I really appreciate your expedition response to my "post".
While the ctrl+ ~ displays the formula in the the worksheet, it will not
print the formulas directly from the worksheet; HOWEVER, i am able to copy
the displayed formulas & paste them into my Word Processor (2007) & then
copy/paste them into an Excel work sheet & successfully print them. So I
will use the this method to print my formulas until or unless I am informed
of a more direct method of printing them. Nevertheless, your prompt & clear
guidance have helped me achieve my primary goal ("printing formulas in a
worksheet"!). I am most appreciative for your help!
 
This macro will do a nice job of formula printing.

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
 
Back
Top