Hi,

I am looking to code a macro to seperate a formula by operands and
evaluate each portion individually. I am familar with programming but
not with VBA (I have a good idea of the algorithm but not enough
knowledge of VBA syntax to code). I'm hoping someone here can help

Basically what I want to do is to split a formula using the operand as
delimiter and evaluating and resubstituting the results back to the
original cell.

Example if a formula in C1 contained

=VLOOKUP(A1, RangeA, 2, FALSE) * VLOOKUP(B1, RangeB, 2, FALSE)

lets say the first portion of this VLOOKUP (on range A) returns the
number 2 and the second portion returns the number 3. I was hoping a
macro could turn this cell into

= 2 * 3

Obviously an escape character (') will be required so 2 * 3 is not
evaluated to 6.

The macro would have to work with all sorts of formulas with or without
brackets.

The alogrithm I had in mind is

LOOP worksheet for formula cells

in each formula cell cast the content of the formula as a string and
loop characters until a operand (+, -, *, /) is reached (keeping track
of the number of brackets).

If the number of "(" is equal to the number of ")" then the formula can
be evaluated in a different cell and the result substituted back to the
original formula.

If the number of "(" is not equal to the number of ")" remove the outer
")" until they are equal.

Repeat until the end of the formula is reached

Then add (') to the start of the string to prevent excel from
performing a calculation.


I know this is a big ask but does anyone have any ideas on whether this
is achievable and how I should code this?

Thanks in advanced


--
qwertyjuan
------------------------------------------------------------------------
qwertyjuan's Profile: http://www.excelforum.com/member.php...o&userid=28902
View this thread: http://www.excelforum.com/showthread...hreadid=486483