I would like to write a procedure that would test a single character in a
string contained in a worksheet cell to see if it's a number, then test
another character in the same string to see if it's a letter. In this case,
the string to test is located in

Worksheets("dataEntry").Range("testCell")

I would like to test the first character in the string in that cell to see
if it's a number.
I would like to test the second character to see if it's a letter.

If either of these conditions returns false, I'd like the whole expression
to be false.

Thanks in advance.

Paul

set rng = Worksheets("dataEntry").Range("testCell")


if isnumeric(left(rng,1)) and _
lcase(mid(rng,2,1)) < ucase(mid(rng(2,1)) then

--
Regards,
Tom Ogilvy

"Paul James" wrote in message
...
I would like to write a procedure that would test a single character in a
string contained in a worksheet cell to see if it's a number, then test
another character in the same string to see if it's a letter. In this
case,
the string to test is located in

Worksheets("dataEntry").Range("testCell")

I would like to test the first character in the string in that cell to see
if it's a number.
I would like to test the second character to see if it's a letter.

If either of these conditions returns false, I'd like the whole expression
to be false.

Thanks in advance.

Paul

Paul,

Try this code

With Worksheets("dataEntry").Range("testCell")
If IsNumeric(Left(.Value, 1)) Then
If IsText(Mid(.Value, 2, 1)) Then
Debug.Print "Yes"
End If
End If
End With


Private Function IsText(val) As Boolean
IsText = (val = "a" And val <= "z") Or _
(val = "A" And val <= "Z")
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Paul James" wrote in message
...
I would like to write a procedure that would test a single character in a
string contained in a worksheet cell to see if it's a number, then test
another character in the same string to see if it's a letter. In this
case,
the string to test is located in

Worksheets("dataEntry").Range("testCell")

I would like to test the first character in the string in that cell to see
if it's a number.
I would like to test the second character to see if it's a letter.

If either of these conditions returns false, I'd like the whole expression
to be false.

Thanks in advance.

Paul

"Tom Ogilvy" wrote in message
...


if isnumeric(left(rng,1)) and _
lcase(mid(rng,2,1)) < ucase(mid(rng(2,1)) then


Smart!

a$=yourcell

numvalue= asc(left(a$,1))+asc(mid(a$,1))
if numvalue 9+9 then combination is false


---
Message posted from http://www.ExcelForum.com/

My thanks to Tom and Bob.

One other question:

I'd also like the expression to return true if the cell has no characters of
any type in it. Can I do that by saying

if rng < "" and_
if isnumeric(left(rng,1)) and _
lcase(mid(rng,2,1)) < ucase(mid(rng(2,1)) then

??

Wouldn't this return true if the entry was either 1a or a1, (which isn't
what I need)?



"dadum1 " wrote in message
...
a$=yourcell

numvalue= asc(left(a$,1))+asc(mid(a$,1))
if numvalue 9+9 then combination is false


---
Message posted from http://www.ExcelForum.com/

if rng = "" or _
( isnumeric(left(rng,1)) and _
lcase(mid(rng,2,1)) < ucase(mid(rng(2,1))) then

--
Regards,
Tom Ogilvy


Paul James wrote in message
...
My thanks to Tom and Bob.

One other question:

I'd also like the expression to return true if the cell has no characters
of
any type in it. Can I do that by saying

if rng < "" and_
if isnumeric(left(rng,1)) and _
lcase(mid(rng,2,1)) < ucase(mid(rng(2,1)) then

??

if rng = ""

Of course - Duh.

Thanks, Tom.

Tom - the code you wrote works great:

if rng = "" or _
( isnumeric(left(rng,1)) and _
lcase(mid(rng,2,1)) < ucase(mid(rng(2,1))) then

I did find a typo in the last expression - (the one to the right of the
inequality):

ucase(mid(rng(2,1)) should be written as ucase(mid(rng,2,1))

Thanks again for your help.

Paul