# Convert Excel formulas containing functions to a simple form

##### New Member
Hello Everyone,

I am a beginner with VBA. I want write a macro to convert a formula which contains functions to a simple form. e.g. D12+SUM(E22:E26) should be converted to D12+E22-E26 or D12+SUM(E22:E26)-SUM(F12:F15)-D20+SUM(G12:G14) should be converted to D12+E22+E23+E24+E25+E26-F12+F13+F14+F15-D20+G12+G13+G14.

I have been trying a lot but no success......Can someone please help me out.......or if someone has already done such sort of thing?

Any help would be highly appreciated.

Thanks

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### ShawnPCooke

##### Board Regular
Here are some possibilities for you.

1) First, you have to parse the existing formula. I assume you know that you can get to the formula in a cell using the Range.Formula property. Assign that to a text value and chop off the equal sign. Next, scan through for the operators + and -. (I'm assuming there is no multiplication or division in any of this?) Load everything between those operators into an array of strings, and the operators into a different array. (Assume the first operator is positive.)

So for example:

=SUM(E6:E10)+F10-SUM(D10:D14)

This would yield two arrays:

{SUM(E6:E10),F10,SUM(D10:D14)}
{+,+,-}

2) Now, you want to look through that first array to expand it out, if necessary. I'm just going to discuss expanding SUM, but it would work similarly if you have other functions you need to expand.

First, search the string to see if the term SUM is found. If not, assume it cannot be decomposed, and move on to the next string in the array.

If you did find it, then get rid of the SUM( at the beginning and the ) at the end, and define a variable of type Range using the text left over as the range.

Now, use a For Each/Next statement to iterate through each cell in that range. For that cell, use the Range.Address property to get the text of that cell address. You can specify whether you want to use relative or absolute cell reference.

Start assembling a new string based on the operator from the second array (the + or -) and each cell reference returned. You would wind up with "+E6+E7+E8+E9+E10", for example.

3) Once you've done that for all of the elements of your array, simply concatenate them together. Make sure that no operators get duplicated in this, but if you're careful it ought to work.

##### New Member
Hi Shawn,

Thanks for your suggestions. I will give it a try and then get back to you guys.

Anyone else here with other ideas?

Replies
1
Views
157
Replies
3
Views
62
Replies
0
Views
54
Replies
3
Views
69
Replies
5
Views
47

1,109,434
Messages
5,528,742
Members
409,832
Latest member
Misspears10

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...