• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 94
  • Last Modified:

Range and #VALUE!

Folks,
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!”
Worksheets(“CONFIDENCE”).Range(“L9”).ClearContents
Range(“L9”).select
Exit Sub
End Select
End Sub

Open in new window

0
Frank Freese
Asked:
Frank Freese
  • 3
  • 2
2 Solutions
 
Glenn RayExcel VBA DeveloperCommented:
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
        Range("L9").ClearContents
        Range("L9").Select
    ElseIf Results = "0" Then 'zero character not allowed
        MsgBox "Invalid value for Significance - must not be zero!", vbcritial
        Range("L9").ClearContents
        Range("L9").Select
    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,
-Glenn
0
 
mekaneck84Commented:
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!")
        Range("L9").ClearContents
        Range("L9").Select
    Case CVErr(xlErrNum) '#NUM! error
        MsgBox ("Invalid value for Significance – must be greater than zero!")
        Range("L9").ClearContents
        Range("L9").Select
    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
0
 
Frank FreeseAuthor Commented:
Two options for the same problem! That's great.
Good work - thank you knidly
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Frank FreeseAuthor Commented:
Thank you very much
0
 
Glenn RayExcel VBA DeveloperCommented:
Glad we could help.  Always good to see various approaches to a solution; Excel & VBA are good about that.
0
 
Frank FreeseAuthor Commented:
Glenn,
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"?
Excel-Statistics.xlsm
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now