Search for any character

[bartoni]

Hi,

I basically want to search for any chracters other than numbers and semi-colons from Row 2 and column D to column AG and to put up an error if detected. Ive used this as an e.g but it only picks up numbers greater than 7. I want this but also any character other than a semicolon.
Thanks

For Each c In Range("d2", Range("d65536").End(xlUp))
    If Not IsEmpty(c) And c.Value > 7 Then
        If MsgBox("Order of Entry; You have a number higher than 7 in cell " & c.Address, 1, "Out of Parameters") = vbCancel Then
            c.Select
            Exit Sub
        End If
    End If
Next c

[Tommy]

Hi bartoni,

I have modified the sub you posted to catch the semicolon, a value greater than 7, and check for alpha. Let me know if this is incorrect.

Sub LookAtIt()
Dim c As Range
For Each c In Range("d2", Range("D65536").End(xlUp))
    If Not IsEmpty(c) And c.Value > 7 And VarType(c.Value) <> vbString Then '<- check for numeric values only
        If MsgBox("Order of Entry; You have a number higher than 7 in cell " & c.Address, 1, "Out of Parameters") = vbCancel Then
            c.Select
            Exit Sub
        End If
    ElseIf Not IsEmpty(c) And VarType(c.Value) = vbString And InStr(1, c.Value, ";") = 0 Then '<-- check for alpha without semicolons
        If MsgBox("Order of Entry; You have an alpha entry in cell " & c.Address, 1, "Out of Parameters") = vbCancel Then
            c.Select
            Exit Sub
        End If
    End If
    MsgBox c.Value
Next c
End Sub

[bartoni]

It just seemed to list the value within column D in a msgbox for each cell down in column D. Not sure why though - any other ideas??

[Tommy]

Sorry that is MsgBo c.Value delete that and the MsgBox will dissapear. When you say you are looking for a semicoln, and alpha could you explain?

[bartoni]

I load my sheets into a application and the values in each column are predetermined. If these are exceeded, the aplication throws the value out. Thus ive tried to find a way to determine errors before they are loaded into the application. The only values allowed in column D for ex. are 1-7 and a semicolon. Anything else i.e a character will cause an error when loading, so im trying to find any other characters present in column D. I actually have 15 columns of data but am going to modify code accordingly when I get the code in 1 column to work..

[bartoni]

Also when i ran your code with the amended version, if there is a character in the D column, the macro stops and tries to debug.

[Tommy]

Sample data

agfdasgfd; <- no msgbox
agfdasgfd <- msgbox
12 <- msgbox
7 <- no msgbox
; <- no msgbox

What version of excel are you using?

[bartoni]

97, Ok in column D i want values of 1-7 and ;

So

agfdasgfd; <- msgbox giving errors as alpha even though there is a semicolon
agfdasgfd <- msgbox giving errors as alpha
12 <- msgbox as over 7
7 <- no msgbox
; <- no msgbox
7; <- no msgbox

7, <- msgbox as comma

Thanks

[Tommy]

Ok I am using Excel 2000 so I have to figure it out, sorry. The below sub picks up the 7, and should work.

Sub LookAtIt()
    Dim c As Range
    For Each c In Range("d2", Range("D65536").End(xlUp))
    If Not IsEmpty© Then
        If c.Value > 7 And InStr(1, c.Value, ";") = 0 Then     '<- check for numeric values only
            If MsgBox("Order of Entry; You have a number higher than 7 in cell " & c.Address, 1, "Out of Parameters") = vbCancel Then
                c.Select
                Exit Sub
            End If
        ElseIf Len(CStr(c.Value)) > 1 Then
            If c.Value <= 7 And InStr(1, c.Value, ";") = 0 Then
                If MsgBox("Order of Entry; You have a number higher than 7 in cell " & c.Address, 1, "Out of Parameters") = vbCancel Then
                    c.Select
                    Exit Sub
                End If
            End If
        End If
    End If
    Next c
End Sub

[bartoni]

yEP THAT WORKS BUT IF YOU PUT A CHARACTER IN THE (sorry Caps) range such as an"x" it tries to debug the code.

[Tommy]

This should catch the x now.

Sub LookAtIt()
    Dim c As Range
    For Each c In Range("d2", Range("D65536").End(xlUp))
    If Not IsEmpty© Then
        If Val(c.Value) > 7 And InStr(1, c.Value, ";") = 0 Then     '<- check for numeric values only
            If MsgBox("Order of Entry; You have a number higher than 7 in cell " & c.Address, 1, "Out of Parameters") = vbCancel Then
                c.Select
                Exit Sub
            End If
        ElseIf Len(CStr(c.Value)) > 1 Then
            If Val(c.Value) <= 7 And InStr(1, c.Value, ";") = 0 Then
                If MsgBox("Order of Entry; You have a number higher than 7 in cell " & c.Address, 1, "Out of Parameters") = vbCancel Then
                    c.Select
                    Exit Sub
                End If
            End If
        ElseIf Len(CStr(c.Value)) = 1 Then
            If Val(c.Value) = 0 And InStr(1, c.Value, ";") = 0 Then
                If MsgBox("Order of Entry; You have a number higher than 7 in cell " & c.Address, 1, "Out of Parameters") = vbCancel Then
                    c.Select
                    Exit Sub
                End If
            End If
        End If
    End If
    Next c
End Sub

[parry]

Hi, Bartoni Im confused as to what your requirements are. First you say you want to search for any values that are not a number or a semi-colon, then you say you want a number greater than 7 (as if 0-6 arent numbers)? Could you clarify exactly what the criteria is. And are you solely looking at the value of the cell or the characters within the value.

Just some thoughts...
1. You cannot check for a value of a cell that is in error. You should use IsError before evaluating the value of a cell.
2. IsNumeric may be handy as this tells you whether you are dealing with a number (cannot contain ; etc). ie If IsNumeric(Cell) and >=7 then OK?
3. Maybe the Find method may be an option but dunno yet.