Show a Formula in Another Cell

Suppose cell C1 says =A2/B2 and the resulting value is, say, 150.

Suppose in D1, I want to see the formula in C1 AS TEXT
Suppose that I want to do this for TOO many cells for me to retype with an apostrophe in front of them.

Any ideas anybody?

Hi Anne,

Here's the basics:

Code:

---

Sub convert()
Dim x    As Long
    Dim lRow  As Long
    lRow = Range("C65536").End(xlUp).Row
    For x = 1 To lRow
    If Range("C" & x).HasFormula Then
        Range("D" & x).Formula = "'" & Range("C" & x).Formula
    End If
    Next
End Sub

---

The only thing this will not do, is if the formula happens to point to the cooresponding cell in column D, column C cell will get the #VALUE error. If not, then this'll work. Haven't done any error handling for that issue yet. Thought I'd see what you had to say.

EDIT: That's not a note on the right, it's supposed to be a single quote inside double quotes. Mark's VBA tags are automatically putting a space in there :roll: (variables changes :) )

Okay. I've removed the VBA tags on it, at least until that's fixed. :)
Let's find out if it helps?

So are you also looking for error checking here Anne?

Nope. Just a simple macro, methinks. Hang tight. :)

Hi Zack,

You might want to dimension x & lrow as Long rather than Integer in case there are more than 32,768 rows

Cheers

Dave

Quote:

---

Originally Posted by Dreamboat

Suppose cell C1 says =A2/B2 and the resulting value is, say, 150.

Suppose in D1, I want to see the formula in C1 AS TEXT
Suppose that I want to do this for TOO many cells for me to retype with an apostrophe in front of them.

Any ideas anybody?

---

You cheat. ;)

You could do it with a nice UDF too:

Code:

---

Function ShowFormula(R As Range)
ShowFormula = R.Formula
End Function

---

:)

Incorporating the simple UDF idea with a user-interface (to select the range to work with and the range to post to) we could use:

Code:

---

Sub FTT()
'Converts the formulas in a range to the text of the formula
Dim rngFrom As Range, rngSC As Range, rngTo As Range, rngCell As Range
Dim lStartRow As Long, lSCRow As Long, iStartCol As Integer, iSCCol As Integer
Dim lCalc As Long
Const MessageFrom As String = "Please select the range containing the formulas:"
Const MessageTo As String = "Please select the first cell in the range to copy to:"
Const Title As String = "Formula To Text"
Const DefaultFrom As String = "Select a range"
Const DefaultTo As String = "Select a cell"
On Error Resume Next
Set rngFrom = Application.InputBox(MessageFrom, Title, DefaultFrom, , , , , 8)
Set rngSC = rngFrom.SpecialCells(xlCellTypeFormulas)
If rngSC Is Nothing Then Exit Sub
Set rngFrom = Application.Intersect(rngFrom, rngSC)
On Error GoTo 0
If rngFrom Is Nothing Then Exit Sub
'get the 'from' ranges
'use Intersect to avoid ALL SCs being used with single cell selection
With rngFrom
    lStartRow = .Rows(1).Row
    iStartCol = .Columns(1).Column
End With
Do
    On Error Resume Next
    Set rngTo = Application.InputBox(MessageTo, Title, DefaultTo, , , , , 8)
    On Error GoTo 0
    If rngTo Is Nothing Then
        Set rngFrom = Nothing
        Exit Sub
    End If
Loop Until rngTo.Count = 1
Set rngTo = rngTo.Resize(rngFrom.Rows.Count, rngFrom.Columns.Count)
'get the 'to' range
'gets a single cell and then resizes to match the 'from' range
With Application
    lCalc = .Calculation
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
For Each rngCell In rngFrom
    With rngCell
        lSCRow = .Row
        iSCCol = .Column
    End With
    rngTo.Item((lSCRow - lStartRow) + 1, (iSCCol - iStartCol) + 1).Value = _
    "'" & FormulaToText(rngCell)
Next
'use UDF to process each cell (convert formula to text)
Set rngFrom = Nothing: Set rngSC = Nothing
Set rngTo = Nothing: Set rngCell = Nothing
With Application
    If Not lCalc = 0 Then .Calculation = lCalc
    'in case lCalc not set before error occurs
    .ScreenUpdating = True
End With
End Sub

Function FormulaToText(ByRef rng As Range) As String
'convert the formula in a range into its text representation
With rng
    If Not .Count > 1 Then
        If .HasFormula Then FormulaToText = .Formula
        If .HasArray Then FormulaToText = "{" & .Formula & "}"
    End If
End With
End Function

---

Nice one Richie

I mucked around for a while trying to avoid looping through the formula range to do this conversion but no luck as of yet

