select case error

[ezil]

To evaluate multiple conditions i am using select case statement. But it
gives error
message if the cell contains "#n/a" value. It is not evaluating else
condition. In the below example the cell (a,2) value changes to 20 if the
curcell value = #n/a
How to achieve this? and also how to use multiple conditions in the below
statement for eg. case is >50 and less than 60

Select Case curcell.Value
Case Is > 50
Cells(a, 2) = 10
Case Else
Cells(a, 2) = 20
End Select

 

[Tom Ogilvy]

If iserror(curcell.Value) then
Cells(a, 2) = ?
else
Select Case curcell.Value
Case Is > 50
Cells(a, 2) = 10
Case Else
Cells(a, 2) = 20
End Select
end if

 

[Rick Rothstein \(MVP - VB\)]

To evaluate multiple conditions i am using select case statement. But it

gives error
message if the cell contains "#n/a" value. It is not evaluating else
condition. In the below example the cell (a,2) value changes to 20 if the
curcell value = #n/a
How to achieve this? and also how to use multiple conditions in the below
statement for eg. case is >50 and less than 60

Select Case curcell.Value
Case Is > 50
Cells(a, 2) = 10
Case Else
Cells(a, 2) = 20
End Select

You could trap the error that is generated...

If TypeName(Target.Value) = "Error" Then
MsgBox "Whoops! Bad cell value!"
Else
Select Case Target.Value
Case Is > 50
Cells(1, 2) = 10
Case Else
Cells(1, 2) = 20
End Select
End If

You can process the error anyway you want (I chose a MessageBox).

Rick

 

[ezil]

Thank you MR. Rick and Tom
How to use multiple conditions in select case statement for eg. if the case
is between 50 and 60?

 

[Rick Rothstein \(MVP - VB\)]

How to use multiple conditions in select case statement for eg.

if the case is between 50 and 60?

You can use the To keyword...

If TypeName(Target.Value) = "Error" Then
MsgBox "Whoops! Bad cell value!"
Else
Select Case Target.Value
Case 50 To 60
Cells(1, 2) = 10
Case Else
Cells(1, 2) = 20
End Select
End If

Here, the 50 and 60 are also included in the range of values.

Rick