Home |
Search |
Today's Posts |
#41
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#42
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#43
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#44
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#45
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
|
#47
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
|
#48
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#49
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#50
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#51
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#52
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula constants? | Excel Worksheet Functions | |||
Copying formula cells that really ought to be constants? | Excel Programming | |||
How do I capitalize and parse in the same formula? | Excel Worksheet Functions | |||
How do you copy a formula without incrementing some constants? | Excel Worksheet Functions | |||
Parse formula | Excel Programming |