?
Solved

Showing actual formula in next cell of selection.

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

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

Accepted Solution

by:
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

Open in new window

0
 
LVL 4

Expert Comment

by:DustinKikuchi
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

Open in new window


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

Author Comment

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

DustinKikuchi:
The Link is very impressive.  How would one easily use this Function?
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 19

Expert Comment

by:Arno Koster
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)

Open in new window

0
 
LVL 93

Expert Comment

by:Patrick Matthews
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

by:Cook09
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…

829 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