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

statiCat

New Member
Joined
Apr 21, 2018
Messages
19
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:

VBA 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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,785
Office Version
  1. 2013
Platform
  1. Windows
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,119,253
Messages
5,576,977
Members
412,754
Latest member
vintage88
Top