How do I wrap text for formulaes

  • Thread starter Thread starter Naz
  • Start date Start date
N

Naz

I have a worksheet with a lot of formulaes - need to print on a single
sheet but cannot seem to wrap text them - any ideas will be welcomed.

TIA, Naz
 
I'm not sure if this works for formulas, but it certainly
works for text. Highlight the cells you need to wrap, in
the menu, click "format", "cells", click the "Alignment"
tab, then put a checkmark in the box beside "Wrap text".
If that doesn't work, someone else will have to come with
a different fix.
You might have to change your row heighth (not sure).
HTH
 
Thanks for your reply. it does work the text as u said but does not see
to work with the formulaes. I can not widen the cells as goes over th
2 pages. I need to print on one page with all the formulaes. i hop
some one has the answer.

TIA, Na
 
Naz

Try this macro from John Walkenbach to list formulas on a separate sheet.

Modified slightly to produce wrapped text.

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
FormulaSheet.Columns("A:C").Cells.WrapText = True
''Modified from AutoFit
Application.StatusBar = False
End Sub

Gord Dibben Excel MVP
 
Gord,
Thx for the macro - it does work as you suggested. What I am looking
for is similar to the way how text works when it has wrap text ticked.
At the moment I go into Tools, options and tick on formulas check and
it does show all the formulas but not in full as cells are not wide
enough. If I widen the cells then It does not fit on 1 page. So thats
why i need to wrap round fuction. Any help will be appreciated.

I hope this makes sense.

TIA, Naz

QUOTE]-Originally posted by Gord Dibben -
*Naz

Try this macro from John Walkenbach to list formulas on a separate
sheet.

Modified slightly to produce wrapped text.

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
FormulaSheet.Columns("A:C").Cells.WrapText = True
''Modified from AutoFit
Application.StatusBar = False
End Sub

Gord Dibben Excel MVP
 
Naz

I realise what you would like to be able to do but I don't know of a way to
wrap text when in Formula View.

That is why I suggested Tom's macro as a workaround.

