Displaying BOTH formulas and values

[Ray S.]

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?

 

[Don Guillett]

=B15*B17 &"=b15*b17"

 

[Ray S.]

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]

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

 

[Ray S.]

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

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
(e-mail address removed)

 

[Gord Dibben]

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

 

[Ray S.]

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.