PDA

View Full Version : search for any character



bartoni
07-16-2004, 07:33 AM
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
07-16-2004, 08:12 AM
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
07-16-2004, 08:16 AM
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
07-16-2004, 08:19 AM
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
07-16-2004, 08:26 AM
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
07-16-2004, 08:30 AM
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
07-16-2004, 08:42 AM
Sample data

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

What version of excel are you using?

bartoni
07-16-2004, 08:46 AM
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
07-16-2004, 09:01 AM
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(c) 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
07-16-2004, 09:04 AM
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
07-16-2004, 09:31 AM
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(c) 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
07-16-2004, 02:10 PM
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.