 # 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

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

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

This code should do the job :

Erik

• Re: Determine Values In A Range Are Numeric

That did seem to work thank you!

Quote from WinteE

This code should do the job :

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)

Code
``````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 :

Erik

• Re: Determine Values In A Range Are Numeric

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``````
• 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``````

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!