Function not working quite as required
 

Stuart,

Easiest way is to force 2 digits with a letter, e.g. P01.

Otherwise you need to first test whether the first character is a letter or
a number and Case that, e.g.

Select Case Asc(Left(R.Value,1))
Case 65 To 90
etc

--

HTH

Bob Phillips

"Stuart" wrote in message
...
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

Function not working quite as required
 

Many thanks to you both.

Regards.

"Bob Phillips" wrote in message
...
Stuart,

Easiest way is to force 2 digits with a letter, e.g. P01.

Otherwise you need to first test whether the first character is a letter
or
a number and Case that, e.g.

Select Case Asc(Left(R.Value,1))
Case 65 To 90
etc

--

HTH

Bob Phillips

"Stuart" wrote in message
...
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






---
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