How can I paste the found regex to another column? Getting Invalid procedure call or argument error

[statiCat]

I have been stuck googling for a while now. What I am trying to do is:

1. First check if "mystring" is in column S (s_col)
2. If so, search column P (p_col) for a year that has white space in front and at the end
3. Paste that year into column k (k_col)

However I keep getting an error that says, "Invalid Procedure call or argument". What am I doing wrong?

My code:

Sub Get_matches()

Dim last_Row As Long
Dim s_col As String
Dim k_col As String
Dim p_col As String

last_Row = Range("A1").SpecialCells(xlCellTypeLastCell).Row
s_col = "S"
k_col = "K"
p_col = "P"

Range(k_col & "1").Value = "Results"
year_pattern = "\s\b[0-9]{4}\b\s"

Dim i As Long
Dim allMatches As Object
Dim result As String

Dim Reg_Exp As Object
Set Reg_Exp = CreateObject("vbscript.regexp")
Reg_Exp.Pattern = year_pattern
Reg_Exp.Global = True
Reg_Exp.IgnoreCase = True
Set allMatches = Reg_Exp.Execute(Text)


' determine if k in s column
For i = 2 To last_Row
    Set allMatches = Reg_Exp.Execute(Sheets("report").Range(p_col & i).Value)
    If InStr(1, LCase(Range(s_col & i)), "mystring") Then
        If allMatches.Count > 0 Then
             result = allMatches.Item(0).SubMatches.Item(0) ' This is where I get "Invalid Procedure call or argument" error
             Sheets("report").Range(k_col & i).Value = result
        End If
    End If
Next i

End Sub

 

[JLGWhiz]

I am not really up on VB Script but based on the excerpt below, I suspect that the SubMatches is the cause of your error.

A SubMatches collection contains individual submatch strings, and can only be created using the Execute method of the RegExp object. The SubMatches collection's properties are read-only