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


Dennis wrote:
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 Rosenfeld wrote:
On 30 Nov 2006 03:57:28 -0800, "Dennis" wrote:

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