Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Formula Listing Code Modification
This code creates a new worksheet in a workbook and lists all formulas contained in the target worksheet. Could someone help modify this code to do the following:
1. The resulting list contains many rows where there are no formulas would like to omit the blank (no formula) rows. 2. Make the column width for column B and C be 45 (rather than the existing auto fit). 3. When the data in column B or C exceeds the 45 width, expand row height to fit the text, and wrap text. 4. When the formula in column B references a workbook other than the current workbook, make font red in all cells in that row. Any help would be much appreciated. Thanks, Phil Sub ListFormulas() 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 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
|
|||
|
|||
Need Formula Listing Code Modification
Phil,
This looks like the same question you asked on May 27th, and this was my response then Apart from no 1, which I can't see that it does, this covers the other points Sub ListFormulas() 'From John Walkenbach List 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 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 If InStr(1, Cell.Formula, "[") 0 Then Cells(Row, 2).Font.ColorIndex = 3 End If Row = Row + 1 End With Next Cell 'Adjust column widths With FormulaSheet .Columns("A:A").AutoFit With .Columns("B:C") .ColumnWidth = 45 .WrapText = True End With .Rows("1:1000").AutoFit End With Application.StatusBar = False End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Phil Hageman" wrote in message ... This code creates a new worksheet in a workbook and lists all formulas contained in the target worksheet. Could someone help modify this code to do the following: 1. The resulting list contains many rows where there are no formulas - would like to omit the blank (no formula) rows. 2. Make the column width for column B and C be "45" (rather than the existing auto fit). 3. When the data in column B or C exceeds the "45" width, expand row height to fit the text, and wrap text. 4. When the formula in column B references a workbook other than the current workbook, make font red in all cells in that row. Any help would be much appreciated. Thanks, Phil Sub ListFormulas() 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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Formula Listing Code Modification
Looks to me like it would not find any formulas in cell A1 and terminate.
But the intent was to use special cells to pick up formulas only. I think you want instead for Range("A"). to use Cells. Set FormulaCells = Cells.SpecialCells(xlFormulas, 23) The specialcells automatically limits you to the used range, and the formulas limits you to the cells with formulas. The 23 selects all types of formulas. As far as changes to width go you would record a macro and place those results into your macro. -- --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Phil Hageman" wrote in message ... This code creates a new worksheet in a workbook and lists all formulas contained in the target worksheet. Could someone help modify this code to do the following: 1. The resulting list contains many rows where there are no formulas - would like to omit the blank (no formula) rows. 2. Make the column width for column B and C be "45" (rather than the existing auto fit). 3. When the data in column B or C exceeds the "45" width, expand row height to fit the text, and wrap text. 4. When the formula in column B references a workbook other than the current workbook, make font red in all cells in that row. Any help would be much appreciated. Thanks, Phil Sub ListFormulas() 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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Formula Listing Code Modification
http://j-walk.com/ss/excel/tips/tip37.htm
Creating a List of Formulas -- Regards, Tom Ogilvy "Marilyn" wrote in message ... This sounds like EXACTLY the solution to my problem. How do I make this code work? What do I need to have or to do? Thank you for helping a newbie to this area of computer use. "Phil Hageman" wrote: This code creates a new worksheet in a workbook and lists all formulas contained in the target worksheet. Could someone help modify this code to do the following: 1. The resulting list contains many rows where there are no formulas - would like to omit the blank (no formula) rows. 2. Make the column width for column B and C be "45" (rather than the existing auto fit). 3. When the data in column B or C exceeds the "45" width, expand row height to fit the text, and wrap text. 4. When the formula in column B references a workbook other than the current workbook, make font red in all cells in that row. Any help would be much appreciated. Thanks, Phil Sub ListFormulas() 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 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 | |||
Code Modification | Excel Programming | |||
Help in Modification of existing code | Excel Programming | |||
Modification to code | Excel Programming |