Need Formula Listing Code Modification

[Phil Hageman]

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

 

[Bob Phillips]

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)

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.

 

[David McRitchie]

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

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:

 

[Tom Ogilvy]

http://j-walk.com/ss/excel/tips/tip37.htm
Creating a List of Formulas

--
Regards,
Tom Ogilvy

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.contained in the target worksheet. Could someone help modify this code to do
the following: