asked on

Showing actual formula in next cell of selection.

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)

ASKER CERTIFIED SOLUTION

Arno Koster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DustinKikuchi

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

Cook09

ASKER

Akoster's is pretty simple and works.

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

Arno Koster

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

Patrick Matthews

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

Cook09

ASKER

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