How in to parse constants in formula to cells
 

On 1 Dec 2006 19:25:00 -0800, "Dennis" wrote:

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

Well, you can retain the "-" by merely changing the parameters in the Pattern
and RegExp.Replace functions also.

But, whatever works for you.
--ron

How in to parse constants in formula to cells
 

Ron, if you would, please indicate exactly the changes that you
suggest. I really did try many different settings but to no avail.
What I do not like about (me) doing a work-around, is that I lose the
chance to learn a new concept or approach.

I tried -?\b64596792\b -?\b-64596792\b \b-64596792\b
b-64596792\b etc.

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"
(Should I have tried "-" & NumToReplace & "\b")

FormulaText = objRegExp.Replace(FormulaText, "$1" & ReplWith)"
(What does the "$1" represent? - is it any of "([=-+/*])"

Thanks, Dennis

Ron Rosenfeld wrote:
On 1 Dec 2006 19:25:00 -0800, "Dennis" wrote:

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

Well, you can retain the "-" by merely changing the parameters in the Pattern
and RegExp.Replace functions also.

But, whatever works for you.
--ron

How in to parse constants in formula to cells
 

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

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

How in to parse constants in formula to cells
 

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

How in to parse constants in formula to cells
 

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

How in to parse constants in formula to cells
 

On Sun, 03 Dec 2006 17:01:05 GMT, wrote:

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

Thanks. It seems as if you have things well in hand, now. But take a look at
the Regex references I cited in a previous message. They will be very helpful.
--ron

How in to parse constants in formula to cells
 

FYI,

I have compiled all of your explanations into a single cohesive
document that I keep as a file on my Memory Stick.

Dennis


Ron Rosenfeld wrote:
On Sun, 03 Dec 2006 17:01:05 GMT, wrote:

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

Thanks. It seems as if you have things well in hand, now. But take a look at
the Regex references I cited in a previous message. They will be very helpful.
--ron

How in to parse constants in formula to cells
 

Ron,

As it turned out the code is fine. I ran into an issue where Excel was
assuming what I wanted stored information to be.

Specifically, I had sheetnames "Jan06", Feb06 etc. I transferred the
sheet names to another worksheet's cells. When done, the cell
information became 1-06.

When I wanted to compare information and/or "GoTo" those sheets,
Jan06 is very different from 1-06.

Therefore, to force Numbers to Text or Date to Text, I concatenated
Range("B2").Value = "'" & Range("A1").Text or
Range("B2").Value = "'" & Activesheet.name
to force Excel to interpret that Jan06 as Jan06 and not 1-06

All is well,

Thanks Dennis






Ron Rosenfeld wrote:
On Sun, 03 Dec 2006 17:01:05 GMT, wrote:

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

Thanks. It seems as if you have things well in hand, now. But take a look at
the Regex references I cited in a previous message. They will be very helpful.
--ron

How in to parse constants in formula to cells
 

Ron,

Sorry if I wasted your time, I really thought that the replacement was
not made because of the handling of the 20% but it failed because of
the sheetname as explained above.

Dennis

Ron Rosenfeld wrote:
On Sun, 03 Dec 2006 17:01:05 GMT, wrote:

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

Thanks. It seems as if you have things well in hand, now. But take a look at
the Regex references I cited in a previous message. They will be very helpful.
--ron

How in to parse constants in formula to cells
 

On 13 Dec 2006 11:34:37 -0800, "Dennis" wrote:

Ron,

Your's and my code fails to handle the following formula:
=(D3*20%) The problem being the "20%"

As per above, in this thread, I want to replace "20%" with a link to a
cell on another w/s, on which, is posted 20% or .20 or whatever else
works.

Any suggestions how to change the code to handle this issue.


I think all you need to do is add the "%" to the description of the number as
an optional ending:


Const NumConstant As String = "-?(\d*\.)?\d+%?"

At least, it works in my code.

That would return 20% as text in your example. You may need to change it to a
value, depending on how you are inputting your constants.

For example:

================================================
'Output for testing, but could go into any range
i = 1
For Each objMatch In colMatches
c.Offset(0, i).Value = Evaluate(Replace(objMatch, "%", "/100"))
i = i + 1
Next objMatch

Next c
============================================

or, perhaps:

==========================================
'Output for testing, but could go into any range
i = 1
For Each objMatch In colMatches
sStr = objMatch
c.Offset(0, i).Value = Evaluate(sStr)
i = i + 1
Next objMatch

Next c
========================================
--ron

How in to parse constants in formula to cells
 

On 14 Dec 2006 10:36:41 -0800, "Dennis" wrote:

Ron,

Sorry if I wasted your time, I really thought that the replacement was
not made because of the handling of the 20% but it failed because of
the sheetname as explained above.

Dennis

Never a waste of time to get me thinking about something else. And who knows,
it may come in handy in another application.

Best wishes,
--ron