Using regexp in Excel can I perform some arithmetic on the matched pattern before replacing the matched string?

I am using `VBscript.RegExp`` to find and replace using a regular expression. I'开发者_StackOverflow中文版m trying to do something like this:

Dim regEx
Set regEx = CreateObject("VBScript.RegExp")
regEx.Pattern = "ID_(\d{3})"
regEx.IgnoreCase = False
regEx.Global = True
regEx.Replace(a_cell.Value, "=HYPERLINK(A" & CStr(CInt("$1") + 2) )

I.e. I have cells which contain things like ID_006 and I want to replace the contents of such a cell with a hyperlink to cell A8. So I match the three digits, and then want to add 2 to those digits to get the correct row to hyperlink to.

But the CStr(CInt("$1") + 2) part doesn't work. Any suggestions on how I can make it work?

Ive posted given these points

• you should test for a valid match before trying a replace
• from your current code the Global is redundant as you can add 1 hyerplink (1 match) to a cell
• your current code will accept a partial string match, if you wanted to avoid ID_9999 then you match the entire string using ^ and $. This version runs me, you can revert to your current pattern with .Pattern = "ID_(\d{3})"
• Normally when adding a hyperlink a visible address is needed. The code beloe does this (with the row manipulation in one shot)

The code below runs at A1:A10 (sample shown dumping to B1:B10 for pre and post coede)

 Sub ParseIt()
    Dim rng1 As Range
    Dim rng2 As Range
    Dim regEx
    Set rng1 = Range([a1], [a10])
    Set regEx = CreateObject("VBScript.RegExp")
    With regEx
        'match entire string
        .Pattern = "^ID_(\d{3})$"
        'match anywhere
       ' .Pattern = "ID_(\d{3})"
        .IgnoreCase = False
        For Each rng2 In rng1
            If .test(rng2.Value) Then
            'use Anchor:=rng2.Offset(0, 1) to dump one column to the right)
                ActiveSheet.Hyperlinks.Add Anchor:=rng2, Address:="", SubAddress:= _
                                           Cells(.Replace(rng2.Value, "$1") + 2, rng2.Column).Address, TextToDisplay:=Cells(.Replace(rng2.Value, "$1") + 2, rng2.Column).Address
            End If
        Next
    End With
End Sub

This is because: "=HYPERLINK(A" & CStr(CInt("$1") + 2) is evaluated once, when the code is executed, not once for every match.

You need to capture & process the match like this;

a_cell_Value = "*ID_006*"
Set matches = regEx.Execute(a_cell_Value)
Debug.Print "=HYPERLINK(A" & CLng(matches(0).SubMatches(0)) + 2 & ")"

>> =HYPERLINK(A8)

Or if they are all in ??_NUM format;

a_cell_Value = "ID_11"
?"=HYPERLINK(A" & (2 + val(mid$(a_cell_Value, instr(a_cell_Value,"_") +1))) & ")"
=HYPERLINK(A13)

The line -

regEx.Replace(a_cell.Value, "=HYPERLINK(A" & CStr(CInt("$1") + 2) )

won't work as VBA will try to do a CInt on the literal string "$1" rather than on the match from your RegEx.

It would work if you did your replace in 2 steps, something like this -

Dim a_cell
a_cell = Sheets(1).Cells(1, 1)
Dim regEx
Set regEx = CreateObject("VBScript.RegExp")
regEx.Pattern = "ID_(\d{3})"
regEx.IgnoreCase = False
regEx.Global = True
a_cell = regEx.Replace(a_cell, "$1")
Sheets(1).Cells(1, 1) = "=HYPERLINK(A" & CStr(CInt(a_cell) + 2) & ")"