Turn formula into Select

[Hile]

Can someone turn the following formula into a select statement:
IF(ISBLANK(M4),"",IF(ISNUMBER(--MID(M4,2,1)),"Manual","Auto"))

Basically I'm tagging a record based on a value, if the second character in
the string contained in the specific column (field) is a number I want this
column to say "Manual" esle Auto and leave blank if no value.

I'm migrating this report into BI and I can't figure out how to do this in
their interface, but I can write select statement for it. It will be a
calculation based on the field containing the data.

Thanks

 

[Mike H]

Do you mean the VB select case? if you do try this:-

Sub sonic()
mystring = Mid(Range("M4"), 2, 1)
Select Case mystring
Case Chr(48) To Chr(57)
myvalue = "Manual"
Case Is <> ""
myvalue = "Auto"
Case Else
myvalue = ""
End Select
End Sub

 

[Rick Rothstein \(MVP - VB\)]

See inline...

Do you mean the VB select case? if you do try this:-

Sub sonic()
mystring = Mid(Range("M4"), 2, 1)
Select Case mystring
Case Chr(48) To Chr(57)

This might be more readable than the above...

Case "0" To "9"

Rick

 

[Hile]

Actually if the second character in the string is 'text' and not a number
then it is "auto" not if it is blank. If the field is blank then I want no
tag ("")

Sorry for cofusion.

 

[Rick Rothstein \(MVP - VB\)]

That is what Mike's code is doing. Case statement are executed in the order
they are listed in. If the mystring variable (which was assigned the 2nd
character of the contents of cell M4) is a digit, it is caught by the first
case statement and Manual is assigned to myvalue. If mystring is not a digit
then the 2nd Case statement looks at it. The Is operator means do a String
test of mystring (the argument to the Select Case statement itself) and that
test is to see if the variable is not empty, that is, does not equal "" (the
less than/greater than signs <> mean "not equal"). So, if it is not empty,
and we know it isn't a digit (because the 1st Case statement didn't catch
it), then it must contain a non-digit; so "Auto" is assigned to myvalue. If
neither of the first two Case statements catch it, then that can only be
because mystring didn't contain a digit or a non-digit... the only what that
could happen is if mystring was empty, so "" is assigned to myvalue.

Rick

 

[Mike H]

Rick,

Yes your suggestion is more intuitive then my ASCII test, thanks for that. I
perhaps should also have included

If Len(Range("M4").Value) < 2 Then End

to test for 1 character in the cell which in my code would have evaluated as
blank when the cell wasn't empty.

Mike