Your point about "-" is well taken. I appreciate your insistence that I really listen and
understand your point.

When I began, I had no idea what you meant by regular expressions. You made me a real believer in
Regular Expressions - Regex to me now

Dennis


Ron Rosenfeld wrote:

On Sat, 02 Dec 2006 23:28:46 GMT, wrote:

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.

That makes sense.

But I don't understand why you can't just ignore the "-". In other words, why
can't you treat the "-" as an operator rather than as denoting the sign of the
constant?

If the reason has to do with how you generate "Pattern", like from your Input
Box, you could just strip it off by using the ABS function:

=======================
Do While NumbToReplace = 0
NumbToReplace = ABS(InputBox("Enter number to _
replace", "Replace Entry Box"))
If NumbToReplace = 0 Then
MsgBox "No Number Entered ... Start Over"
End If
Loop
Pattern = "\b" & NumbToReplace & "\b"
objRegExp.Pattern = Pattern
==============================

If you need to specify that NumbToReplace must follow an operator or an "="
sign or a comma (for example), then:


Pattern = "([-*/+=,])" & NumbToReplace & "\b"

Note that the Character Class containing the operators and other characters is
enclosed in parentheses. That will be "captured" and can be referenced in the
Replace statement, so the same operator is also replaced:

e.g.:

FormulaText = objRegExp.Replace(FormulaText, "$1" & ReplaceWith)




BTW, I forgot that I added the "=" in the "([=-+/*])" string thinking the change "made sense." Being
a newbe causes problems.

Well, in a regular expression, within a Character Class, the hyphen, unless it
is at the beginning, specifies a range of characters. So [A-Z] means all the
capital letters. [0-9] means all the digits. When you construct [=-+/*] that
means all the characters from "=" to "+". If you want to specify all the
operators plus an "=", you must put the "-" first: [-=+/*].

Be aware that your assistance really helped.

Dennis



Best,
--ron