Regular Expression approach to alter part of an Excel Formula

Main question:

I'm using this RegExp pattern to match Excel Cell References

Please note that I'm using VBscript

([$]{0,1}[A-Z]{1,2}[$]{0,1}\d{1,5})

it finds the formulas properly but I want to extract a nth element and convert it to absolute referencing and then put it back in the same string

ie
=A100+A100+B$10
I'd like to be able to replace the second match (A100) with $A$100 in the same string.
=A100+$A$100+B$10
Would appreciate any pointers on this


Subquestion: The approach I tried using Replace is below and it led to another query:

I found this code below at  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vsmthreplace.asp and I thought that it gave me a good start - but
when I tried to swap the 4th 5th and 6th matches (ie "brown fox") I couldn't get it to work

Function ReplaceTest(patrn, replStr)
  Dim regEx, str1               ' Create variables.
  str1 = "The quick brown fox jumped over the lazy dog."
  Set regEx = New RegExp            ' Create regular expression.
  regEx.Pattern = patrn            ' Set pattern.
  regEx.IgnoreCase = True            ' Make case insensitive.
  ReplaceTest = regEx.Replace(str1, replStr)   ' Make replacement.
End Function

In addition, the Replace method can replace subexpressions in the pattern. The following call to the function shown in the previous example swaps the first pair of words in the original string:

MsgBox(ReplaceTest("(\S+)(\s+)(\S+)", "$3$2$1"))   ' Swap first pair of words

How would I swap the order of "brown" and "fox"?

I tried
MsgBox(ReplaceTest("(\S+)(\s+)(\S+)", "$6$5$4"))  
and it gave me

"$6$5$4 brown fox jumped over the lazy dog."

Cheers

DaveStart Free Trial