Printing formulas

royhern

Board Regular
Joined
Feb 21, 2002
Messages
158
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

AC

Board Regular
Joined
Mar 21, 2002
Messages
153
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

Well-known Member
Joined
Jul 27, 2002
Messages
1,920
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,307
Messages
5,641,439
Members
417,209
Latest member
Agbarker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top