Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Macro Modification
This is John Walkenbacks code to make a list of formulas on a worksheet. I would like to modify it as follows:
1. Not show the rows where there is no formula 2. In the case where a formula links to another workbook, make all fonts red (all three columns) 3. Make the €śFormula€ť and €śValue€ť column widths = 45 4. Where columns are not wide enough to display all the text, make row height fit, and wrap. Could someone modify this code? Thanks, Phil Sub ListFormulas() 'From John Walkenback '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 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 Macro Modification
I had a go at this yesterday Phil. Did you not see that, or was there a
problem with what I suggested? -- 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 is John Walkenback's code to make a list of formulas on a worksheet. I would like to modify it as follows: 1. Not show the rows where there is no formula 2. In the case where a formula links to another workbook, make all fonts red (all three columns) 3. Make the "Formula" and "Value" column widths = 45 4. Where columns are not wide enough to display all the text, make row height fit, and wrap. Could someone modify this code? Thanks, Phil Sub ListFormulas() 'From John Walkenback '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 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 Macro Modification
Bob
Sorry about the multiple posts - working on two different computers (work and home) and the search works differently when I look up the thread. I don't see everything, thus multiple postings. ?? Your mod works great - thanks. As to the first issue, eliminating blank lines: I ran this macro on a worksheet and the result was a lis of 523 rows, with all 523 rows showing the cell (or range) address in column A. However, only 88 of the rows showed a formula in column B and its corresponding return value in column C. What I would like to do is eliminate the rows where column B and C are blank Phil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Modification Help | Excel Discussion (Misc queries) | |||
macro - modification | Excel Discussion (Misc queries) | |||
Macro Modification | Excel Worksheet Functions | |||
Macro modification | Excel Discussion (Misc queries) | |||
Macro Modification Help | Excel Worksheet Functions |