As an aside........you can toggle view formulas by hitting CRTL + `(above the
TAB key. No need to go into Tools>Options>View.

Gord
 
Maybe you could have the macro just copy the worksheet and replace the formulas
with the formula strings. Then look at your stuff there:

Option Explicit
Sub ListFormulas2()

Dim newWks As Worksheet
Dim curWks As Worksheet
Dim FormulaRange As Range
Dim myCell As Range
Dim CalcMode As Long

Set curWks = ActiveSheet

With curWks
Set FormulaRange = Nothing
On Error Resume Next
Set FormulaRange = .Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If FormulaRange Is Nothing Then
MsgBox "no formulas--quitting"
Exit Sub
End If
End With

With Application
.ScreenUpdating = False
CalcMode = xlCalculationManual
End With

curWks.Copy _
after:=curWks

Set newWks = ActiveSheet
With newWks
On Error Resume Next
newWks.Name = Left(curWks.Name, 29) & "-F"
On Error GoTo 0

For Each myCell In FormulaRange.Cells
.Range(myCell.Address).Value = "'" & myCell.Formula
Next myCell

With .UsedRange
.WrapText = True
.ColumnWidth = 40
.EntireRow.AutoFit
End With
End With

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

Gord,
Thx for the macro - it does work as you suggested. What I am looking
for is similar to the way how text works when it has wrap text ticked.
At the moment I go into Tools, options and tick on formulas check and
it does show all the formulas but not in full as cells are not wide
enough. If I widen the cells then It does not fit on 1 page. So thats
why i need to wrap round fuction. Any help will be appreciated.

I hope this makes sense.

TIA, Naz

QUOTE]-Originally posted by Gord Dibben -
*Naz

Try this macro from John Walkenbach to list formulas on a separate
sheet.

Modified slightly to produce wrapped text.

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
FormulaSheet.Columns("A:C").Cells.WrapText = True
''Modified from AutoFit
Application.StatusBar = False
End Sub

Gord Dibben Excel MVP

Thanks for your reply. it does work the text as u said but does not seem
to work with the formulaes. I can not widen the cells as goes over the
2 pages. I need to print on one page with all the formulaes. i hope
some one has the answer.

TIA, Naz
Click to expand...
Click to expand...
 
Oops, a typo:

Change this:

With Application
.ScreenUpdating = False
CalcMode = xlCalculationManual
End With

to:

With Application
.ScreenUpdating = False
CalcMode = .Calculation
End With

(I wanted to preserve the calculation mode--not always change it to manual!)

Dave said:
Maybe you could have the macro just copy the worksheet and replace the formulas
with the formula strings. Then look at your stuff there:

Option Explicit
Sub ListFormulas2()

Dim newWks As Worksheet
Dim curWks As Worksheet
Dim FormulaRange As Range
Dim myCell As Range
Dim CalcMode As Long

Set curWks = ActiveSheet

With curWks
Set FormulaRange = Nothing
On Error Resume Next
Set FormulaRange = .Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If FormulaRange Is Nothing Then
MsgBox "no formulas--quitting"
Exit Sub
End If
End With

With Application
.ScreenUpdating = False
CalcMode = xlCalculationManual
End With

curWks.Copy _
after:=curWks

Set newWks = ActiveSheet
With newWks
On Error Resume Next
newWks.Name = Left(curWks.Name, 29) & "-F"
On Error GoTo 0

For Each myCell In FormulaRange.Cells
.Range(myCell.Address).Value = "'" & myCell.Formula
Next myCell

With .UsedRange
.WrapText = True
.ColumnWidth = 40
.EntireRow.AutoFit
End With
End With

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
Gord,
Thx for the macro - it does work as you suggested. What I am looking
for is similar to the way how text works when it has wrap text ticked.
At the moment I go into Tools, options and tick on formulas check and
it does show all the formulas but not in full as cells are not wide
enough. If I widen the cells then It does not fit on 1 page. So thats
why i need to wrap round fuction. Any help will be appreciated.

I hope this makes sense.

TIA, Naz

QUOTE]-Originally posted by Gord Dibben -
*Naz

Try this macro from John Walkenbach to list formulas on a separate
sheet.

Modified slightly to produce wrapped text.

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
FormulaSheet.Columns("A:C").Cells.WrapText = True
''Modified from AutoFit
Application.StatusBar = False
End Sub

Gord Dibben Excel MVP

Thanks for your reply. it does work the text as u said but does not seem
to work with the formulaes. I can not widen the cells as goes over the
2 pages. I need to print on one page with all the formulaes. i hope
some one has the answer.

TIA, Naz
Click to expand...
Click to expand...
Click to expand...
 
Dave

I posted John Walkenbach's(not Tom's) ListFormulas macro to place the formulas
on a new worksheet and OP found that not acceptable. Needs the formulas
wrapped as is.

Gord Dibben Excel MVP

Maybe you could have the macro just copy the worksheet and replace the formulas
with the formula strings. Then look at your stuff there:

Option Explicit
Sub ListFormulas2()

Dim newWks As Worksheet
Dim curWks As Worksheet
Dim FormulaRange As Range
Dim myCell As Range
Dim CalcMode As Long

Set curWks = ActiveSheet

With curWks
Set FormulaRange = Nothing
On Error Resume Next
Set FormulaRange = .Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If FormulaRange Is Nothing Then
MsgBox "no formulas--quitting"
Exit Sub
End If
End With

With Application
.ScreenUpdating = False
CalcMode = xlCalculationManual
End With

curWks.Copy _
after:=curWks

Set newWks = ActiveSheet
With newWks
On Error Resume Next
newWks.Name = Left(curWks.Name, 29) & "-F"
On Error GoTo 0

For Each myCell In FormulaRange.Cells
.Range(myCell.Address).Value = "'" & myCell.Formula
Next myCell

With .UsedRange
.WrapText = True
.ColumnWidth = 40
.EntireRow.AutoFit
End With
End With

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

Gord,
Thx for the macro - it does work as you suggested. What I am looking
for is similar to the way how text works when it has wrap text ticked.
At the moment I go into Tools, options and tick on formulas check and
it does show all the formulas but not in full as cells are not wide
enough. If I widen the cells then It does not fit on 1 page. So thats
why i need to wrap round fuction. Any help will be appreciated.

I hope this makes sense.

TIA, Naz

QUOTE]-Originally posted by Gord Dibben -
*Naz

Try this macro from John Walkenbach to list formulas on a separate
sheet.

Modified slightly to produce wrapped text.

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
FormulaSheet.Columns("A:C").Cells.WrapText = True
''Modified from AutoFit
Application.StatusBar = False
End Sub

Gord Dibben Excel MVP

Thanks for your reply. it does work the text as u said but does not seem
to work with the formulaes. I can not widen the cells as goes over the
2 pages. I need to print on one page with all the formulaes. i hope
some one has the answer.

TIA, Naz
Click to expand...
Click to expand...
Click to expand...
 
I thought that one of the problems the OP had was that the formulas weren't in
the same locations.

I thought that if you convert the formulas to strings on another worksheet, then
the OP could wraptext there. It won't help if Naz wanted to edit the formulas
in place, but it might if it was for printing/reviewing.

So if you squint your eyes just a bit and redefine ASIS as same address (but
different worksheet), then maybe, maybe, it would be sufficient.

Dave

I posted John Walkenbach's(not Tom's) ListFormulas macro to place the formulas
on a new worksheet and OP found that not acceptable. Needs the formulas
wrapped as is.

Gord Dibben Excel MVP

Maybe you could have the macro just copy the worksheet and replace the formulas
with the formula strings. Then look at your stuff there:

Option Explicit
Sub ListFormulas2()

Dim newWks As Worksheet
Dim curWks As Worksheet
Dim FormulaRange As Range
Dim myCell As Range
Dim CalcMode As Long

Set curWks = ActiveSheet

With curWks
Set FormulaRange = Nothing
On Error Resume Next
Set FormulaRange = .Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If FormulaRange Is Nothing Then
MsgBox "no formulas--quitting"
Exit Sub
End If
End With

With Application
.ScreenUpdating = False
CalcMode = xlCalculationManual
End With

curWks.Copy _
after:=curWks

Set newWks = ActiveSheet
With newWks
On Error Resume Next
newWks.Name = Left(curWks.Name, 29) & "-F"
On Error GoTo 0

For Each myCell In FormulaRange.Cells
.Range(myCell.Address).Value = "'" & myCell.Formula
Next myCell

With .UsedRange
.WrapText = True
.ColumnWidth = 40
.EntireRow.AutoFit
End With
End With

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

Gord,
Thx for the macro - it does work as you suggested. What I am looking
for is similar to the way how text works when it has wrap text ticked.
At the moment I go into Tools, options and tick on formulas check and
it does show all the formulas but not in full as cells are not wide
enough. If I widen the cells then It does not fit on 1 page. So thats
why i need to wrap round fuction. Any help will be appreciated.

I hope this makes sense.

TIA, Naz

QUOTE]-Originally posted by Gord Dibben -
*Naz

Try this macro from John Walkenbach to list formulas on a separate
sheet.

Modified slightly to produce wrapped text.

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
FormulaSheet.Columns("A:C").Cells.WrapText = True
''Modified from AutoFit
Application.StatusBar = False
End Sub

Gord Dibben Excel MVP

On Wed, 31 Dec 2003 12:45:56 -0600, Naz
<[email protected]>
wrote:

Thanks for your reply. it does work the text as u said but does not
seem
to work with the formulaes. I can not widen the cells as goes over
the
2 pages. I need to print on one page with all the formulaes. i hope
some one has the answer.

TIA, Naz
Click to expand...
Click to expand...
Click to expand...
 
Dave and Gord

Thx a million - the macros worked just as required

Your assistance was greatly appreciated.

Naz
 
If I understand what you are asking, just use Alt+Enter
to create a new line in the Formula. I use this to
strcuture my formulas like VBA code.
Then I copy the formula as text to a documentation cell
where I add comments at the end of every linhe that needs
it.

Exemple of Formula cell:
=IF(Z10="",
N10+O10-R10,
IF(K10="SERV",
(N10+O10)*Q10/VLOOKUP(Z10,SANRaid,Col_RaidDrives)-R10,
(N10+O10)))

Example of Documentation cell:
Total space in SAN Storage and SERV to be backed up
IF(Y10="" ==> if the RAID type is blank, we assume a
server with no raid => Backup the disk size * drives -
swap space
IF(K10="SERV" ==> if server (versus SAN Storage)
(N10+O10)*Q10-VLOOKUP(Y10,SANRaid,Col_RaidDrives)-R10,
==> (drive size(N10) + expansion(O10)) * Drives
indicated - Drives in RAID cluster - Exclude space (i.e.
swap and others)
(N10+O10) ==> simply GBUs requested + Expansion (no
swap involved)

-----Original Message-----
Dave

I posted John Walkenbach's(not Tom's) ListFormulas macro to place the formulas
on a new worksheet and OP found that not acceptable. Needs the formulas
wrapped as is.

Gord Dibben Excel MVP

Maybe you could have the macro just copy the worksheet and replace the formulas
with the formula strings. Then look at your stuff there:

Option Explicit
Sub ListFormulas2()

Dim newWks As Worksheet
Dim curWks As Worksheet
Dim FormulaRange As Range
Dim myCell As Range
Dim CalcMode As Long

Set curWks = ActiveSheet

With curWks
Set FormulaRange = Nothing
On Error Resume Next
Set FormulaRange = .Cells.SpecialCells (xlCellTypeFormulas)
On Error GoTo 0

If FormulaRange Is Nothing Then
MsgBox "no formulas--quitting"
Exit Sub
End If
End With

With Application
.ScreenUpdating = False
CalcMode = xlCalculationManual
End With

curWks.Copy _
after:=curWks

Set newWks = ActiveSheet
With newWks
On Error Resume Next
newWks.Name = Left(curWks.Name, 29) & "-F"
On Error GoTo 0

For Each myCell In FormulaRange.Cells
.Range(myCell.Address).Value = "'" & myCell.Formula
Next myCell

With .UsedRange
.WrapText = True
.ColumnWidth = 40
.EntireRow.AutoFit
End With
End With

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

Gord,
Thx for the macro - it does work as you suggested. What I am looking
for is similar to the way how text works when it has wrap text ticked.
At the moment I go into Tools, options and tick on formulas check and
it does show all the formulas but not in full as cells are not wide
enough. If I widen the cells then It does not fit on 1 page. So thats
why i need to wrap round fuction. Any help will be appreciated.

I hope this makes sense.

TIA, Naz

QUOTE]-Originally posted by Gord Dibben -
*Naz

Try this macro from John Walkenbach to list formulas on a separate
sheet.

Modified slightly to produce wrapped text.

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
FormulaSheet.Columns("A:C").Cells.WrapText = True
''Modified from AutoFit
Application.StatusBar = False
End Sub

Gord Dibben Excel MVP

On Wed, 31 Dec 2003 12:45:56 -0600, Naz
<[email protected]>
wrote:

Thanks for your reply. it does work the text as u said but does not
seem
to work with the formulaes. I can not widen the cells as goes over
the
2 pages. I need to print on one page with all the formulaes. i hope
some one has the answer.

TIA, Naz
Click to expand...
Click to expand...

.
Click to expand...
Click to expand...
 
Back
Top