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


    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!