Regexp & VBA - loop through cells, return matched value in adjacent cell

Question

If in column A,I had values like A

A0394

948B0129

Zkjs333

0a0401a

09ab28

and I wanted to return matches where there are 2 alpha followed by 2 numeric characters using regular expressions and VBA (without using a custom function) B

js33

ab28

What would the code look like?

I threw it away because I was not running properly and I've never used regexp with vba before. Here's something along the lines of what I had `sub test() Dim strPattern As String dim regex As Object, str as string set regex=createObject("VBScript.RegExp") with regex .pattern="[a-z]{2}[0-9]{2}" .global=true end with for each thing in range("A1:A4") set matches= regex.execute(thing) thing.offset(0,1)=regex.execute(thing) next` — machump, May 17 '17 at 19:54

score 0 · Answer 1 · edited May 23 '17 at 12:10

0

You Regex is correct and should something like this to define it properly in VBA

Private Sub simpleRegex()
    Dim strPattern As String: strPattern = "[a-z]{2}[0-9]{2}"       
    Dim regEx As New RegExp      


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

with

regEx.Test(strInput)

you can check if a String match or not.

you can find a very deep answer here.

edited May 23 '17 at 12:10

Community

1
1

answered May 17 '17 at 20:18

Denis Ismailovski

311
1
7
15

using the code in example 3 (looping through range) of the very deep answer: what would you write in the if statement (if regex.test(strinput) then) to return the value of the matches found? I tried msgbox (regex.execute(strinput)) but it returns an error: "Wrong number of arguments or invalid property assignment" – machump May 17 '17 at 20:30
regex.execute(strinput) return all the possible matches i think that doing `msgbox (regex.execute(strinput)) you are settings too much arguments. @machump – Denis Ismailovski May 17 '17 at 21:23

score 0 · Answer 2 · answered May 17 '17 at 21:15

Sub test()

    Dim matches, regex As Object, c As Range
    Dim i As Long

    Set regex = CreateObject("VBScript.RegExp")
    With regex
        .Pattern = "[a-z]{2}[0-9]{2}"
        .Global = True
    End With

    For Each c In Range("A1:A4")
        Set matches = regex.Execute(c.Value)

        'if only one match expected...
        If matches.Count > 0 Then
            c.Offset(0, 1) = matches(0)
        End If

        'if can be multiple matches...
        'For i = 1 To matches.Count
        '    c.Offset(0, i) = matches(i - 1)
        'Next i
    Next

End Sub

A.S.H · Accepted Answer · 2017-05-17T21:35:02.173

You almost had it. Since you are searching for only one occurrence of the pattern, say the first, you can have it as matches(0), but first check if there are matches using matches.count.

Sub Test()
  Dim cel As Range, matches As Object
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "[a-zA-Z]{2}[0-9]{2}"
    For Each cel In Range("A1:A10")
      Set matches = .Execute(cel.Value2)
      If matches.Count > 0 Then cel.Offset(0, 1).Value = matches(0)
    Next
  End With
End Sub

Regexp & VBA - loop through cells, return matched value in adjacent cell

3 Answers3