Printing formulas

[royhern]

Hope someone can help...i have a workbook with several worksheets. i know that by pressing the CRl key and ~ key i can see part of all formulas on the sheet, but what i want to accomplish is to create a report of all formulas in the workbook so that i can print. The report should print by given the sheet name, cell, and the whole formula versus just part of it.

Any ideas..Roy

 

[AC]

This will list formulas , cell address, and valves, for a sheet, in a new worksheet,
I think I got it off this board but don’t know who to give the credit to. As is you would have to do each sheet but maybe it could be modified to do the whole workbook at one time

Sub ListFormulas()
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

 

[XL-Dennis]

Roy,

Following code will do what You want:

<PRE>
<FONT color=blue>Sub </FONT>ListFormulas()

<FONT color=blue>Dim </FONT>FormulaCells<FONT color=blue> As</FONT> Range, Cell<FONT color=blue> As</FONT> Range

<FONT color=blue>Dim </FONT>FormulaSheet<FONT color=blue> As</FONT> Worksheet

<FONT color=blue>Dim </FONT>wsSheet<FONT color=blue> As</FONT> Worksheet

<FONT color=blue>Dim </FONT>stName<FONT color=blue> As</FONT><FONT color=blue> String</FONT>

<FONT color=#ff0000>'Revised
</FONT>
<FONT color=blue>Dim </FONT>Row<FONT color=blue> As</FONT><FONT color=blue> Long</FONT>



stName = "Formulas in " & ActiveWorkbook.Name



<FONT color=blue>With </FONT>Application

.ScreenUpdating =<FONT color=blue> False</FONT>

.DisplayAlerts =<FONT color=blue> False</FONT>

<FONT color=blue>End With</FONT>



<FONT color=blue>On Error</FONT> <FONT color=blue>Resume </FONT>Next

ActiveWorkbook.Worksheets(stName).Delete

<FONT color=blue>On Error</FONT> <FONT color=blue>GoTo</FONT> 0



<FONT color=blue>Set </FONT>FormulaSheet = ActiveWorkbook.Worksheets.Add

<FONT color=#ff0000>'Revised
</FONT>
FormulaSheet.Name = stName



<FONT color=blue>With </FONT>FormulaSheet

.Range("A1").Value = "Worksheet Name"

.Range("B1").Value = "Address"

.Range("C1").Value = "Formula"

.Range("D1").Value = "Value"

.Range("A1:D1").Font.Bold =<FONT color=blue> True</FONT>

<FONT color=blue>End With</FONT>



<FONT color=#ff0000>'Revised to loop through all the worksheets and set the
</FONT>
<FONT color=#ff0000>'reference to each worksheet.
</FONT>


Row = 2

<FONT color=blue>For </FONT>Each wsSheet In ActiveWorkbook.Worksheets

<FONT color=blue>If </FONT>wsSheet.Name <> stName Then

<FONT color=blue>On Error</FONT> <FONT color=blue>GoTo</FONT> SheetNext

<FONT color=blue>With </FONT>wsSheet

<FONT color=blue>Set </FONT>FormulaCells = .Range("A1").SpecialCells(xlFormulas, 23)

<FONT color=blue>End With</FONT>

<FONT color=blue>For </FONT>Each Cell In FormulaCells

<FONT color=blue>With </FONT>FormulaSheet

Cells(Row, 1) = wsSheet.Name

Cells(Row, 2) = Cell.Address _

(RowAbsolute:=False, ColumnAbsolute:=False)

Cells(Row, 3) = " " & Cell.Formula

Cells(Row, 4) = Cell.Value

Row = Row + 1

<FONT color=blue>End With</FONT>

<FONT color=blue>Next </FONT>Cell

<FONT color=blue>End If</FONT>

SheetNext:

<FONT color=blue>Next </FONT>wsSheet



FormulaSheet.Columns("A:D").AutoFit



<FONT color=blue>With </FONT>Application

.ScreenUpdating =<FONT color=blue> True</FONT>

.DisplayAlerts =<FONT color=blue> True</FONT>

<FONT color=blue>End With</FONT>



<FONT color=blue>End Sub</FONT>
</PRE>

Kind regards,
Dennis