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!

Teresa, try this macro by John Walkenbach
It will list formulas , cell address, and valves in a new worksheet for the
sheet you are on.

Sub ListFormulas()
'from John Walkenbach
Dim FormulaCells As Range, Cell As Range
Dim FormulaSheet As Worksheet
Dim Row As Integer

' 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 or the sheet is protected."
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

--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

"Teresa Hoffman" wrote in message
...
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!

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!

Paul,

Thank you! This macro works great. However, I have a another question
for you. How do I modify this macro if I want to display the contents
of all cells? I want to keep the output format of the macro (i.e. same
three columns in a new worksheet) but I want the macro to display the
contents of all cells, not just cells with formulas. I will attempt to
figure this out in the meantime but please let me know if you have any
suggestions. Thanks again!

Teresa


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!