?can I wrap displayed formulas?

[David at Abbott]

I have multiple large formulas in a spreadsheet, and need to print a version
of the spreadsheet showing the formulas for verification purposes. The
"Format" "Cell" "Alignment" "Wrap text" doesn't seem to work on formulas. Is
there an alternate way to get the formulas to wrap within the cells? the
current alternative is to widen the columns extremely wide, making the
spreadsheet unwieldy to verify, or changing the font to a point where it is
difficult to read. Not a desirable option.

 

[Bob Phillips]

Insert Alt-Return at appropriate points in the formula.

 

[David Biddulph]

That doesn't seem to help (in Excel 2003).
Doesn't wrap when formula is displayed (even when set to wrap in Format/
Cells/ Alignment).

 

[Gord Dibben]

Try this John Walkenbach macro with slight revisions by myself.

Sub ListFormulas()
'from John Walkenbach
Dim FormulaCells As Range, Cell As Range
Dim FormulaSheet As Worksheet
Dim Row As Integer
Dim ws As Worksheet
' 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."
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
With FormulaSheet.Columns("B").Cells
.WrapText = True
.ColumnWidth = 30
.EntireRow.AutoFit = True
End With
Application.StatusBar = False
End Sub


Gord Dibben MS Excel MVP

On Thu, 24 Jul 2008 09:30:00 -0700, David at Abbott <David at