Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turn formula into Select
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 -- Hile |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turn formula into Select
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 "Hile" wrote: 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 -- Hile |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turn formula into Select
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 myvalue = "Manual" Case Is < "" myvalue = "Auto" Case Else myvalue = "" End Select End Sub "Hile" wrote: 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 -- Hile |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turn formula into Select
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. -- Hile "Mike H" wrote: 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 "Hile" wrote: 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 -- Hile |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turn formula into Select
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 "Hile" wrote in message ... 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. -- Hile "Mike H" wrote: 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 "Hile" wrote: 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 -- Hile |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Turn formula into Select
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 "Rick Rothstein (MVP - VB)" wrote: 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 myvalue = "Manual" Case Is < "" myvalue = "Auto" Case Else myvalue = "" End Select End Sub "Hile" wrote: 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 -- Hile |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you turn off the formula bar? | New Users to Excel | |||
Want formula to turn to value by itself | Excel Discussion (Misc queries) | |||
Can I turn off the auto-select in Functions? | Excel Worksheet Functions | |||
Turn Off Formula ? | New Users to Excel | |||
Turn OFF Formula Bar??? | Excel Programming |