View Full Version : RegExp Add Increment to each SubMatch

12-22-2008, 11:31 PM
I am trying to write a RegExp to add a formatted incrementing number to each sub match of pattern "(TB[RD])".

Cell contains "Txt (TBD) more txt (TBR) more txt (TBD)."

I want this:
"Txt (TBD001) more txt (TBR002) more txt (TBD003)."

This is what I have so far:

Dim re As Object, matches As Object, m As Object
Dim n As Integer 'Counter
Set re = CreateObject("vbscript.regexp")
re.IgnoreCase = False
re.Global = True
re.Pattern = "(TB[RD])"
n = 1
Set matches = re.Execute(Cells(1, 1).Value) 'Chose one cell as an example
For Each m In matches
'Write the Data
Cells(1, 1).Value = re.Replace(Cells(1, 1).Value, m & Format(n, "000"))
n = n + 1

This replaces each matches with the same number. Need to change to each submatch increments with unique number. But how?