Teresa

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").AutoFit
Application.StatusBar = False
End Sub

Note the options to display addresses and values if desired. Just delete the
' from the lines of code.

Gord Dibben Excel MVP - XL97 SR2 & XL2002

On Thu, 24 Jul 2003 09:18:41 -0500, "Teresa Hoffman"
wrote:

I am attempting to write a macro that will display the cell formulas in the
Active Sheet and then print this sheet. I would like to have the printed
sheet display one cell formula per line rather than display the worksheet as
is (in row/column form) with cell formulas displayed. The macro in our
Quattro Pro spreadsheets prints out one cell formula per line but I don't
know if this is possible in Excel.

A related issue regarding the printing of cell formulas is that cells that
contain large formulas do not display the entire formula. It only displays
the portion within the default width of the column. I need to display the
entire formula, whether it is one formula per line or not.

I welcome any suggestions on how to accomplish this objective. Thank you!