LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 31
Default 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


.

 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
modification to this code James Excel Discussion (Misc queries) 0 March 23rd 09 09:20 PM
Code modification help AndyMP Excel Worksheet Functions 1 February 8th 09 11:41 PM
line code modification TUNGANA KURMA RAJU Excel Discussion (Misc queries) 3 March 2nd 07 01:12 PM
last modification Chip Smith Excel Discussion (Misc queries) 1 June 19th 06 11:55 PM
Formula Modification Help Dmorri254 Excel Worksheet Functions 4 November 15th 04 07:06 PM


All times are GMT +1. The time now is 06:37 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"