Cells can only contain characters 0 thru 9

[keith05281967]

Greetings Coders,

How can I have vba loop thru a range and check that the contents only contain the characters 0 thru 9. I say characters because they aren't numbers, rather work id's. Usually formatted as general or text. So something like...


<TABLE style="WIDTH: 135pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=180><COLGROUP><COL style="WIDTH: 135pt; mso-width-source: userset; mso-width-alt: 6582" width=180><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 135pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 width=180>870232</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>10213#6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>9706371</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>20087964</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>20477341</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>20686054</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17></TD></TR></TBODY></TABLE>
If Cells (x,y) <> (0,1,2,3,4,5,6,7,8,9) then
msgbox "hey i found a non numeric character"

As I mentioned, the range is work ID's so IsNumeric doesn't apply.


thanks,
Keith

 

[Scott Huish]

Why does IsNumeric not apply?

Sub test()
Dim c As Range
For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    If IsNumeric(c) Then
            If Int(c) <> c Then MsgBox "Invalid Entry Found in Cell " & c.Address(0, 0)
    Else
            MsgBox "Invalid Entry Found in Cell " & c.Address(0, 0)
    End If
Next
End Sub

 

[Scott Huish]

It could also be done like this:

Sub test()
Dim c As Range
For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    If IsNonNumeric(c.Text) Then MsgBox "Invalid Entry found in Cell " & c.Address(0, 0)
Next
End Sub

Function IsNonNumeric(r As String) As Boolean
With CreateObject("vbscript.regexp")
    .Pattern = "\D"
    IsNonNumeric = .test(r)
End With
End Function

 

[keith05281967]

Hi HotPepper,

You are right. IsNumeric will work after all. I was just using the wrong syntax on it before, but I got it working now. Thank you for all of the suggestions. My 1st attempt looked something like... If Cells(i, ColumnNumber).IsNumeric = False Then...but it didn't like that. Below is what i got working:


If (IsNumeric(Cells(i, ColumnNumber))) = False Then
Cells(i, ColumnNumber).Interior.ColorIndex = 4
intCounter3 = intCounter3 + 1
End If


thanks,
Keith

 

[pgc01]

... IsNumeric will work after all ...

Hi Keith

I would not use IsNumeric() to test for only digits. IsNumeric() tests if it is possible to convert the string into a number which is clearly not what you need.

Examples of string that test ok with IsNumeric() and that have characters other than digits:

11.
11.1
11E1
+234
-234
234-
234+
1,2,3,4
11 (with spaces after the 11)
11 (with spaces before the 11)

I'd use something like this, that tests if all the characters are digits (non empty string)

Sub AllDigits()
Dim s As String
 
s = "123#45"
Debug.Print s Like String(Len(s), "#")
End Sub

 

[Scott Huish]

To a point you are correct, for some of those, my first code correctly identified as being incorrect.

My 2nd code I posted found all the examples.

 

[pgc01]

My 2nd code I posted found all the examples.

I agree completely. The code that uses the RegExp will identify all incorrect entries. I was just saying that using IsNumeric() on the cell value will not do the job.