Is there a way in VBA to compare two formula strings should be the same aside
from the fact that rows have been insterted. For example. Lets say

OriginalString = SUM(B1:B4)*B6+B8

Now lets say I know that a Row was inserted at Row 1:
RevisedString = SUM(B2:B5)*B7+B9

Now assume these two formulas return the same value. I want to be able to
write a function which does the following:

CompareStrings (OriginalString, RevisedString).

I only want to test the formula string:

SUM(B1:B4)*B6+B8 = SUM(B2:B5)*B7+B9

I need to adjust B9 in the second term to reflect that it is really B8 + 1
row. I need a formula parser that subtracts a row value from all rows
greater than 8+1 in the second string.

Any ideas

Thanks
EM

could you provide an example of when you would need to make such a comparison?

"ExcelMonkey" wrote:

> Is there a way in VBA to compare two formula strings should be the same aside
> from the fact that rows have been insterted. For example. Lets say
>
> OriginalString = SUM(B1:B4)*B6+B8
>
> Now lets say I know that a Row was inserted at Row 1:
> RevisedString = SUM(B2:B5)*B7+B9
>
> Now assume these two formulas return the same value. I want to be able to
> write a function which does the following:
>
> CompareStrings (OriginalString, RevisedString).
>
> I only want to test the formula string:
>
> SUM(B1:B4)*B6+B8 = SUM(B2:B5)*B7+B9
>
> I need to adjust B9 in the second term to reflect that it is really B8 + 1
> row. I need a formula parser that subtracts a row value from all rows
> greater than 8+1 in the second string.
>
> Any ideas
>
> Thanks
> EM
>

I am putting together a file compare routine with the aim of incorporating
logic to account for inserted rows/columns. First step is assessing if
anyting has been inserted. Second step is to compare the two sets of formula
strings. Most of the file compare logic I have seen does not account for
inserted rows/columns.

Thanks

EM

"JLGWhiz" wrote:

> could you provide an example of when you would need to make such a comparison?
>
> "ExcelMonkey" wrote:
>
> > Is there a way in VBA to compare two formula strings should be the same aside
> > from the fact that rows have been insterted. For example. Lets say
> >
> > OriginalString = SUM(B1:B4)*B6+B8
> >
> > Now lets say I know that a Row was inserted at Row 1:
> > RevisedString = SUM(B2:B5)*B7+B9
> >
> > Now assume these two formulas return the same value. I want to be able to
> > write a function which does the following:
> >
> > CompareStrings (OriginalString, RevisedString).
> >
> > I only want to test the formula string:
> >
> > SUM(B1:B4)*B6+B8 = SUM(B2:B5)*B7+B9
> >
> > I need to adjust B9 in the second term to reflect that it is really B8 + 1
> > row. I need a formula parser that subtracts a row value from all rows
> > greater than 8+1 in the second string.
> >
> > Any ideas
> >
> > Thanks
> > EM
> >

em -
to see if anything's been inserted, have an auto_open sub that counts
the rows BEFORE anything is done and assign it to a variable. then
you can use that variable to check later if anything's been added.

if you need to check it for if somebody ELSE inserted a row, you could
have a hidden spreadsheet in which you manually enter the # of rows
used at the end of your session, and then the macro compares the
current number of rows to the stored number of rows.

can do the same thing for columns.

just ideas


susan


On May 7, 3:01 pm, ExcelMonkey <ExcelMon...@discussions.microsoft.com>
wrote:
> I am putting together a file compare routine with the aim of incorporating
> logic to account for inserted rows/columns. First step is assessing if
> anyting has been inserted. Second step is to compare the two sets of formula
> strings. Most of the file compare logic I have seen does not account for
> inserted rows/columns.
>
> Thanks
>
> EM
>
>
>
> "JLGWhiz" wrote:
> > could you provide an example of when you would need to make such a comparison?
>
> > "ExcelMonkey" wrote:
>
> > > Is there a way in VBA to compare two formula strings should be the same aside
> > > from the fact that rows have been insterted. For example. Lets say
>
> > > OriginalString = SUM(B1:B4)*B6+B8
>
> > > Now lets say I know that a Row was inserted at Row 1:
> > > RevisedString = SUM(B2:B5)*B7+B9
>
> > > Now assume these two formulas return the same value. I want to be able to
> > > write a function which does the following:
>
> > > CompareStrings (OriginalString, RevisedString).
>
> > > I only want to test the formula string:
>
> > > SUM(B1:B4)*B6+B8 = SUM(B2:B5)*B7+B9
>
> > > I need to adjust B9 in the second term to reflect that it is really B8 + 1
> > > row. I need a formula parser that subtracts a row value from all rows
> > > greater than 8+1 in the second string.
>
> > > Any ideas
>
> > > Thanks
> > > EM- Hide quoted text -
>
> - Show quoted text -