How in to parse constants in formula to cells
Ron,
The process is to remove constants from formulas and substituting a w/s reference in each constant's
place. The reference is to a w/s of constants. On this w/s the constants can then be fully
described and labeled. In addition, many constants in my OP's w/s are the same number throughout
many w/s. Therefore, a change on the "Constants Input" w/s will change all relevant w/s's.
The purpose is to compare formula's for changes each month. Currently the OPs change formulas (
the constant's values) for each month throughout the year.
With the routine, the formulas (the "engine") remain the same - only the cell values change.
I hope that I was clear.
BTW, I forgot that I added the "=" in the "([=-+/*])" string thinking the change "made sense." Being
a newbe causes problems.
Be aware that your assistance really helped.
Dennis
Ron Rosenfeld wrote:
On 2 Dec 2006 07:39:28 -0800, "Dennis" wrote:
Now I realize that I may have avoided your earlier point because I did
not understand what it was doing and, at that time, I had an immediate
project to complete and I thought I'll learn new concepts later. I
just could not take a chance that numerous others' worksheets
(formulas) would be changed incorrectly. Your comments we
If that doesn't work, and you must test for ONLY operators, then
change two lines:
Pattern = "([=-+/*])" & NumToReplace & "\b"
I don't believe I ever posted that sort of Pattern. In particular, [=-+/*] is
not a legal syntax. The "-" means something very different in this context.
what I posted was
Pattern = "([-+/*])" & NumToReplace & "\b"
If you want to add an equal sign to the list of operators, the "-" must still
be FIRST inside the brackets.
(Should I have tried "-" & NumToReplace & "\b")
FormulaText = objRegExp.Replace(FormulaText, "$1" & ReplWith)"
(What does the "$1" represent? - is it any of "([=-+/*])"
See below for explanation:
If you have your routine working, then all is fine.
But I am somewhat confused as to your precise specifications with regard to the
"-".
Initially, it appeared as if you were just parsing out signed numbers.
But in your latest example, you were substituting a cell reference for a signed
number.
The same solution will not work for both, because of the removal of the "-",
which is functioning as an operator in the equation.
(Copied from a previous post of mine; perhaps you did not see this?):
---------------------------------------
You could test for just the number without the sign:
Pattern = "\b64596792\b"
Original -'C:\Acctg\FIT\[Accum Temp.xls]2005'!$A$1-64596792+59410246
Replaced -'C:\Acctg\FIT\[Accum Temp.xls]2005'!$A$1-'Constants
Input'!$D$245+59410246
OR, if there is some reason you must have that "-" in the expression, you could
capture it and return it in the ReplaceWith string:
Pattern = "(-?)\b64596792\b"
' Note the parentheses around the -? to "capture" it.
FormulaText = objRegExp.Replace(FormulaText, "$1" & ReplaceWith)
' the "$1" represents the first captured item.
Original -'C:\Acctg\FIT\[Accum Temp.xls]2005'!$A$1-64596792+59410246
Replaced -'C:\Acctg\FIT\[Accum Temp.xls]2005'!$A$1-'Constants
Input'!$D$245+59410246
If the value in 'Constants Input'!$D$245 is a negative number, then you'd want
to replace the "-" with a "+", or change the sign of that cell.
--ron
|