Loop Through Cells & Return Regex Search in Message Box

Question

Question with a possibly very simple answer, but I'm still potty training with VBA. I'm looping through an excel array and searching a very long string for the regex pattern "\.\w*?_\w*?_Tag_\w*?". There is a similar example of this in this post under the accepted answer "Example 3: Loop Through Range."

Instead of finding and replacing text though, I want to display the matched search pattern from each cell of the array in a single message box. I've been searching for several hours but most of the VBA Regex examples I've found use built in functions (which don't loop through an array), but since this will be used by other folks I need to use a macro.

Here's what I have so far. I'm thinking I need another loop outside of my For Each loop but not sure how to start executing on this.

Sub TagNameList()
    Dim strPattern As String: strPattern = "\.\w*?_\w*?_Tag_\w*?"
    Dim Regx As New RegExp
    Dim StrInput As String
    Dim Rng As range
    Dim LastRow As Long: LastRow = ActiveSheet.UsedRange.Rows.Count

'    Set Rng = ActiveSheet.range(Cells(2, 16), Cells(LastRow, 16))
'    Set RegxMatch = Regx.Execute(StrInput)

    For Each cell In Rng
        StrInput = cell.Value

        With Regx
            .Global = True
            .MultiLine = True
            .IgnoreCase = True
            .Pattern = strPattern
        End With

'        If Regx.Test(StrInput) Then
'             MsgBox (Regx.Replace(StrInput, strReplace))
'        Else
'             MsgBox ("Not matched")
'        End If

    Next
End Sub

SJR · Accepted Answer · 2017-07-06T20:44:18.770

0

I'm not clear if you mean a message box for the whole range or one for each cell, but you need to use the matches collection, e.g.

    Sub TagNameList()

    Dim strPattern As String: strPattern = "\.\w*?_\w*?_Tag_\w*?"
    Dim Regx As New RegExp
    Dim StrInput As String
    Dim Rng As Range
    Dim LastRow As Long: LastRow = ActiveSheet.UsedRange.Rows.Count
    Dim oMatches As Object, s As String

    Set Rng = ActiveSheet.Range(Cells(2, 16), Cells(LastRow, 16))
    Set RegxMatch = Regx.Execute(StrInput)

    For Each cell In Rng
        StrInput = cell.Value

        With Regx
            .Global = True
            .MultiLine = True
            .IgnoreCase = True
            .Pattern = strPattern

            If .Test(cell) Then
                Set oMatches = .Execute(cell)
                s = s & "," & oMatches(0).Value
            End If

        End With
    Next
    MsgBox Mid(s, 2)

End Sub

edited Jul 06 '17 at 20:44

answered Jul 06 '17 at 20:18

SJR

22,986
6
18
26

Sorry SJR, it would be one message box for the whole range. – D. Lemur Jul 06 '17 at 20:33
Do you know how I'd display the first occurrence of the match from each cell in just one message box? – D. Lemur Jul 06 '17 at 20:42
That works! I just realized that you can't copy the text from the message box. So I can't actually use it for what I wanted, but you definitely deserve the check box. Do you know how I'd put the matched text in a corresponding range? – D. Lemur Jul 06 '17 at 20:55
Just something like range("a1").value=mid(s,2) – SJR Jul 06 '17 at 20:59
Okay, gotcha. You're code is a lot more streamlined than what I'm used to seeing. Still trying to figure everything out. Thanks again though! – D. Lemur Jul 06 '17 at 21:06
Regexp is not the easiest place to start. It makes my head hurt! – SJR Jul 06 '17 at 21:07

Loop Through Cells & Return Regex Search in Message Box

1 Answers1