Use RegEx in vba sub in general sheet

Posts
13
Registration date
Thursday May 9, 2019
Status
Member
Last seen
May 28, 2019
- - Latest reply: ac3mark
Posts
12606
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
July 22, 2019
- May 25, 2019 at 06:31 PM
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
See more 

2 replies

Posts
13
Registration date
Thursday May 9, 2019
Status
Member
Last seen
May 28, 2019
3
0
Thank you
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.
ac3mark
Posts
12606
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
July 22, 2019
1161 -
How does that value get entered in?
Sanju_7454
Posts
13
Registration date
Thursday May 9, 2019
Status
Member
Last seen
May 28, 2019
3 -
Copy pasted from non-formatted cell to Cell A1
Respond to Sanju_7454
Posts
12606
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
July 22, 2019
1161
0
Thank you
I dont get an error with this:

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