CODEWITHSUNDEEP
regexvbaexcel
Girish Kaul
Girish Kaul

Reputation: 3

How do I print my extracted pattern in a column using regex.execute and match object in vba?

I'm using vba to write a sub to extract pin codes from given addresses in a column in an excel worksheet. I was able to find the regex pattern to extract the pin pattern but Im unable to output the said extracted pins to a column. As a way to test whether the regex is able to extract the pin pattern from the column (it is) I passed the Match.value property from matches object to a msgbox and was able to get an output for each string in a msgbox. 

Private Sub simpleRegex()
    Dim strPattern As String: strPattern = "\d{6}"
    Dim Match As Object
    Dim matches As Object

    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    Dim strInput As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("B1:B30")

    For Each cell In Myrange
        If strPattern <> "" Then
            strInput = cell.Value

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

            If regex.Test(strInput) Then
                Set matches = regex.Execute(strInput)
                For Each Match In matches
                    MsgBox (Match.Value) 'A workaround I found to see if my pattern 
                                         'worked but I need to print Match.value 
                                         'in a column so this wont do
                Next
            Else
                MsgBox ("Not matched")
            End If
        End If 
    Next
End Sub

How do I extract the pattern string from the match object and print it into a column (like U1:U30) for each cell in my range B1:B30

TL;DR: Regex Pattern working but how to print extracted pattern in cell

Upvotes: 0

Views: 628

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57753

How about collecting the matches comma separated in a string strMatches and write that to a cell?

Add this before For Each cell In Myrange

Dim i As Long, strMatches As String
i = 1 'row number where we start to write

And replace your other For Each with

strMatches = vbNullString
For Each Match In matches
    strMatches = strMatches & Match.Value & ", " 'collect all matches comma seprated
Next
If Not strMatches = vbNullString Then strMatches = Left(strMatches, Len(strMatches) - 2) 'remove last comma
Worksheets("your-sheet-name").Range("U" & i).Value = strMatches 'write the matches into cell
i = i + 1

Upvotes: 0

Related Questions