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