On Mon, 4 May 2009 13:01:02 -0700, Excel Monkey
wrote:

I am using Regular Expressions to for a replace function. I could not get
the following to work below so I changed the last line by using a Substitute
function from Excel. Why isn't the line: 'tempstring =
objRegExp.Replace(SeriesAddress, Match(0).Value) working?



Public Function RegReplace(Pattern As String, SeriesAddress As String) As
String
'Set Reference To Microsoft Scripiting Runtime
Dim objRegExp As Object
Dim Match
Dim tempstring As String

Set objRegExp = CreateObject("Vbscript.RegExp")

objRegExp.IgnoreCase = IgnoreCase
objRegExp.MultiLine = MultiLine
objRegExp.Pattern = Pattern

Set Match = objRegExp.Execute(SeriesAddress)

'tempstring = objRegExp.Replace(SeriesAddress, Match(0).Value)

tempstring = WorksheetFunction.Substitute(SeriesAddress, SeriesAddress,
Match(0).Value)

RegReplace = tempstring

End Function

Thanks

EM

It's hard to tell exactly what you're doing.

But you have a number of variables that are not declared.
You are doing both an extraction and then a replacement, so your routine is
more than just a simple use of the Replace method of the Regex object.

It would be useful if you could supply the values for Pattern & SeriesAddress,
as well as your expected result.

You should also make it a habit to always have Option Explicit at the start of
your modules, so that you will detect your undeclared variables. This can be
automated by selecting Tools/Options/Editor and select "Require Variable
Definition".
--ron