Use RegEx in vba sub in general sheet

Closed
Sanju_7454 Posts 12 Registration date Thursday 9 May 2019 Status Member Last seen 28 May 2019 - 21 May 2019 à 17:44
 Blocked Profile - 25 May 2019 à 18:31
How to use regex in general vba sheet. i am getting lot of red lines and not able to find the reason for that.

I have enabled the Regularexpression vbScript also.

Private Const FCheckRgAddress As String = "A1:A100"
Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140905
    Dim a As String

    Set xChanged = Application.Intersect(Range(FCheckRgAddress), Target)
    If xChanged Is Nothing Then Exit Sub
End If

    Set xRegExp = CreateObject("VBScript.RegExp")
    xRegExp.Global = True
    xRegExp.IgnoreCase = True
    xRegExp.Pattern = "[^0-9a-z]"
    For Each xRg In xChanged
        If xRegExp.Test(xRg.Value) Then
            xHasErr = True
            Application.EnableEvents = False
            xRg.ClearContents
            Application.EnableEvents = True
        End If
    Next
    If a=1 Then

MsgBox "These cells had invalid entries and have been cleared:"
End If
End Sub


I got it from someother site, most of the lines are red

Plz help me
Related:

2 responses

Sanju_7454 Posts 12 Registration date Thursday 9 May 2019 Status Member Last seen 28 May 2019 3
21 May 2019 à 18:56
Thank You. I found answer for this.

But i have one doubt, how to read the cell value in vba if A1 has special character like --------

I used Range("A1").Value , Its throwing an error

Plz help me on this.
Blocked Profile
22 May 2019 à 10:20
How does that value get entered in?
Sanju_7454 Posts 12 Registration date Thursday 9 May 2019 Status Member Last seen 28 May 2019 3
25 May 2019 à 11:20
Copy pasted from non-formatted cell to Cell A1
Blocked Profile
25 May 2019 à 18:31
I dont get an error with this:

Sub showit()
Dim theval
theval = Range("A1").Value
MsgBox (theval)
End Sub