Use RegEx in vba sub in general sheet

Closed
Sanju_7454 Posts 13 Registration date Thursday May 9, 2019 Status Member Last seen May 28, 2019 - May 21, 2019 at 05:44 PM
 Blocked Profile - 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

2 responses

Sanju_7454 Posts 13 Registration date Thursday May 9, 2019 Status Member Last seen May 28, 2019 3
May 21, 2019 at 06:56 PM
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.
0
Blocked Profile
May 22, 2019 at 10:20 AM
How does that value get entered in?
0
Sanju_7454 Posts 13 Registration date Thursday May 9, 2019 Status Member Last seen May 28, 2019 3
May 25, 2019 at 11:20 AM
Copy pasted from non-formatted cell to Cell A1
0
Blocked Profile
May 25, 2019 at 06:31 PM
I dont get an error with this:

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