ExcelBanter - Show all formulas on sheet

Atwork, Ctrl+`, next to the number 1 key on my keyboard, will toggle between
valves and formulas

You may also want to try this macro by John Walkenbach

This will list formulas , cell address, and valves in a new worksheet for
the sheet you are on.

Sub ListFormulas()

'from John Walkenbach

'http://j-walk.com/ss/excel/tips/tip37.htm

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

"Atwork today" wrote in message
...
I have a spread sheet that contains a large amount of formulas. I would
like
to double check my formulas. Is there a way to show all the formulas at
once
instead of one cell at a time?