Determine Values In A Range Are Numeric

I am trying to create a macro that determines if a range I am selecting has any non-numeric fields. If it finds say an cell beginning with a letter a message box appears letting the user know and possibly give the cell and value it found.
Any suggestions?

Re: Determine Values In A Range Are Numeric

Sorry I was not being clear at the end.
Show a messagebox giving the alpha value it found and the cell it was in.
I don't require an ELSE after the IF...just a simple IF .. END IF

Quote from Kenneth Hobson

I don't understand the last part. It shows a MsgBox if a cell is not numeric, but then what?

Of course users can do a Ctrl+G and Special to find such things.

Re: Determine Values In A Range Are Numeric

That did seem to work thank you!

Quote from WinteE

This code should do the job :

Code

Sub FindNonNumerics()


For Each i In Range("Range")
    If Not IsNumeric(i) Then
        MsgBox ("A non numeric item has been found, it's value is : " & i.Value)
    End If
Next i


End Sub

Display More

Erik

Re: Determine Values In A Range Are Numeric

I just realized that there are 2 non-numeric exceptions I do not want a messagebox to apper on: the words BASE and A/W.
How could I add this to the IF statement below to skip over those 2 situations? (an example below is what I'm thinking of)

For Each i In Range("Range")
If Not IsNumeric(i) And (i<>"BASE" or i<>"A/W") Then

Quote from WinteE

This code should do the job :

Code

Sub FindNonNumerics()


For Each i In Range("Range")
    If Not IsNumeric(i) Then
        MsgBox ("A non numeric item has been found, it's value is : " & i.Value)
    End If
Next i


End Sub

Display More

Erik

Re: Determine Values In A Range Are Numeric

Perfect thank you! I really appreciate it http://www.ozgrid.com/forum/images/smilie/smile.gif

Quote from Kenneth Hobson

Code

Sub FindNonNumerics()
  Dim cell As Range
    For Each cell In [A1:A10]
        If Not IsNumeric(cell) And cell.Value <> "BASE" And cell.Value <> "A/W" Then
            MsgBox ("Non-numeric cell, " & cell.Address & ", value is : " & cell.Value)
        End If
    Next cell
End Sub