Modification to code
 

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

-----Original Message-----
In the following macro code, is there a way to list only
the cells having a formula? For reasons beyond my
skills,
there are a lot of cells listed with no information.

Thanks,Phil

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


.

Modification to code
 

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)

"Peter Atherton" wrote in message
...
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

-----Original Message-----
In the following macro code, is there a way to list only
the cells having a formula? For reasons beyond my
skills,
there are a lot of cells listed with no information.

Thanks,Phil

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


.