Might also want to consider autofitting the columns and/or wrapping text etc. - the formulae could well be quite long.

:)

Quote:

---

You cheat. ;)

---

I'm going to continue to cheat as long as I think others might ask the same question, Ab. ;)

I want our kb to be *definitive*.

:)

Hello,

if you just want to see the formulas mentioned in column C in Column D you might think about using next macro. this puts the formula of your selected range in the column to the right of it.

Code:

---

Sub ShowFormula()
Application.ScreenUpdating = False
For Each cell In Selection
    cell.Offset(0, 1).Formula = "'" & cell.Formula
Next cell
Application.ScreenUpdating = True
End Sub

---

regards,
Jeroen

Hey, Jeroen. :)

See my signature for how to do the VBA code properly. :)
You can edit your post.

Quote:

---

Originally Posted by roos01

Hello,

if you just want to see the formulas mentioned in column C in Column D you might think about using next macro. this puts the formula of your selected range in the column to the right of it.

Code:

---

Sub ShowFormula()
Application.ScreenUpdating = False
For Each cell In Selection
    cell.Offset(0, 1).Formula = "'" & cell.Formula
Next cell
Application.ScreenUpdating = True
End Sub

---

regards,
Jeroen

---

Hi Jeroen,

Thanks for your input.

The trouble with this approach is that it will act on all cells in the selection rather than just those that contain fomulas. So we end up with text copies of everything; formulas, numbers, text. That's why some of the earlier code examples use SpecialCells or HasFormula in them. ;)

Quote:

---

Originally Posted by Dreamboat

Suppose cell C1 says =A2/B2 and the resulting value is, say, 150.

Suppose in D1, I want to see the formula in C1 AS TEXT
Suppose that I want to do this for TOO many cells for me to retype with an apostrophe in front of them.

Any ideas anybody?

---

Fastest way: download MOREFUNC (http://longre.free.fr/english/index.html)
and use the formula

Code:

---

=FORMULATEXT(C1)

---

I use it daily!!

Hi Shades,

Very nice!

We could do the same thing with a UDF:

Code:

---

Function FTEXT(f As Range)
    FTEXT = f.Formula
End Function

---

Hello Richie,
your right it shows all the values as well as text as numbers etc. perhaps next approachs works,

it shows only the formula's in the cells if the selection cells contains formula's. Hope this would do the trick.

Code:

---

Sub ShowOnlyFormula()
    Application.ScreenUpdating = False
    For Each Cell In Selection
    If Cell.HasFormula = True Then
        Cell.Offset(0, 1).Formula = "'" & Cell.Formula
    End If
    Next Cell
    Application.ScreenUpdating = True
End Sub

---

Jeroen

Hi Jeroen,

Very nice! The only thing I'd change is dim the 'cell':

Code:

---

Option Explicit
Sub ShowOnlyFormula()
    Dim cell As Range
    Application.ScreenUpdating = False
    For Each cell In Selection
    If cell.HasFormula = True Then
        cell.Offset(0, 1).Formula = "'" & cell.Formula
    End If
    Next cell
    Application.ScreenUpdating = True
End Sub

---

Just for good practice. But it works like a charm, very nice!

Hi Zack,

Thanks for the adjustments. I Still have to pay more attention on DIM's I think your post of using a function is fancier. it is shorter and simple.

here also another addaption to you function (added the check on formula)

Code:

---

Function FTEXT(f As Range)
If f.HasFormula = True Then
    FTEXT = f.Formula
Else
    FTEXT = ""
End If
End Function

---

regards,
Jeroen

LOL! You're good!

I just made a post to the KB with this:

Code:

---

=Function FTEXT(f As Range)
If f.HasFormula Then
    FTEXT = f.Formula
Else: FTEXT = f
    End If
End Function

---

I thought it was too bare-boned. Plus any numerical value would've come out as textual, and I didn't like that idea! But this seems to work ok! :)

For a less invasive appraoch, how about something like the following:

Code:

---

Sub ShowFormulas()
With ActiveWindow
    .NewWindow
    .DisplayFormulas = True
    .Parent.Windows.Arrange ArrangeStyle:=xlVertical
End With
End Sub

---

:),
Mike

You know, I just realized that the UDF I posted was first posted by Mark007! (Sorry Mark) LOL!

Quote:

---

Originally Posted by mark007

You could do it with a nice UDF too:

Code:

---

Function ShowFormula(R As Range)
ShowFormula = R.Formula
End Function

---

:)

---

The other option would be to write a comment to every cell containing a formula, and then display the comment. This then gives you an easy option to switch on and off using view/comments. Gets a bit messy with lots of contiguous cells with formulas, but no more so than anything else.

