Showing actual formula in next cell of selection.

Posted on 2012-08-14
Last Modified: 2012-08-14
How would I write the code that will copy the actual formula of a selection into adjacent cells or the next cells to the right of a formula result.  Right now, I have to place a ' in the cell, manually copy the formula, and then paste it into the next cell.  There could just be one or a selection of 10, but they will all be placed into the next cell.  I will also change the color of the formula, but I can add that to the end of the Sub.

    C                D                         E                                         F
Name 1      Name 2      Concatenated Text               Formula      
Alan           Jones               AlanJones            =CONCATENATE(C6,D6)
Question by:Cook09
    LVL 19

    Accepted Solution

    you could use macro code:

    Sub write_out_selected_formula_to_next_cell()
        Selection.Offset(0, 1).NumberFormat = "@"
        Selection.Offset(0, 1).Value = Selection.Formula
    End Sub

    Open in new window

    LVL 4

    Expert Comment

    Quick little user-defined VBA function that should do as you're asking:
    Function GetFormula(Cell as Range) as String
       GetFormula = Cell.Formula
    End Function

    Open in new window

    Courtesy of:

    Author Comment

    Akoster's is pretty simple and works.

    The Link is very impressive.  How would one easily use this Function?
    LVL 19

    Expert Comment


    to use custom VBA functions as part of a regular excel cell formula, add a module to the workbook inside the vba editor.
    post the function inside of this module

    you can then enter the formula in say cell C3 to display the formula inside cell C2:


    Open in new window

    LVL 92

    Expert Comment

    by:Patrick Matthews
    As an aside, in Excel 2013, there is a new function FORMULATEXT that will do this without VBA:

    Author Closing Comment

    I plugged it in and added a link to my QAT and it's performing like I had it in mind.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now