Show a Formula in Another Cell

[Anne Troy]

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?

[Zack Barresse]

Hi Anne,

Here's the basics:

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 (variables changes )

[Anne Troy]

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

[Zack Barresse]

So are you also looking for error checking here Anne?

[Anne Troy]

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

[brettdj]

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

[SJ McAbney]

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.

[mark007]

You could do it with a nice UDF too:

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

[Richie(UK)]

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:

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

[brettdj]

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

[mark007]

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

[Anne Troy]

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*.

[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.

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

[Anne Troy]

Hey, Jeroen.

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

[Richie(UK)]

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.

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.

[shades]

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

=FORMULATEXT(C1)

I use it daily!!

[Zack Barresse]

Hi Shades,

Very nice!

We could do the same thing with a UDF:

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

[roos01]

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.

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

[Zack Barresse]

Hi Jeroen,

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

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!

[roos01]

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)

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

regards,
Jeroen