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.

我正在使用vba编写一个子来从excel工作表的列中的给定地址中提取PIN码。我能够找到正则表达式模式来提取引脚模式,但我无法将所述提取的引脚输出到列。作为一种测试正则表达式是否能够从列中提取引脚模式的方法(它是)我将Match.value属性从matches对象传递给msgbox,并且能够获得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

如何从匹配对象中提取模式字符串并将其打印到我的范围B1:B30中的每个单元格的列(如U1:U30)中

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

TL; DR:正则表达式模式工作,但如何在单元格中打印提取的模式

1 个解决方案

#1

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

如何收集字符串strMatches中分隔的匹配逗号并将其写入单元格?

Add this before For Each cell In Myrange

在Myrange中为每个单元格添加此项

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

And replace your other For Each with

并替换你的其他For Each

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

#1