Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 31

Thread: Show a Formula in Another Cell

  1. #1
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location

    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?
    ~Anne Troy

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    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 )
    Last edited by Zack Barresse; 06-17-2004 at 08:34 AM. Reason: Tags now fixed...

  3. #3
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Okay. I've removed the VBA tags on it, at least until that's fixed.
    Let's find out if it helps?
    ~Anne Troy

  4. #4
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    So are you also looking for error checking here Anne?

  5. #5
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Nope. Just a simple macro, methinks. Hang tight.
    ~Anne Troy

  6. #6
    VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    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

  7. #7
    VBAX Tutor SJ McAbney's Avatar
    Joined
    May 2004
    Location
    Glasgow
    Posts
    243
    Location
    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.

  8. #8
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    620
    Location
    You could do it with a nice UDF too:

    Function ShowFormula(R As Range)
    ShowFormula = R.Formula
    End Function
    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  9. #9
    VBAX Contributor Richie(UK)'s Avatar
    Joined
    May 2004
    Location
    UK
    Posts
    188
    Location
    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

  10. #10
    VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    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

  11. #11
    BoardCoder
    Licensed Coder VBAX Expert mark007's Avatar
    Joined
    May 2004
    Location
    Leeds, UK
    Posts
    620
    Location
    Might also want to consider autofitting the columns and/or wrapping text etc. - the formulae could well be quite long.

    "Computers are useless. They can only give you answers." - Pablo Picasso
    Mark Rowlinson FIA | The Code Net

  12. #12
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    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*.

    ~Anne Troy

  13. #13
    VBAX Regular
    Joined
    Jun 2004
    Location
    The Netherlands
    Posts
    34
    Location
    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
    Last edited by roos01; 06-18-2004 at 07:19 AM.

  14. #14
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Hey, Jeroen.

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

  15. #15
    VBAX Contributor Richie(UK)'s Avatar
    Joined
    May 2004
    Location
    UK
    Posts
    188
    Location
    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.

    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.

  16. #16
    Mac Moderator VBAX Expert shades's Avatar
    Joined
    May 2004
    Location
    Kansas City, USA
    Posts
    638
    Location
    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

    =FORMULATEXT(C1)
    I use it daily!!

    Software: LibreOffice 3.3 on Mac OS X 10.6.5
    (retired Excel 2003 user, 3.28.2008 )
    Humanware: Older than dirt
    --------------------
    old, slow, and confused
    but at least I'm inconsistent!

    Rich

  17. #17
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Hi Shades,

    Very nice!

    We could do the same thing with a UDF:

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

  18. #18
    VBAX Regular
    Joined
    Jun 2004
    Location
    The Netherlands
    Posts
    34
    Location
    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

  19. #19
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    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!

  20. #20
    VBAX Regular
    Joined
    Jun 2004
    Location
    The Netherlands
    Posts
    34
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •