Modification to code

[Peter Atherton]

I have just remmed out the unwanted lines in JW's code,
now it just prints a list of addresses
Sub ListFormulas()
'From John Walkenback
'Lists formulas, cell addresses, and values in a newly
created Worksheet
'
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

Regards
Peter

 

[Bob Phillips]

Peter,

This commented lien

' Cells(Row, 2) = " " & Cell.Formula

should not be commented.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)