Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
modification to this code | Excel Discussion (Misc queries) | |||
Code modification help | Excel Worksheet Functions | |||
line code modification | Excel Discussion (Misc queries) | |||
last modification | Excel Discussion (Misc queries) | |||
Formula Modification Help | Excel Worksheet Functions |