Showing Formulas

R

Ripper

I know how to show the formulas for the entire sheet (ctrl + `

Is there a way to show the formulas for a selected portion of the sheet

Thanks As Always
RIP
 
J

Jason Morin

You could select the range and then run a simple macro
like:

Sub ShowMyFormulas()

Dim cell As Range

For Each cell In Selection
If cell.HasFormula Then
With cell
.Formula = Chr(39) & .Formula
End With
End If
Next cell

End Sub

--
To run, press ALT+F11, Insert > Module, and paste this
code in. Then run the macro.

To get the formulas back, try:

Um, still working on that ;)

HTH
Jason
Atlanta, GA
 
D

David McRitchie

Hi RIP,
Yes there is see my http://www.mvps.org/dmcritchie/excel/formula.htm
page. To show individual formulas in another cell use User Defined Formulas
=personal.xls!GetFormulaD(A8)

To see the formulas in a small range in a MsBox see
Display Formula in a MsgBox (#formulabox)
http://www.mvps.org/dmcritchie/excel/formula.htm#formulabox

There is a limit to how many characters can appear in a formulabox
so you will have to choose fairly small ranges. This will
formula, and number format -- you could also show .value or .text
if you also want to see the value or the entry as displayed in the cell.

If not familiar with installikng and using Macros see my page
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
J

Jason Morin

With a little help from MVP Vasant Nanavati, I finally got
it. You can undo the macro ShowMyFormulas with:

Sub BringMyFormulasBack()

Dim cell As Range
Dim oldfrmla As String

For Each cell In Selection
oldfrmla = cell.Text
If Left(oldfrmla, 1) = "=" Then
cell.Formula = Evaluate(oldfrmla)
End If
Next cell

End Sub
 
Top