IsNumeric in a Range 2

[V00D00]

Can someone give me some sample VBA code to determine if any cell in a selected range has a numeric value?

VD

 

[V00D00]

I should add that if any of the cells are not numeric, I would like to insert a new column.

Right now this is what I have.

For Each n In Range("A2:A13")      
    If Not IsNumeric(n) Then
         Msgbox "Numeric", vbOKOnly, "What is What"
    End If
Next n

This will go through each cell and determine if the cell value is numeric.

How do I break out of the For Each if a non numeric value is found so I can insert a new column? If I just use the if as is, I will end up iserting a new column every time I encounter a non numeric value.
VD

 

[PHV]

For Each n In Range("A2:A13")
If Not IsNumeric Then
Msgbox "Numeric", vbOKOnly, "What is What"
[!]Exit For[/!]
End If
Next n

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[firefytr]

For a large range, you can do away with the loop...

Sub CheckForNumerics()
    Dim r As Range, s As Range
    Set s = Selection
    On Error Resume Next
    Set r = s.SpecialCells(xlCellTypeConstants, 1)
    If Not r Is Nothing Then MsgBox "There are numerics in the selection."
    s.Select
End Sub

The "On Error Resume Next" is there because an error will be generated if there are no special cells of that type available in the defined range.

HTH

-----------
Regards,
Zack Barresse