Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Printing formulas
I have 5 work sheets in a BOOK,each sheet a2:m30, every cell contains
formula.Iwould like to keep a hard copy of these formulas I have used.In Toolsoptionsview tab I have checked formulas,but these formulas wrap each other by which I can't print these formulas.Is there any way to print all these formulas at one go. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Printing formulas
You could just dump them all to a new worksheet and print that
Sub PrintFormulas() Dim cell As Range Dim sh As Worksheet Dim i As Long On Error Resume Next Set sh = Worksheets("Formulas") On Error GoTo 0 If sh Is Nothing Then Set sh = Worksheets.Add sh.Name = "Formulas" Else sh.Cells.ClearContents End If For Each cell In ActiveSheet.UsedRange If cell.HasFormula Then i = i + 1 sh.Cells(i, "A").Value = cell.Address(False, False) sh.Cells(i, "B").Value = "'" & cell.Formula End If Next cell sh.Activate sh.Columns("B:B").AutoFit End Sub -- HTH RP (remove nothere from the email address if mailing direct) "TUNGANA KURMA RAJU" wrote in message ... I have 5 work sheets in a BOOK,each sheet a2:m30, every cell contains formula.Iwould like to keep a hard copy of these formulas I have used.In Toolsoptionsview tab I have checked formulas,but these formulas wrap each other by which I can't print these formulas.Is there any way to print all these formulas at one go. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Printing formulas
Generally you would only need a representative formula. If that is the case
you can show a representative formula in a cell somewhere else or if you want to actually show all of the formulaa down a column (fill handle) to see how they changed you can place your representative formulas in another column. That way you can see all of your spreadsheet and the formulas you want to see at the same time. Then simply print the worksheet in your normal fashion. Show FORMULA or FORMAT of another cell http://www.mvps.org/dmcritchie/excel/formula.htm =personal.xls!getformula(A1) =personal.xls!GetFormulaD(A1) Information on installing macros and User Defined Functions (UDF) http://www.mvps.org/dmcritchie/excel/getstarted Information on use of the Fill Handle http://www.mvps.org/dmcritchie/excel/fillhand.htm To actually do what you asked you should copy the worksheet to another worksheet where you can switch to the formula view and change the column widths, since you wouuld not want to change the column widths on your original sheet. Edit, Move or Copy Sheet, create a copy Switch to the Formula view, and then you can change the widths, under format menu , cell, alignment, you can check "Wrap text" anything that helps you format the formulas the way you want. If you want a more complete record in another worksheet showing cell address, text (displayed value), value, formula, number format look for FormulaSheet in the code at http://www.mvps.org/dmcritchie/excel/code/formula.txt Some other documentation of things in a workbook (also see related area) Build Table of Contents, similar listings, working with Hyperlinks http://www.mvps.org/dmcritchie/excel/buildtoc.htm --- 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 "TUNGANA KURMA RAJU" wrote in message ... I have 5 work sheets in a BOOK,each sheet a2:m30, every cell contains formula.Iwould like to keep a hard copy of these formulas I have used.In Toolsoptionsview tab I have checked formulas,but these formulas wrap each other by which I can't print these formulas.Is there any way to print all these formulas at one go. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Printing formulas
Thanks,David,I am really honoured to get this answer from the great
intellectual. "David McRitchie" wrote: Generally you would only need a representative formula. If that is the case you can show a representative formula in a cell somewhere else or if you want to actually show all of the formulaa down a column (fill handle) to see how they changed you can place your representative formulas in another column. That way you can see all of your spreadsheet and the formulas you want to see at the same time. Then simply print the worksheet in your normal fashion. Show FORMULA or FORMAT of another cell http://www.mvps.org/dmcritchie/excel/formula.htm =personal.xls!getformula(A1) =personal.xls!GetFormulaD(A1) Information on installing macros and User Defined Functions (UDF) http://www.mvps.org/dmcritchie/excel/getstarted Information on use of the Fill Handle http://www.mvps.org/dmcritchie/excel/fillhand.htm To actually do what you asked you should copy the worksheet to another worksheet where you can switch to the formula view and change the column widths, since you wouuld not want to change the column widths on your original sheet. Edit, Move or Copy Sheet, create a copy Switch to the Formula view, and then you can change the widths, under format menu , cell, alignment, you can check "Wrap text" anything that helps you format the formulas the way you want. If you want a more complete record in another worksheet showing cell address, text (displayed value), value, formula, number format look for FormulaSheet in the code at http://www.mvps.org/dmcritchie/excel/code/formula.txt Some other documentation of things in a workbook (also see related area) Build Table of Contents, similar listings, working with Hyperlinks http://www.mvps.org/dmcritchie/excel/buildtoc.htm --- 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 "TUNGANA KURMA RAJU" wrote in message ... I have 5 work sheets in a BOOK,each sheet a2:m30, every cell contains formula.Iwould like to keep a hard copy of these formulas I have used.In Toolsoptionsview tab I have checked formulas,but these formulas wrap each other by which I can't print these formulas.Is there any way to print all these formulas at one go. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Printing formulas
Thanks,Bob.It also worked well,once again I am greatful to you.
"Bob Phillips" wrote: You could just dump them all to a new worksheet and print that Sub PrintFormulas() Dim cell As Range Dim sh As Worksheet Dim i As Long On Error Resume Next Set sh = Worksheets("Formulas") On Error GoTo 0 If sh Is Nothing Then Set sh = Worksheets.Add sh.Name = "Formulas" Else sh.Cells.ClearContents End If For Each cell In ActiveSheet.UsedRange If cell.HasFormula Then i = i + 1 sh.Cells(i, "A").Value = cell.Address(False, False) sh.Cells(i, "B").Value = "'" & cell.Formula End If Next cell sh.Activate sh.Columns("B:B").AutoFit End Sub -- HTH RP (remove nothere from the email address if mailing direct) "TUNGANA KURMA RAJU" wrote in message ... I have 5 work sheets in a BOOK,each sheet a2:m30, every cell contains formula.Iwould like to keep a hard copy of these formulas I have used.In Toolsoptionsview tab I have checked formulas,but these formulas wrap each other by which I can't print these formulas.Is there any way to print all these formulas at one go. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Printing formulas
Mr.Bob Philips,
Though I am late,I tested your code and really it is very good.I have one query abouut this code.Am I to run this code twice?.When I first run this code It has inserted "Formulas" sheet first.second time when I run this code all formulas with address dumped into this formulas sheet.Is this two stepped code? secondly I am facing a problem with this code.While printing this "Formulas" sheet many of my formulas have truncated,as they are very large(most of them are more than 250 characters).Will you please modify that last line of your code so that column B:B width b= 94 and all cells format in column B:B =wraptext format =true. and I would like to add column name"Address" for col A and "Formula" to column B "Bob Phillips" wrote: You could just dump them all to a new worksheet and print that Sub PrintFormulas() Dim cell As Range Dim sh As Worksheet Dim i As Long On Error Resume Next Set sh = Worksheets("Formulas") On Error GoTo 0 If sh Is Nothing Then Set sh = Worksheets.Add sh.Name = "Formulas" Else sh.Cells.ClearContents End If For Each cell In ActiveSheet.UsedRange If cell.HasFormula Then i = i + 1 sh.Cells(i, "A").Value = cell.Address(False, False) sh.Cells(i, "B").Value = "'" & cell.Formula End If Next cell sh.Activate sh.Columns("B:B").AutoFit End Sub -- HTH RP (remove nothere from the email address if mailing direct) "TUNGANA KURMA RAJU" wrote in message ... I have 5 work sheets in a BOOK,each sheet a2:m30, every cell contains formula.Iwould like to keep a hard copy of these formulas I have used.In Toolsoptionsview tab I have checked formulas,but these formulas wrap each other by which I can't print these formulas.Is there any way to print all these formulas at one go. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Printing formulas
There was a slight bug in Bob's original code. If the Formulas worksheet didn't
exist, then when the code added that sheet, it became the activesheet. And there were no formulas on that sheet--so nothing got done. Option Explicit Sub PrintFormulas2() Dim cell As Range Dim sh As Worksheet Dim curSh As Worksheet Dim i As Long Set curSh = ActiveSheet On Error Resume Next Set sh = Worksheets("Formulas") On Error GoTo 0 If sh Is Nothing Then Set sh = Worksheets.Add sh.Name = "Formulas" Else sh.Cells.ClearContents End If sh.Range("a1").Resize(1, 2).Value = Array("Address", "Formula") i = 1 For Each cell In curSh.UsedRange If cell.HasFormula Then i = i + 1 sh.Cells(i, "A").Value = cell.Address(False, False) sh.Cells(i, "B").Value = "'" & cell.Formula End If Next cell With sh .Activate .Range("b:b").EntireColumn.ColumnWidth = 94 .Range("b:b").WrapText = True .Rows.AutoFit End With End Sub When you want to tweak code, you can get a lot of the basics by just recording code when you do it manually. (It can be quicker than asking a followup.) TUNGANA KURMA RAJU wrote: Mr.Bob Philips, Though I am late,I tested your code and really it is very good.I have one query abouut this code.Am I to run this code twice?.When I first run this code It has inserted "Formulas" sheet first.second time when I run this code all formulas with address dumped into this formulas sheet.Is this two stepped code? secondly I am facing a problem with this code.While printing this "Formulas" sheet many of my formulas have truncated,as they are very large(most of them are more than 250 characters).Will you please modify that last line of your code so that column B:B width b= 94 and all cells format in column B:B =wraptext format =true. and I would like to add column name"Address" for col A and "Formula" to column B "Bob Phillips" wrote: You could just dump them all to a new worksheet and print that Sub PrintFormulas() Dim cell As Range Dim sh As Worksheet Dim i As Long On Error Resume Next Set sh = Worksheets("Formulas") On Error GoTo 0 If sh Is Nothing Then Set sh = Worksheets.Add sh.Name = "Formulas" Else sh.Cells.ClearContents End If For Each cell In ActiveSheet.UsedRange If cell.HasFormula Then i = i + 1 sh.Cells(i, "A").Value = cell.Address(False, False) sh.Cells(i, "B").Value = "'" & cell.Formula End If Next cell sh.Activate sh.Columns("B:B").AutoFit End Sub -- HTH RP (remove nothere from the email address if mailing direct) "TUNGANA KURMA RAJU" wrote in message ... I have 5 work sheets in a BOOK,each sheet a2:m30, every cell contains formula.Iwould like to keep a hard copy of these formulas I have used.In Toolsoptionsview tab I have checked formulas,but these formulas wrap each other by which I can't print these formulas.Is there any way to print all these formulas at one go. -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Printing formulas
A million Thanks to Dave Peterson.You really made my day.Your code worked
perfectly,the way what I need.Thanks once again "Dave Peterson" wrote: There was a slight bug in Bob's original code. If the Formulas worksheet didn't exist, then when the code added that sheet, it became the activesheet. And there were no formulas on that sheet--so nothing got done. Option Explicit Sub PrintFormulas2() Dim cell As Range Dim sh As Worksheet Dim curSh As Worksheet Dim i As Long Set curSh = ActiveSheet On Error Resume Next Set sh = Worksheets("Formulas") On Error GoTo 0 If sh Is Nothing Then Set sh = Worksheets.Add sh.Name = "Formulas" Else sh.Cells.ClearContents End If sh.Range("a1").Resize(1, 2).Value = Array("Address", "Formula") i = 1 For Each cell In curSh.UsedRange If cell.HasFormula Then i = i + 1 sh.Cells(i, "A").Value = cell.Address(False, False) sh.Cells(i, "B").Value = "'" & cell.Formula End If Next cell With sh .Activate .Range("b:b").EntireColumn.ColumnWidth = 94 .Range("b:b").WrapText = True .Rows.AutoFit End With End Sub When you want to tweak code, you can get a lot of the basics by just recording code when you do it manually. (It can be quicker than asking a followup.) TUNGANA KURMA RAJU wrote: Mr.Bob Philips, Though I am late,I tested your code and really it is very good.I have one query abouut this code.Am I to run this code twice?.When I first run this code It has inserted "Formulas" sheet first.second time when I run this code all formulas with address dumped into this formulas sheet.Is this two stepped code? secondly I am facing a problem with this code.While printing this "Formulas" sheet many of my formulas have truncated,as they are very large(most of them are more than 250 characters).Will you please modify that last line of your code so that column B:B width b= 94 and all cells format in column B:B =wraptext format =true. and I would like to add column name"Address" for col A and "Formula" to column B "Bob Phillips" wrote: You could just dump them all to a new worksheet and print that Sub PrintFormulas() Dim cell As Range Dim sh As Worksheet Dim i As Long On Error Resume Next Set sh = Worksheets("Formulas") On Error GoTo 0 If sh Is Nothing Then Set sh = Worksheets.Add sh.Name = "Formulas" Else sh.Cells.ClearContents End If For Each cell In ActiveSheet.UsedRange If cell.HasFormula Then i = i + 1 sh.Cells(i, "A").Value = cell.Address(False, False) sh.Cells(i, "B").Value = "'" & cell.Formula End If Next cell sh.Activate sh.Columns("B:B").AutoFit End Sub -- HTH RP (remove nothere from the email address if mailing direct) "TUNGANA KURMA RAJU" wrote in message ... I have 5 work sheets in a BOOK,each sheet a2:m30, every cell contains formula.Iwould like to keep a hard copy of these formulas I have used.In Toolsoptionsview tab I have checked formulas,but these formulas wrap each other by which I can't print these formulas.Is there any way to print all these formulas at one go. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing Excel Formulas without file paths updating | Excel Discussion (Misc queries) | |||
Array Formulas take waaaay too long... | Excel Worksheet Functions | |||
Problem with named formula's | Excel Worksheet Functions | |||
In Exel 2000, stop the blank cells (with formulas) from printing. | Excel Discussion (Misc queries) | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |