How to print formulas and their results on the same page?

  • Thread starter Thread starter Dmitriy Kopnichev
  • Start date Start date
D

Dmitriy Kopnichev

Hello
How to print formulas and their results on the same page?
The "Formula Auditing mode" prints only formulas without their results.
 
Dmitriy, here is a macro from John Walkenbach that will list formulas ,
cell address, and valves in a new worksheet for the sheet you are on, see if
this will do what you need

Sub ListFormulas()
'from John Walkenbach
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 are 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

--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
You can insert a column and use a formula like this

=ShowAllInfo(A7)

Or

=Showformula(A7)


You need to copy this functions in a module also

Function ShowAllInfo(rng As Range)
' show Formula and Result from cell
ShowAllInfo = Right(rng.Formula, Len(rng.Formula) - 1) _
& " = " & rng.Value
End Function


Function ShowFormula(rng As Range)
ShowFormula = rng.Formula
End Function
 
Hello
Your macro just makes a new worksheet but doesn't list formulas, cell
address, and values in the new worksheet.
 
Dmitriy, works for me, To put in this macro, from your workbook right-click
the workbook's icon and pick View Code. This icon is to the left of the
"File" menu this will open the VBA editor, in the left hand window click on
your workbook name, go to insert, module, and paste the code in the window
that opens on the right hand side, press Alt and Q to close this window and
go back to your workbook and press alt and F8, this will bring up a box to
pick the Macro from, click on ListFormulas to run it. If you are using
excel 2000 or newer you may have to change the macro security settings to
get the macro to run.

--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
Hi Dmitriy,
If you install the User Defined Function in your personal.xls
you must specify the bookname unlike macros. Macros
can be found in any open workbook. You can set up a
a reference to your personal.xls library ; otherwise, you
have to use something like this when it is not in the same
project library.
=personal.xls!showformula(a1)

You will find more information on my page about user defined functions
GetFormula, GetFormulaD, ShowFormula, FontInfo etc..
Show FORMULA of another cell in Excel
http://www.mvps.org/dmcritchie/excel/formula.htm
 
How to delete a module?
Paul B said:
Dmitriy, works for me, To put in this macro, from your workbook right-click
the workbook's icon and pick View Code. This icon is to the left of the
"File" menu this will open the VBA editor, in the left hand window click on
your workbook name, go to insert, module, and paste the code in the window
that opens on the right hand side, press Alt and Q to close this window and
go back to your workbook and press alt and F8, this will bring up a box to
pick the Macro from, click on ListFormulas to run it. If you are using
excel 2000 or newer you may have to change the macro security settings to
get the macro to run.

--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
Click to expand...
 
I copied the functions in a module (not a sheet module). The ShowAllInfo
macro doesn't appear in the macro list after clicking alt and F8. How to run
the formula?
 
Thank you!
Paul B said:
Dmitriy, works for me, To put in this macro, from your workbook right-click
the workbook's icon and pick View Code. This icon is to the left of the
"File" menu this will open the VBA editor, in the left hand window click on
your workbook name, go to insert, module, and paste the code in the window
that opens on the right hand side, press Alt and Q to close this window and
go back to your workbook and press alt and F8, this will bring up a box to
pick the Macro from, click on ListFormulas to run it. If you are using
excel 2000 or newer you may have to change the macro security settings to
get the macro to run.

--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
Click to expand...
 
Dmitriy

ShowAllInfo is not a macro........it is a Function and is entered into a cell
just as any other Function.

It will not show up in the macro list.

Assume you have a formula in A1, Enter in B1 =ShowAllInfo(A1)

Gord Dibben XL2002
 
Delete a module by going to the VBEditor.

If you follow Paul's instructions you will probably wind up inside the
ThisWorkbook module of your project.

You do want to be there. Close that window.

You should now be in the Project Explorer window. Select your
Workbook/project by clicking on it. Right-click and "Remove Module". Click
no when asked to Export.

Save and hit ALT + F11 to return to Excel.

Gord Dibben XL2002
 
Thanks.
Delete a module by going to the VBEditor.

If you follow Paul's instructions you will probably wind up inside the
ThisWorkbook module of your project.

You do want to be there. Close that window.

You should now be in the Project Explorer window. Select your
Workbook/project by clicking on it. Right-click and "Remove Module". Click
no when asked to Export.

Save and hit ALT + F11 to return to Excel.

Gord Dibben XL2002
Click to expand...
 
I get an invalid name error in a cell with the =ShowAllInfo(a cell with a
formula).
 
This is not a macro

You must place this formula in B7 for example to see the formula in A7
=ShowAllInfo(A7)
 
Back
Top