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