Find a single zero, special charcters and return row number

[Jomar153]

I need a macro to identify special characters and single zero. Then highlight those rows and return with the row number in a pop up window.

Example of list in column A:

98%kl
45tds$
123450
0
CE 123

This is what I have so far, but I can't find the way to highlight rows with a single zero ("0") and can't make it return the row numbers


Any help will be appreciated

Sub test()
 
    Dim rng As Range, r As Range, m As Object
    Set rng = Range("A1", Range("A" & Rows.Count).End(xlUp))
    rng.Interior.ColorIndex = xlNone
    With CreateObject("VBScript.RegExp")
        .Global = True
        .IgnoreCase = True
        .Pattern = "([\(\)\\""!@#\$.%\^&\*\+\?~\€Ž\öôòóõ\~žŸœ¡¢£¤¥¦§®¯°±²³´µ¶·¸¹º»¼½¾¿àáâãäåæçèéêëìíîïðñòóôõö×øùúûü\-]|CLN|DG |CE | )"
        For Each r In rng
            If .test(r.Value) Then
                r.Interior.Color = vbYellow
                For Each m In .Execute(r.Value)
                    With r.Characters(m.firstindex + 1, m.Length).Font
                        .Bold = True
                        .Color = vbRed
               If rng Is Nothing Then
        MsgBox "No violations found"
        Else
        MsgBox "Violation found at " & rng.Row
 
                          End If
                    End With
                Next
            End If
        Next
    End With
End Sub

[zmbd]

Jomar153:

I just spotted this thread, are you still working on this issue?

Do you need the cell or the entire row highlighted (cell would be easier)?

Do you need to highlight something with two zeros, for example: "x00x" or "0x0x", or just a cell containing only a single "0"

Can we simplify your search to strings that only contain alphanumerics less the number 0, ([a-z][A-Z][1-9])?

what about spaces "x x" or "xxxx xx" etc...?

Do you really want a single message box with all of the row numbers in one go, or do you really need a message box each time the offending cell is located?

Line 21

MsgBox "Violation found at " & rng.Row

should read
Line 21

MsgBox "Violation found at " & r.Row

Line 17: If rng Is Nothing Then
I think you are testing the wrong object here... and if you are testing for an empty cell then this isn't the proper syntax/function.

[hvsummer]

@jomar153: Welcome to Bytes.com
I'm not familiar to your aproach with "VBScript.RegExp" (NeoPA - Mod - has explained it, but I still confusing)

So I give your my approach, another way to determind special character and the Zero alone, base on ACSII code:

Public function FindWeirdCharacter()
 
Dim i as integer, n as integer
Dim RngArray As Variant
 
rngAray = Range("A1", Range("A" & Rows.Count).End(xlUp)).value
Range("A1", Range("A" & Rows.Count).End(xlUp)).Interior.ColorIndex = xlNone
 
 for i = 33 to 254
   if not (i > 47 and i < 58) and not (i > 64 and i < 123) then
     for n = Lbound(RngArray) to Ubound(RngArray)
       if instr(1, RngArray(n,1), chr(i),VbBinaryCompare) > 0 or Cstr(RngArray(n,1)) = "0" then
         'Change Color for the cell that fullfill condition here (all special character and the alone-Zero here)
         'Give Code here
       end if
     next n
   end if
 next i 
 
end function
 

As I can see, your range from A1 and Xlup only in Column A mean your rng only in Column A that start from A1 then the rows position will be the var "n", but if you find a lot of row, and each row turnon Msgbox, you'll deal with lot of msgbox. That would be annoying.

so I suggest only change colour for the violent cells and you filter data by color to fix them or do whatever you want.

[Jomar153]

zmbd:

I need to identify cells containing any of the following:
special characters
spaces
cell containing only a single "0"

I don't need the entire row highlighted, a highlighted cell would be just fine. I prefer a message box each time the offending cell is located and activate it so that I can also see numbers listed before and after the offending cell.

[codegazer]

@hvsummer

In your post, line 10 reads-

 if not (i > 47 and i < 58) and not (i > 64 and i < 123) then
 

I realise that you're trying to identify the alpha-numeric characters, but ascii 91 - 96 are special characters as well.

