Issue extracting pattern from URL in VBA

Question

Here in the excel sheet I am looping through the column(containing URL) extracting patterns(for eg. us/en/dhs) however I am getting a runtime error : 5 Tried a similar piece of code which was working but here it fails.

Error at line containing bold text.

Sub generateLWP()
'
' In the Active Sheet :
'   - Extract the LWP from the URL and save it in column N
'   - Identify pattern in every URL in the From Column and add the matching word to its corresponding cell in Article column
'

    'Select Non-Blank Link Text
   ‘ Call selectNonBlank

    'Start from D3(URL) and loop through till the end and extract the LWP pattern
    With ActiveSheet
        .Range("N1") = "LWP"
        '.Range("O1") = "Article Pattern"
        .Range("D3").Select
    End With
    'End

    'Variables
    Dim lwp As String: lwp = ""
    Dim str As String

    Set regEx = CreateObject("vbscript.regexp")
    With regEx
            .Global = True
            '.MultiLine = True
            .IgnoreCase = True
            .pattern = "/([a-zA-Z]){2}\/([a-zA-Z]){2}\/(([a-zA-z]{5}[+0-9])|[a-zA-z]{3}|[0-9]{2})/g"
    End With

    Do Until IsEmpty(ActiveCell)
        'extract the LWP pattern and store it in corresponding cell of column N
        str = ActiveCell.Value
        Set regMatchObj = regEx.Execute(str)    ‘Object containing the matching text
        lwp = regMatchObj.Item(0)
        ‘lwp = regMatchObj.Item(0).SubMatches.Item(0)
        ActiveCell.Offset(0, 10).Text = lwp
        ActiveCell.Offset(1, -10).Select  'Go down
    Loop    

End Sub

Can you help determine why I am receiving an error on this line?

Hi Tim, Just like you said I updated the lwp declaration to Match type and used Set to assign it the expression, yet it continues to encounter the same error. To add to the above I tried checking the typeName of lwp its showing as "Nothing". — Azan Momin
– Azan Momin, Commented Mar 28, 2016 at 15:05

Tim Williams · Accepted Answer · 2016-03-16 16:52:33Z

1

lwp is a Match object, not a string, so you need to change your declaration and use Set when assigning a value.

See https://msdn.microsoft.com/en-us/library/yfy6y4h1(v=vs.84).aspx

answered Mar 16, 2016 at 16:52

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Issue extracting pattern from URL in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related