Ron,

In the example, the minus sign is not transferred.

In the interim, I use an input box and flip the sign to positive.
Then, the following:

ReplaceWith = Replace(FormulaText, "=", "```")

If (objRegExp.Test(FormulaText) = True) Then
TempText = objRegExp.Replace(FormulaText, ReplaceWith)
TempText = IIf(NumbToReplace < 0, Replace(TempText, "-```", "+"), _
Replace(TempText, "```", "+"))
ActiveCell.formula = TempText
End if

Thanks for you knowledge and time.

Dennis

Ron Rosenfeld wrote:
On 1 Dec 2006 14:23:53 -0800, "Dennis" wrote:

Yep,

My real name is Dennis. I use EagleOne for all the spamers.

Ron, I have constructed the suggested pattern: -?\b64596792\b for the
negative number 64596792 and it still will not replace.

I also tried ?\b64596792\b. Does something else have to change?

Actually, given the data you posted, in which the negative number is not the
first number, either Pattern will work because there is a \b prior to the "-".

This Sub seems to replicate what you have posted for data, and works to do the
substitution.

==================================================
Option Explicit
Sub ReplaceNeg()
Dim FormulaText As String
Dim Pattern As String
Dim ReplaceWith As String

Dim objRegExp As Object

Set objRegExp = New RegExp

FormulaText = "-'C:\Acctg\FIT\[Accum Temp.xls]2005'!$A$1-64596792+59410246"
Pattern = "b-64596792\b"

objRegExp.Pattern = Pattern

'ReplaceWith = Replace(MyCell.Offset(0, 3).Formula, "=", "")
ReplaceWith = "'Constants Input'!$D$245"

If (objRegExp.Test(FormulaText) = True) Then 'Does Pass as True but:
Debug.Print "Original", FormulaText

FormulaText = objRegExp.Replace(FormulaText, ReplaceWith)
Debug.Print "Replaced", FormulaText

End If

End Sub
===========================

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

=============================

Your negative number is replaced by ReplaceWith.

Of course, this demonstrates the issue of replacing a signed negative number
with some other constant. You have stated that you want the signed number.
But the "-" is really an operator. So when you do the replacement with a cell
reference, there is no operator, and the result has a flawed syntax.

If you want to retain the "-" in the replacement, you need to do things a bit
differently.

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