Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: IsNumeric

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I may be using this wrong. Can someone shed some light on it for me? I have the following in my code:
    If IsNumeric("D9") = False Then
    Code
    End If

    The problem is, even when Cell D9 contains a number I am returned a False causing the code to execute, which I dont want.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the following: -

    If Not IsNumeric(Range("D9").Value) Then
    Code
    End If

    Brought to you courtesy of the Breeders and Cannonball.


  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you, I shall give that a try

  4. #4
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thats because your not refering to a cell your just testing a text string i.e. what ever you put into quotes is text. Here this code will do the job for you.

    If Not IsNumeric(Trim(Range("D9").Value)) Then
    'place code here
    End If

  5. #5
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Mudface solution is almost correct other then it will test a blank cell as holding a value of Zero.... which is a number! If you don't want blank cells testing true as a number then use my solution with the added trim function in it.
    If Not IsNumeric(Trim(Range("D9").Value)) Then
    'place code here
    End If


    [ This Message was edited by: Nimrod on 2002-05-04 18:26 ]

    [ This Message was edited by: Nimrod on 2002-05-04 18:27 ]

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok, Nimrods worked, but I will be darned if I know why. I dont understand this one at all. That being said, thanks a lot, you got me going

  7. #7
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Without trimming you have an empty string which is evaluated to zero. The trim command means that the empty string is removed therefore eliminating the evaluation to Zero.

  8. #8
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nimrod, can we get in a chat somewhere, you may be giving me the answer to a question that has nagged me for a long time concerning "empty" cells.

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aye, sorry, should've been the code below to check for a blank cell too: -

    If Not IsNumeric(Range("D9").Text) Then
    Code
    End If

    All the code is saying is that if the cell d9 contains anything but a number then ...

  10. #10
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    My email is presently down .. is there any chat rooms on this site ? ... or you could ask the question here ?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •