Regular Expression in excel VBA

Question

I'm trying to use regex in excel VBA to match a pattern within all cells in a column range, and remove the matched patterns to a new column range.

E.g.

1. Happy Day Care Club (1124734)
2. French Pattiserie (8985D)
3. The King's Pantry (G6666642742D)
4. Big Shoe (China) Ltd (ZZ454)

Essentially I want to remove the last bracketed portion of each string and transpose this part (without the brackets) into a different column range.

The regex I have so far is "(([^)]+))\z" (which I don't know if this is actually correct), and embedded within this VBA:

Dim regEx As New RegExp
Dim strPattern As String
Dim strInput As String
Dim strReplace As String
Dim Myrange As Range

Sheets("Sheet 1").Activate
Range("FF65536").End(xlUp).Select
LastCell = ActiveCell.Address

Set Myrange = ActiveSheet.Range("FF2:" & LastCell)

For Each C In Myrange
    strPattern = "(\(([^\)]+)\)\z)"

    If strPattern <> "" Then
        strInput = C.Value
        strReplace = "$1"

        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        If regEx.Test(strInput) Then
            Range("FF2").Select = regEx.Replace(strInput, "$1")
            Range("DX2").Select = regEx.Replace(strInput, "$2")
        End If
    End If
Next

I'm a newbie so please forgive glaringly obvious mistakes.

Many thanks,

Answer 1

No your regex pattern isn't correct. You should test your pattern separately as regex is its own mini-language. Try this pattern (Regex101):

\((.+)\)$

About the gm options: g means Global, m means Multiline, both of which are set to True in your code.