Range and #VALUE!

Posted on 2014-08-17
Last Modified: 2014-08-18
What I'm trying to learn to do is to use Case statements.
In the example below, (I don't know how close I was in the structure) my objective is to evaluate L9 based upon several conditions that will generate an error after the function has been calculated. The conditions are L9 cannot be nonnumeric or a #VALUE! error is generated and L9 ≤ 0 ≥ or a #NUM! error is generated. I would like to be able evaluate L9 telling the user a value (s) is incorrect before the user goes to the next cell, if possible. Else I'm open for suggestions.
I don't know if I'm in the right event or if there needs to be two events - one for Worksheet_Change and another for Worksheet_Calculate.
Note, I only show one MsgBox in the code below. I would have to show a second one for the #NUM! error.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Results

Select Case Results
Case Is Range(“L9”).NumberFormat= 
IF L9 returns a #Value! Error value
Msgbox(“Invalid value for Significance – must be numeric!”
Exit Sub
End Select
End Sub

Open in new window

Question by:Frank Freese
    LVL 27

    Assisted Solution

    by:Glenn Ray
    Two things:

    First, Select...Case is best used when there are more than two possible test conditions.  Say, if one were testing for "Apples", "Oranges", "Pears", "Bananas", and then anything else.  Your example above really only tests for two possible states:
    a) non-numeric value
    b) zero
    So, in this case, I'd recommend If...Then...ElseIf.... like so:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim Results As Variant
        Results = Range("L9").Value
        If Not (IsNumeric(Results)) Then 'not a number
            MsgBox "Invalid value for Significance – must be numeric!", vbCritical
        ElseIf Results = "0" Then 'zero character not allowed
            MsgBox "Invalid value for Significance - must not be zero!", vbcritial
        End If
    End Sub

    Open in new window

    Secondly, the syntax for a Select...Case statement is usually along the lines of
        Select Case Results
            Case 0
            Case 1
            Case 2
            Case 3 To 10
            Case Is > 10
        End Select

    Open in new window

    where one is testing a variety of possible values or ranges of values for a specific variable.  That really isn't practical here.  You could of course test for zero, then have another test for alphanumeric content, but the statement
    Not (IsNumeric(Results))
    will only return TRUE or FALSE, not the actual value contained in Results (L9).

    Hope that helps,

    Accepted Solution

    You may want to look into the basics of the "select case" statement to understand how it should be formatted. Here's a good link: Select Case Statement

    Here's a little code to get you started:

    Sub Test()
    Dim varInput As Variant
    varInput = Range("L9").Value
    Select Case varInput
        Case CVErr(xlErrValue) '#VALUE! error
            MsgBox ("Invalid value for Significance – must be numeric!")
        Case CVErr(xlErrNum) '#NUM! error
            MsgBox ("Invalid value for Significance – must be greater than zero!")
        Case Else
            MsgBox ("Input is OK")
    End Select
    End Sub

    Open in new window

    See Chip Pearson's page for information on the CVErr function: Returning Errors from VBA

    Author Comment

    by:Frank Freese
    Two options for the same problem! That's great.
    Good work - thank you knidly

    Author Closing Comment

    by:Frank Freese
    Thank you very much
    LVL 27

    Expert Comment

    by:Glenn Ray
    Glad we could help.  Always good to see various approaches to a solution; Excel & VBA are good about that.

    Author Comment

    by:Frank Freese
    Got a problem with your code. I've attached the w/b and the w/s I'm working with is labeled "CONFIDENCE"
    When the w/s is activated I set values to ranges L9:L11 and clear any values in R11,O13:P13: and R13:S13. This sets up the w/s for a user to use provided data. There is a command button labeled "Try Again" that is assigned to a macro. This will clear all the data so the user can enter their own if the wanted to (I haven't finished that yet).
    If you look under "Remarks" in the w/s you will find that certain values are not excepted in certain cells (L9:L11 - I'm working only on L9 at this time) w/o creating an error. If the user elects to change any values in L9:L11 the worksheet_change event is triggered. In that event I have call a module labeled "UserInput" ( this is your code 'slightly' modified).
    I am looping through the Workbook_Change event in call "UserInput".
    The best way to see what's happening would be enter in L9 text such as your name. I place a break in the modUserInput module and stepped through it.
    Would you prefer a new question "Looping"?

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now