Convert Excel formulas containing functions to a simple form

syedalisajjad

New Member
Joined
Sep 11, 2014
Messages
13
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
 

Some videos you may like

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
Joined
May 14, 2007
Messages
151
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.
 

syedalisajjad

New Member
Joined
Sep 11, 2014
Messages
13
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?
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top