Check excel file for special character _ and letter with Like Operator on entire Sheet

Question

Below is the macro code but it is working for a single cell.I want it for entire sheet.

Public Function IsSpecial(s As String) As Long
 Dim L As Long, LL As Long
 Dim sCh As String
 IsSpecial = 0
 For L = 1 To Len(s)
     sCh = Mid(s, L, 1)
     If sCh Like "[0-9a-zA-Z]" Or sCh = "_" Then
     Else
         IsSpecial = 1
         Exit Function
     End If
 Next L
End Function

What do you want to do with it further ? Are you trying to remove special characters or replace them ? — Imran Malek, Oct 11 '17 at 12:09

Imran Malek · Answer 1 · 2017-10-11T18:19:56.877

0

Effective way of matching patterns is to use regex , consider the general code below

Check this answer for more information and tweaks.

Sub test()

    Dim regex As Object
    Dim pattern As String

    Set regex = CreateObject("VBScript.RegExp")

    regex.Global = True
    regex.IgnoreCase = True
    regex.pattern = pattern

    'define your regex pattern here
    pattern = "[0-9a-zA-Z]"

    'check each cell in range
    For Each cell In ActiveSheet.Range("A1:A10")
        If pattern <> "" Then
            If regex.test(cell.Value) Then
                'if the pattern matches do some operation
                Debug.Print cell.Address
            End If
        End If
    Next

End Sub

edited Oct 11 '17 at 18:19

answered Oct 11 '17 at 12:22

Imran Malek

1,709
2
13
14

It is missing the character `_` underline on the Regex pattern – danieltakeshi Oct 11 '17 at 12:41
Linked a great answer which should help OP to figure the pattern of his choice. – Imran Malek Oct 11 '17 at 19:56

danieltakeshi · Answer 2 · 2017-10-11T12:45:19.267

As @ImranMalek said, Regex would do it better, because it is taking too much time to loop on each cell and each letter of your code. However, if you still want to use in your entire sheet, use this:

Dim L As Long, LL As Long
Dim sCh As String, s As String
Dim IsSpecial As Boolean

For Each cell In ActiveSheet.UsedRange.Cells
    IsSpecial = False
    If IsEmpty(cell) = False Then
        s = CStr(cell)
        For L = 1 To Len(s)
            sCh = Mid(s, L, 1)
            If sCh Like "[0-9a-zA-Z]" Or sCh = "_" Then
            Else
                IsSpecial = True
                Exit For
            End If
        Next L
        If IsSpecial = True Then
            cell.Interior.ColorIndex = 3
        Else
            cell.Interior.ColorIndex = 4
        End If
    End If
Next

The code will color in green if the name is ok and red if nok.

And the result is:

Check excel file for special character _ and letter with Like Operator on entire Sheet

2 Answers2