How in to parse constants in formula to cells

[Dennis]

I could not wait!

How do I handle:

Const NumToReplace = 2
Const ReplWith = 4

These need to be variables determined in subsequent code.

So do I:

Const NumToReplace As Double (needs "=" to something)
Const ReplWith As Double (needs "=" to something)

-or-

Dim NumToReplace as String (or Double or Long)
Dim NumToReplace as String (or Double or Long)

EagleOne

After work today, I'll take a look at the code you sent.

Your help has been a God-send for me this week.

Eagle-one

Ron,

If you are still in the mood. Attached is some code that I am
currently using for a huge project and the deadline is tomorrow..

The code is part of the same project with which you helped me earlier.
Here is my whine with cheese.

The code works fine until I attempt to replace i.e. a "2" in formulas
where there are multiple "2's". a specific example is: =4237987/2 or
=122335/2. What I want to replace is the 2 after the operator "/"
(where the 2 is the ENTIRE NUMBER after ANY operator. I think that
those parameters will keep me out of trouble.

Currently, I trap that error and "mark" the cell for manual followup
later.

Would you please help me to devise

Well, the simplest way (for me) to do that sort of replacement is by using
Regular Expressions. The routines work quicker if you set a reference to
Microsoft VBScrip REgular Expressions 5.5 (See Tools/References), but you can
also use the CreateObject method to set his within the script.

The "key" as to what is going to be replaced is in how you define "Pattern".
As set up, the "\b" parameter refers to any word boundary, which could be an
operator, comma, or any character that is not alphanumeric, or the beginning or
end of the string.

If that doesn't work, and you must test for ONLY operators, then change two
lines:

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

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

================================
Option Explicit

Sub replConstant()
Dim FormulaText As String
Const NumToReplace = 2
Const ReplWith = 4
Dim Pattern As String

Dim objRegExp As RegExp
Dim objMatch As Match
Dim colMatches As MatchCollection

Pattern = "\b" & NumToReplace & "\b"
FormulaText = Selection.Formula

' Create a regular expression object.
Set objRegExp = New RegExp

'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern

' Set Case Insensitivity.
objRegExp.IgnoreCase = True

'Set global applicability.
objRegExp.Global = True

'Test whether the String can be compared.
If (objRegExp.Test(FormulaText) = True) Then

'Do the replacement
FormulaText = objRegExp.Replace(FormulaText, ReplWith)

Debug.Print FormulaText

End If

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

Hope this helps


--ron

 

[Ron Rosenfeld]

I could not wait!

How do I handle:

Const NumToReplace = 2
Const ReplWith = 4

These need to be variables determined in subsequent code.

So do I:

Const NumToReplace As Double (needs "=" to something)
Const ReplWith As Double (needs "=" to something)

-or-

Dim NumToReplace as String (or Double or Long)
Dim NumToReplace as String (or Double or Long)

EagleOne

Well, I didn't know how you were planning to input those variables, so I just
put them in as Constants for testing purposes. You can Dim them and then set
them to whatever, however you wish.

With the Const statement, the type declaration is optional. But if the values
were always going to be integers, I would use the Long type; if they might be
decimal numbers, I would use Double or String.


--ron

 

[Dennis]

Intrigued by your code, I just said the hell with it and just
experimented. Then walla! I got to your answer at 2:15PM EST.

As you can tell, I am "new" to this. Even though I know the difference
between a constant and variable, I did not realize that Const was just
short for Constant begging Public & Private issues. I even thought it
was short for Construct what ever that is. Just making mountains out of
mole hills when 20/20 hindsight is available.

Thanks again

EagleOne

 

[Ron Rosenfeld]

Intrigued by your code, I just said the hell with it and just
experimented. Then walla! I got to your answer at 2:15PM EST.

As you can tell, I am "new" to this. Even though I know the difference
between a constant and variable, I did not realize that Const was just
short for Constant begging Public & Private issues. I even thought it
was short for Construct what ever that is. Just making mountains out of
mole hills when 20/20 hindsight is available.

Thanks again

EagleOne

When you have a word in a module that requires further explanation, just put
your cursor in the word, and hit F1. HELP should open to explain it. That
will work for the standard VBA stuff; it doesn't work for the Regular
Expression stuff, though.

Best,
--ron

 

[Dennis]

Ron, (or anyone with a desire the help who knows RegEx)

All works very well except that negative numbers are not actually being
replaced. Positive amounts are being replaced as expected.
************************************************
FormulaText = ActiveCell.Formula
ReplaceWith = Replace(MyCell.Offset(0, 3).Formula, "=", "")

If (objRegExp.Test(FormulaText) = True) 'Does Pass as True but:

ActiveCell.Formula = objRegExp.Replace(FormulaText, ReplaceWith)
'Does not actually do the replace when the Pattern is negative
************************************************
Next are the variables at that point

ReplaceWith = 'Constants Input'!$D$245
Pattern = \b-64596792\b
FormulaText =-'C:\Acctg\FIT\[Accum Temp.xls]2005'!$A$1 _
-64596792+59410246

If the pattern is \b64596792\b then the replacement is made.

Other examples of negative numbers not replaced (I have re-spaced for
clarity)

= -9516836788 -703165.07
='Constants Input'!$D$8 -575002+ 'Constants Input'!$D$10
= -821377172+ -4030801

My Full code is next:


***************************************************
Dim MyCell As Range
Dim myRange As Range
Dim Filter1 As Range
Dim FilterCriteria As String
Dim FormulaText As String
Dim NumbToReplace As Double
Dim ReplaceWith As String
Dim Pattern As String
Dim objRegExp As RegExp
Dim objMatch As match
Dim colMatches As MatchCollection

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
.CalculateBeforeSave = False
End With

Set objRegExp = New RegExp

objRegExp.IgnoreCase = True

objRegExp.Global = True

If Sheets("Constants Input").AutoFilterMode = True Then
Sheets("Constants Input").UsedRange.AutoFilter
End If
Reset:
Do While NumbToReplace = 0
NumbToReplace = 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
FilterCriteria = WorksheetFunction. _
Text(NumbToReplace, "#,##0.00_);(#,##0.00)")
Sheets("Constants Input").Activate
Set Filter1 = Sheets("Constants Input").Range("A1:F" & _
Cells(Rows.Count, "D").End(xlUp).Row)
Filter1.AutoFilter Field:=2, Criteria1:="<>"
Filter1.AutoFilter Field:=4, Criteria1:="=" & FilterCriteria
Set myRange = Nothing
On Error Resume Next
Set myRange = Intersect(Sheets("Constants Input").Range("B2:B" & _
Cells(Rows.Count, "B").End(xlUp).Row), _
Sheets("Constants Input").UsedRange. _
SpecialCells(xlCellTypeVisible))
If Err.Number > 0 Then
MsgBox ("The number you entered is not on the worksheet! & _
Chr(10)" & "Please try again .....")
GoTo Reset:
Err.Reset
End If
For Each MyCell In myRange
If Len(MyCell.Offset(0, 3).Formula) = 0 Then
MsgBox "Not all Replacements are entered!!!"
Exit Sub
End If
MyCell.Offset(0, 3).Formula = myRange(1).Offset(0, 3).Formula
Application.Goto Reference:=Worksheets(MyCell.Offset(0, 0)._
Value).Range(MyCell.Offset(0, 1).Value)
FormulaText = ActiveCell.Formula
ReplaceWith = Replace(MyCell.Offset(0, 3).Formula, "=", "")
If (objRegExp.Test(FormulaText) = True) Then
'Do the replacement
ActiveCell.Formula = objRegExp.Replace(FormulaText, _
ReplaceWith)
If ActiveCell.Formula <> FormulaText Then
ActiveCell.Interior.ColorIndex = 43
myRange(1).Offset(0, 3).Interior.ColorIndex = 4
Else
ActiveCell.Interior.ColorIndex = 3
myRange(1).Offset(0, 3).Interior.ColorIndex = 3
End If
'Debug.Print FormulaText
Else
MyCell.Offset(0, 3).Interior.ColorIndex = 3
ActiveCell.Interior.ColorIndex = 3
myRange(1).Offset(0, 3).Interior.ColorIndex = 3
End If
Next MyCell
ActiveWindow.Zoom = 75
Sheets("Constants Input").Activate
Range(myRange(1).Address).Select
Cells.Columns.AutoFit
MsgBox "Process Completed! Press OK to Continue"

End Sub

********************************
Thanks Ron
********************************

 

[Ron Rosenfeld]

Ron, (or anyone with a desire the help who knows RegEx)

All works very well except that negative numbers are not actually being
replaced. Positive amounts are being replaced as expected.
************************************************
FormulaText = ActiveCell.Formula
ReplaceWith = Replace(MyCell.Offset(0, 3).Formula, "=", "")

If (objRegExp.Test(FormulaText) = True) 'Does Pass as True but:

ActiveCell.Formula = objRegExp.Replace(FormulaText, ReplaceWith)
'Does not actually do the replace when the Pattern is negative
************************************************
Next are the variables at that point

ReplaceWith = 'Constants Input'!$D$245
Pattern = \b-64596792\b
FormulaText =-'C:\Acctg\FIT\[Accum Temp.xls]2005'!$A$1 _
-64596792+59410246

If the pattern is \b64596792\b then the replacement is made.

Other examples of negative numbers not replaced (I have re-spaced for
clarity)

= -9516836788 -703165.07
='Constants Input'!$D$8 -575002+ 'Constants Input'!$D$10
= -821377172+ -4030801

I'm going to confine my answer to your regex, and not review the code you've
posted.

Yes, the examples you posted last were all positive integers, and I did not
test against anything else. Actually, I wasn't quite sure until subsequent
posts that Denis and EagleOne were the same person.

The problem is that the "\b" token picks up a word boundary. A word boundary
is defined as a change from a word character [A-Za-z0-9_] and a few other
characters to a non-word character.

Since the operators are not word characters, the pattern \b-64596792\b will not
pick up the "-" as there is no "\b" prior to the "-". By definition, there
cannot be. The first word boundary in the string "-64596792" is between the
"-" and the first digit "6".

This only becomes a problem when you specify the "-" as part of a signed
number, rather than as an operator.

Depending on precise requirements, you could just make the "-" optional, and
construct a pattern like:

-?\b64596792\b

--ron

 

[Ron Rosenfeld]

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 were:

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

 

[EagleOne]

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 (re:
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

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 were:

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

 

[Ron Rosenfeld]

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 (re:
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

 

[EagleOne]

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,

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 (re:
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

 

[Ron Rosenfeld]

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

 

[Dennis]

FYI,

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

Dennis

 

[Dennis]

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.

Partial but relevent code follows:

'set up Regex
Dim objRegExp As RegExp
Dim objMatch As match
Dim colMatches As MatchCollection

' Create a regular expression object.
Set objRegExp = New RegExp
' Set Case Insensitivity.
objRegExp.IgnoreCase = True
' Set global applicability.
objRegExp.Global = True

Dim FormulaText As String
Const CellRef As String = "\$?[A-Z]{1,2}\$?\d{1,5}"
Const Operator As String = "[/*^&()=<>,+]" 'No "-"
Const WithinString As String = """[^""]*"""
Const NumConstant As String = "-?(\d*\.)?\d+"

Ignore = Join(Array(WithinString, CellRef, Operator), "|")

' set the Regex pattern to replace unwanted stuff
objRegExp.Pattern = Join(Array(WithinString, CellRef, Operator),
"|")


Set myRange = Sheets("Constants in Formulas").Range("F2:F" & _
Sheets("Constants In Formulas").Cells(Rows.Count,
"D").End(xlUp).Row)
iCounter = Sheets("Constants Input").Cells(Rows.Count,
"D").End(xlUp)(2).Row

Set myCell = Nothing
For Each myCell In myRange
OrigFormula = myCell.Formula
FormulaText = myCell.Formula

' Test whether the String can be compared.
If (objRegExp.Test(FormulaText) = True) Then

' change - to +- to retain negative signage
FormulaText = Replace(FormulaText, "-", "+-")

' replace unwanted stuff
objRegExp.Pattern = Ignore
FormulaText = objRegExp.Replace(FormulaText, "~")

' Get the matches.
objRegExp.Pattern = NumConstant
Set colMatches = objRegExp.Execute(FormulaText) ' Execute
search.

End If

' Output for testing, but could go into any range
For Each objMatch In colMatches
'myCell.Offset(0, iCounter).Value = objMatch
With Sheets("Constants Input").Range("D" & iCounter)
.Offset(0, 0).Value = objMatch 'Amount
.Offset(0, -2).Value = myCell.Offset(0, -4).Value
'Sheetname
.Offset(0, -1).Value = myCell.Offset(0, -3).Value
'Cell address Relative
.Offset(0, 1).Formula = "='" + Sheets("Constants
Input").Name + "'!" _
+ Application.ConvertFormula(Range("D" &
iCounter).Offset(0, 0).Address, _
xlA1, xlA1, xlAbsolute)
'Cell address Absoulte
.Offset(0, 2).Formula =
"=HYPERLINK(""#""&CELL(""address"",'" + _
myCell.Offset(0, -4).Value + "'!" + _
myCell.Offset(0, -3).Value + "),""Click Me"")"
End With
If InStr(1, OrigFormula, "!") > 0 Then
Sheets("Constants Input").Range("D" &
iCounter).Offset(0, 0).Interior.ColorIndex = 6 ' Yellow
End If
If InStr(1, OrigFormula, "(") > 0 Then
On Error Resume Next
With Sheets("Constants Input").Range("D" &
iCounter).Offset(0, 0)
.BorderAround LineStyle:=xlContinuous,
ColorIndex:=3, Weight:=xlMedium
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideVertical).Weight = xlMedium
End With
On Error GoTo 0
End If
iCounter = iCounter + 1
Next objMatch
iCounter = iCounter + 2
Next myCell
*************************************************************************************

TIA Dennis

*************************************************************************************

 

[Dennis]

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

 

[Dennis]

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]

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

 

[Ron Rosenfeld]

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