[zmbd]

ok,
still not 100% clear on the "single zero"

Highlight a cell with this "xxxxx0xxxxx" a single zero anywhere within the text; however, do not highlight a cell that contains two or more zeros anywhere within the text either as adjacent or separated entries within the text.

[codegazer]

I take it that the 'single zero' refers just to "0" with no other text in the cell, as referred to in the example data.

[zmbd]

codegazer: Let us not guess here... too many times have we guessed and then played the "20 questions game."

Sorry, today, I do not have time to play 20 questions... indeed, working in the lab, I usually have 10 to 15 minute blocks of time to help; thus, playing 20 questions just lengthens the time it takes to get to solution....

[Jomar153]

zmbd:

codegazer is right, the 'single zero' refers just to "0" with no other text in the cell.

[codegazer]

One other question springs to mind.

Do you just want to identify and highlight cells with errors?
You then still have these cells to deal with, but you could also remove the problem characters at the same time as the scan if you wanted.

This link appears to show a very similar search.
http://www.devguru.com/technologies/vbscript/14108

[Jomar153]

codegazer:

I do not want to remove the characters. I just need to identify and highlight the offending cells.

[zmbd]

I leave the actual application up to OP; however, this is my take:

>Open VBE [Alt][F11]
>Insert a standard module
>Copy and paste the following code block in to the module
>Open the immediate pane/window [Ctrl][G]
>In the immediate pane type the following:
?fncB64113("StringHere")

Replace the "StringHere" with whatever you want to test
The number of matching characters will be returned, and in this case displayed in the immediate pane.
i.e.
?fncB64113("StringHere") = 0
?fncB64113("String%%Here") = 2
?fncB64113("0") = 1
?fncB64113("String0Here") = 0
?fncB64113("1") = 0
etc...

Option Explicit '<<This may already be in your module
 
Function fncB964113(zInString As String) As Integer
    Dim zRegExp As Object
    Dim zMatch As Object
    Dim zo As Object
    Dim zTestString As String
    '
    'error trapping
    On Error GoTo zerrtrap
    '
    'With the argument being a string data type, this shouldn't be an issue; however, just incase someone changes the datatype to varient to handle null values
    If (zInString & "") = "" Then Err.Raise Number:=(-2147221504 + 1000), Source:="fncB964113", Description:="Required input string missing"
    zTestString = zInString
    '
    'setup the regular expressions object using late binding to avoid reference errors
    Set zRegExp = CreateObject("VBScript.RegExp")
    With zRegExp
        .Global = True
        .ignorecase = True
        'will set the pattern to match later
    End With
    '
    'Test the input string for possible matching
    If Len(zTestString) > 1 Then
    '
    'No need to test for isolated zero test for only non-alphanumerics
        With zRegExp
        '
        'I tried various \d \w etc... found this to be more reliable
            .Pattern = "([^a-z^A-Z^0-9])"
            Set zMatch = .Execute(zTestString)
            fncB964113 = zMatch.Count
        End With
    Else
    '
    'Only one character in the string, so check to see if it is a zero
    '> of course, one could use instr() or even a simple
    '> if/then construct; however, the RegEx object is open so why not use it?
        With zRegExp
            .Pattern = "(0)"
            Set zMatch = .Execute(zTestString)
            fncB964113 = zMatch.Count
        End With
    End If
zcleanup:
    If Not zRegExp Is Nothing Then Set zRegExp = Nothing
    If Not zMatch Is Nothing Then Set zMatch = Nothing
Exit Function
zerrtrap:
    MsgBox prompt:=Err.Source & vbCrLf & Err.Number & vbCrLf & Err.Description
    fncB964113 = 0
    Resume zcleanup
End Function

Written as a function, this should be really easy to integrate in to the existing logic... speaking of logic, one could change the fncB964113 from integer to Boolean and then return true or false instead of the count... up to the end user. ;-)

This has been tested and works within Office2013, all applications.

[zmbd]

opps... forgot the address thing :)
Range.Address Property (Excel)
Example there...

and you might find how I handled ranges in this post's code useful: https://bytes.com/topic/excel/answer...el#post3799741