Macro for finding unwanted cell contents

[rhino4eva]

I have a excel sheet that has a single column starting in A1 and ending at A96. Each cell in this column should contain a 7 digit number apart from two cells that contain the letters "CMV" and "NEG". i need a vba macro to search form a1 to a96 and alert the user with a message box if any cell contains any text other than "CMV" and "NEG" or is not a 7 dgit number

has anyone any ideas

 

[VoG]

Try

Sub test()
Dim i As Long
For i = 1 To 96
    With Range("A" & i)
        If IsNumeric(.Value) Then
            If Len(.Value) <> 7 Then MsgBox "Row " & i & " numeric <> 7 digits"
        Else
            If .Value <> "CMV" And .Value <> "NEG" Then MsgBox "Row " & i & " not CMV or NEG"
        End If
    End With
Next i
End Sub

 

[rhino4eva]

thanks for that but a slight problem !!!
it detects "0" in empty cells

 

[VoG]

You didn't say that cells could be blank

Sub test()
Dim i As Long
For i = 1 To 96
    With Range("A" & i).SpecialCells(xlCellTypeConstants)
        If IsNumeric(.Value) Then
            If Len(.Value) <> 7 Then MsgBox "Row " & i & " numeric <> 7 digits"
        Else
            If .Value <> "CMV" And .Value <> "NEG" Then MsgBox "Row " & i & " not CMV or NEG"
        End If
    End With
Next i
End Sub

 

[rhino4eva]

sorry about that...
tried the new code but it debugs

 

[shg]

Maybe ...

Sub test()
    Dim cell      As Range
    Dim v         As Variant
 
    If WorksheetFunction.CountA(Range("A1:A96")) Then
        For Each cell In Range("A1:A96") _
            .SpecialCells(xlCellTypeConstants, _
                          lNumbers + xlTextValues + xlLogical + xlErrors)
            v = cell.Value2
            Select Case VarType(v)
                Case vbDouble
                    If v < 1000000 Or v > 9999999 Then GoTo Oops
                Case vbString
                    If v <> "CMV" And v <> "NEG" Then GoTo Oops
                Case Else
                    GoTo Oops
            End Select
        Next cell
    End If
    Exit Sub
 
Oops:
    cell.Select
    MsgBox "Baaaad!"
End Sub

 

[rhino4eva]

hi shg
tried your code but it does not quite there
i dont understand the line

If v < 1000000 Or v > 9999999 Then GoTo Oops

i want it to goto Oops if number is not 7 digits long .. ihave tried with
1, 10, 100 etc and it still passes

 

[shg]

All positive 7-digit numbers are between 1000000 and 9999999, so if it's outside that range, it isn't 7 digits.

EDIT: That's curious. This continuation line ...

                          lNumbers + xlTextValues + xlLogical + xlErrors)

should be

                          [COLOR=red]x[/COLOR]lNumbers + xlTextValues + xlLogical + xlErrors)

 

[rhino4eva]

thanks SHG ... works a treat

 

[rhino4eva]

i know i am pushing my luck shg but if i wanted to exclude a string containing "RPT"and a 7 digit number how would i do that