Travis456789
asked on
VBA Script Performance
Utilizing the following script that I found online:
Function SimpleCellRegex(Myrange As Range) As String
Dim regEx As New RegExp
Dim strPattern As String
Dim strInput As String
Dim strReplace As String
Dim strOutput As String
strPattern = "[E][N][0-9][0-9][0-9][0-9 ][0-9][0-9 ][0-9]"
If strPattern <> "" Then
strInput = Myrange.Value
strReplace = ""
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End With
If regEx.test(strInput) Then
SimpleCellRegex = regEx.Replace(strInput, strReplace)
Else
SimpleCellRegex = "Not matched"
End If
End If
Set regEx = Nothing
End Function
Works great for my purposes; but two things after it identifies the string; I want it to return the 7 Numbers after and it's a memory hog. Is the Set regEx = nothing in the correct place?
Data Point: EN0799038 - 3 ROLLS X 3000
Currently returns all the text after the 8
Function SimpleCellRegex(Myrange As Range) As String
Dim regEx As New RegExp
Dim strPattern As String
Dim strInput As String
Dim strReplace As String
Dim strOutput As String
strPattern = "[E][N][0-9][0-9][0-9][0-9
If strPattern <> "" Then
strInput = Myrange.Value
strReplace = ""
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End With
If regEx.test(strInput) Then
SimpleCellRegex = regEx.Replace(strInput, strReplace)
Else
SimpleCellRegex = "Not matched"
End If
End If
Set regEx = Nothing
End Function
Works great for my purposes; but two things after it identifies the string; I want it to return the 7 Numbers after and it's a memory hog. Is the Set regEx = nothing in the correct place?
Data Point: EN0799038 - 3 ROLLS X 3000
Currently returns all the text after the 8
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
My Mistake! That did work! Thank you!!!!
ASKER