Code:

---

Sub Formulas()
Dim cel As Range
On Error Resume Next
Application.ScreenUpdating = False
For Each cel In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, 23)
    With cel
        .AddComment
        .Comment.Visible = True
        .Comment.Text Text:=.Formula
    End With
Next cel
Application.ScreenUpdating = True
End Sub

---

Regards
Ken.....................

Nice one Ken!

Here is an addition to your code that will resize the comment box also:

Code:

---

Sub Formulas()
Dim cel As Range
Dim com As String
On Error Resume Next
Application.ScreenUpdating = False
For Each cel In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, 23)
    With cel
        If cel.Comment.Text = "" Then
            .AddComment
            .Comment.Visible = True
            .Comment.Text Text:=.Formula
            .Comment.Shape.ScaleHeight 0.27, msoFalse, msoScaleFromTopLeft
        End If
    End With
Next cel
Application.ScreenUpdating = True
End Sub

---

The only thing this really won't do, is adjust if you already have a comment in a cell with a formula. Also if you have formulas in consecutive columns (on the same row) as the comment fields will most likely overlap each other.

Well, my code actually looks a lot like Zack's code, but I'll post it anyway.
For non-us users it's pracical to have the comment in their language.
This code will change all comments in selection (if any)
I think the only difference is the use of FormulaLocal and AutoSize.

Code:

---

Sub show_formula_in_comment()
Dim rCell As Range, OldText As String
Application.ScreenUpdating = False
On Error Resume Next
For Each rCell In Selection.SpecialCells(xlCellTypeFormulas, 23)
    With rCell
        .AddComment
        With .Comment
            .Text Text:=CStr(rCell.FormulaLocal)
            .Shape.TextFrame.AutoSize = True
            .Visible = True
        End With
    End With
Next
Application.ScreenUpdating = True
End Sub
 
'Sub to remove the comments
Sub RemoveComments()
    Selection.ClearComments
End Sub

---

Tommy Bak

Hello Tommy B!

Nice to see you here :)

Scott (a.k.a. ViperGTS)

Thanks, Scott.
Just found this place today (thanks to shades :yes )and it really looks nice.

regards
Tommy Bak

Just another idea on a theme.
(pinched the .HasArray & .FormulaLocal from the previous posts)
Using DataValidation Input Message via WorksheetChange Event:

Code:

---

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim c As Range, frm As String, but As String
Application.EnableEvents = False
    On Error Resume Next
    With Target
    .SpecialCells(xlCellTypeBlanks).Validation.Delete 'remove for blanks
    .SpecialCells(xlCellTypeConstants).Validation.Delete 'remove if not a formula any more
    For Each c In .SpecialCells(xlCellTypeFormulas)
        but = vbNullString
        With c
            frm = .FormulaLocal
            If .HasArray Then frm = "{" & .FormulaLocal & "}"
            If Len(frm) > 254 Then but = " [too long]"
            frm = Left(frm, 254)
            With .Validation
                .Delete
                .Add Type:=xlValidateInputOnly
                .InputTitle = "Formula in " & c.Address & but
                .InputMessage = frm
                .ShowInput = True
            End With
        End With
    Next
End With
Application.EnableEvents = True
End Sub

---

Positives:
1. Automated via Change Event
2. Can drag the message anywhere on the screen & it stays there (eg. under formula bar to veiw whilst editing).
3. If (God forbid) you use the Office Assistant it will display the formula in a larger font in its balloon.
4. Will work as soon as formula is entered, with Copy & Paste, drag&drop - no need to run a macro.

Negatives:
1. Deletes any validation you may have (could be modified to at least reapply all but Input message)
2. Formulas longer than 255 characters will be chopped.
3. Excel will crash in xl97 if you Copy & Paste or Drag & Drop an array formula to more than 1 cell.

just out of curiosity what does the "23" really stand for in the formulas above?

(i.e. Selection.SpecialCells(xlCellTypeFormulas, 23)

I understand it's a value but where is that value derived from?

Sorry for digging up an older post but doing a search does that. :rolleyes:

Microsoft created it, it's a numeric constant in c-code, it's sitting on your hard drive (most likely). 23 refers to a combination all 4 value types (constants) of Special Cells -> Formulas. Those being:

xlErrors, xlLogical, xlNumbers, xlNumbers.

All of which are early bound references numeric, constant arguments. E.g., xlNumbers = 1.

23 is actually the sum of all four numeric constants:

Errors = 16
Logical = 4
Numbers = 1
Text = 2

There's only one argument to be passed. 23 is moot in this case as the argument is both optional and 23 is the value by default unless stated otherwise.

You may also want to refer to the SpecialCells Method in the VBE help file.