Function not working quite as required
 

My mistake, I overlooked that one. Instead:

Function ValCategory(R As Range) As Byte
'1 = capital letter
'2 = number 1 to 99
'3 = 1 + 2
'4 = something else
Select Case Len(CStr(R.Value))
Case 1
Select Case Asc(R.Value)
Case 48 To 57
ValCategory = 2
Case 64 To 90
ValCategory = 1
Case Else
ValCategory = 4
End Select
Case 2
If IsNumeric(R.Value) Then
Select Case R.Value
Case 1 To 99
ValCategory = 2
Case Else
ValCategory = 4
End Select
Else
Select Case Asc(R.Value)
Case 64 To 90
If IsNumeric(Mid(R.Value, 2)) Then
Select Case Val((Mid(R.Value, 2)))
Case 1 To 99
ValCategory = 3
Case Else
ValCategory = 4
End Select
Else
ValCategory = 4
End If
Case Else
ValCategory = 4
End Select
End If
Case 3
Select Case Asc(R.Value)
Case 64 To 90
If IsNumeric(Mid(R.Value, 2)) Then
Select Case Val((Mid(R.Value, 2)))
Case 1 To 99
ValCategory = 3
Case Else
ValCategory = 4
End Select
Else
ValCategory = 4
End If
Case Else
ValCategory = 4
End Select
Case Else
ValCategory = 4
End Select
End Function

--
HTH. Best wishes Harald
Excel MVP
Followup to newsgroup only please

"Stuart" skrev i melding
...
Function ValCategory(R As Range) As Byte
'1 = capital letter
'2 = number 1 to 99
'3 = 1 + 2
'4 = something else
Select Case Len(CStr(R.Value))
Case 1
Select Case Asc(R.Value)
Case 48 To 57
ValCategory = 2
Case 64 To 90
ValCategory = 1
Case Else
ValCategory = 4
End Select
Case 2
If IsNumeric(R.Value) Then
Select Case R.Value
Case 1 To 99
ValCategory = 2
Case Else
ValCategory = 4
End Select
Else
ValCategory = 4
End If
Case 3
Select Case Asc(R.Value)
Case 64 To 90
If IsNumeric(Mid(R.Value, 2)) Then
Select Case Val((Mid(R.Value, 2)))
Case 1 To 99
ValCategory = 3
Case Else
ValCategory = 4
End Select
Else
ValCategory = 4
End If
Case Else
ValCategory = 4
End Select
Case Else
ValCategory = 4
End Select
End Function

If the value of R is P1 then when the function executes, it
jumps to Case2 and then deduces that Valcategory is 4
....not quite what was intended (should be Case3).

What is happening, please?

Regards.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.518 / Virus Database: 316 - Release Date: 11/09/2003