Anyway to Print Formulas in Worksheet

  • Thread starter Thread starter Jan
  • Start date Start date
J

Jan

Is there anyway that I can print my worksheet showing the
formulas in cells rather than the values?

Thanks, Jan
 
Jan,
If you go into tools > options, then in the View tab,
check off "Formulas", that's all :-)
THT
Kevin M
 
Hi Jan!

Kevin gives the correct answer but you might like to use the following
UDF:

Function GETFORMULA(cell As Range) As String
GETFORMULA = cell.Formula
End Function

Helps especially if placed near the cell with the formula as you get
to see what it is currently returning.

You might also like the ListFormulas subroutine from John Walkenbach:

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


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top