sdc248
asked on
Excel VBA regular expression debug
Hi:
I am trying to loop through each cell in a column and count the number of cells having 'e' in their texts but keep getting "Application-defined or object-defined error" error message. Below is what I've got. Please help.
Dim REX As RegExp
Dim oMatch As Object
Dim count As Integer
Dim c As Range
Set REX = CreateObject("VBScript.Reg Exp")
With REX
.Global = True
.IgnoreCase = True
.Pattern = "*e"
End With
count = 0
For Each c In Range("F2:F500").Cells
If REX.Test(c.Value) Then 'the error message is thrown here
Set oMatch = REX.Execute(c.Value)
End If
If Not oMatch Is Nothing Then
count = count + 1
End If
Set oMatch = Nothing
Next c
Please note that I have added 'Microsoft VBScript Regular Expressions 5.5' to References of the project.
Thanks.
I am trying to loop through each cell in a column and count the number of cells having 'e' in their texts but keep getting "Application-defined or object-defined error" error message. Below is what I've got. Please help.
Dim REX As RegExp
Dim oMatch As Object
Dim count As Integer
Dim c As Range
Set REX = CreateObject("VBScript.Reg
With REX
.Global = True
.IgnoreCase = True
.Pattern = "*e"
End With
count = 0
For Each c In Range("F2:F500").Cells
If REX.Test(c.Value) Then 'the error message is thrown here
Set oMatch = REX.Execute(c.Value)
End If
If Not oMatch Is Nothing Then
count = count + 1
End If
Set oMatch = Nothing
Next c
Please note that I have added 'Microsoft VBScript Regular Expressions 5.5' to References of the project.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm glad I was able to help.
Marty - MVP 2009 to 2012
Marty - MVP 2009 to 2012
ASKER