On Tue, 29 May 2012 17:23:23 +0000, plotzmoney wrote:


Hello all, I was wondering if someone could please help me with a macro.
Any and all help is appreciated:

Essentially, it would be a macro where you take a cell (which has
various formulas and cell references in it) and convert those formulas
and references into their values within the formula bar into a separate
cell next to it.

For example:

Where Cell A1 = Cells (F2+F3+F4)/F5
F2 = 50
F3= 200
F4= 250
F5=100

Steps for Macro

1. I would highlight cell A1 (or if multiple cells, I would highlight
all of them)
2. I would run the macro

Macro Output

1. The macro would paste a formula into the corresponding cells directly
to the right of it. (In this case, it would be cell B1)
2. In cell B1, the cell would show a value of "5", and would show the
following formula: =(50+200+250)/100

It is simple to show the formula itself with the range.formula method.

To replace the cell references with their values becomes more complex, as you need to write a routine that differentiates cell references from other contents. This becomes even more complicated if you also want to include NAMEs.

Furthermore, you write you also want to convert "formulas" to values. Does this refer to the entire formula? or also to various formulas that might be within the cell.