ExcelBanter - Displaying BOTH formulas and values

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Displaying BOTH formulas and values (https://www.excelbanter.com/excel-discussion-misc-queries/195777-displaying-both-formulas-values.html)

Displaying BOTH formulas and values

Is there some easy way to display both the values and the formulas?
I want to be able to show the results of the formulas as well as the
formulas used in obtaining the cell results. I know I can toggle back and
forth by using Ctrl+`, but for what I want to do I now have to display the
formulas and copy the sheet as formulas into a new worksheet, then insert
columns and copy over the results into adjacent columns of cells. Is there
another way of doing this?

Displaying BOTH formulas and values

John Walkenbach macro.

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 ''AutoFit
Application.StatusBar = False
End Sub

Gord Dibben MS Excel MVP

On Tue, 22 Jul 2008 09:03:02 -0700, Ray S.
wrote:

Your macro is useful...I can work with that.

"Don Guillett" wrote:

I must have misunderstood. Sample layout of what you do want. This maybe

Sub showformulainnextcell()
For Each c In Selection
c.Offset(, 1) = "'" & c.Formula
Next
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ray S." wrote in message
...
That idea is very cumbersome, especially where formulas are complex or
include complicated functions; besides, it puts both the value and the
formula in the same cell.

"Don Guillett" wrote:

=B15*B17 &"=b15*b17"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ray S." wrote in message
...
Is there some easy way to display both the values and the formulas?
I want to be able to show the results of the formulas as well as the
formulas used in obtaining the cell results. I know I can toggle back
and
forth by using Ctrl+`, but for what I want to do I now have to display
the
formulas and copy the sheet as formulas into a new worksheet, then
insert
columns and copy over the results into adjacent columns of cells. Is
there
another way of doing this?

Displaying BOTH formulas and values

WOW...I'll have to study this one a little...I'll let you know tomorrow if I
was able to get it to work nicely for me.

"Gord Dibben" wrote:

John Walkenbach macro.

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 ''AutoFit
Application.StatusBar = False
End Sub

Gord Dibben MS Excel MVP

On Tue, 22 Jul 2008 09:03:02 -0700, Ray S.
wrote:

Your macro is useful...I can work with that.

"Don Guillett" wrote:

I must have misunderstood. Sample layout of what you do want. This maybe

Sub showformulainnextcell()
For Each c In Selection
c.Offset(, 1) = "'" & c.Formula
Next
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ray S." wrote in message
...
That idea is very cumbersome, especially where formulas are complex or
include complicated functions; besides, it puts both the value and the
formula in the same cell.

"Don Guillett" wrote:

=B15*B17 &"=b15*b17"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ray S." wrote in message
...
Is there some easy way to display both the values and the formulas?
I want to be able to show the results of the formulas as well as the
formulas used in obtaining the cell results. I know I can toggle back
and
forth by using Ctrl+`, but for what I want to do I now have to display
the
formulas and copy the sheet as formulas into a new worksheet, then
insert
columns and copy over the results into adjacent columns of cells. Is
there
another way of doing this?