Link to home
Start Free TrialLog in
Avatar of Travis456789
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
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Travis456789
Travis456789

ASKER

Closer; but I want to output the 7 characters in the cell after it finds the patter.  Not "Match"
My Mistake!  That did work!  Thank you!!!!