Solved

# Showing actual formula in next cell of selection.

Posted on 2012-08-14
Medium Priority
776 Views
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.

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

LVL 19

Accepted Solution

Arno Koster earned 2000 total points
ID: 38292372
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
``````
0

LVL 4

Expert Comment

ID: 38292378
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
``````

Courtesy of: http://dmcritchie.mvps.org/excel/formula.htm
0

Author Comment

ID: 38292486
Akoster's is pretty simple and works.

DustinKikuchi:
The Link is very impressive.  How would one easily use this Function?
0

LVL 19

Expert Comment

ID: 38292575
Author,

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:

``````=GetFormula(C2)
``````
0

LVL 93

Expert Comment

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

http://office.microsoft.com/en-us/excel-help/formulatext-function-HA102753280.aspx
0

Author Closing Comment

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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
###### Suggested Courses
Course of the Month17 days, 8 hours left to enroll