Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
emulating if else Like statements with case
I have been given code of:
Do Until Selection = "" If Selection Like "A*" Then Selection.FormulaR1C1 = "AL" ElseIf Selection Like "G*" Then Selection.FormulaR1C1 = "GL" ElseIf Selection Like "P*" Then Selection.FormulaR1C1 = "PET" ElseIf Selection Like "B*" Then Selection.FormulaR1C1 = "BM" ElseIf Selection Like "1*" Then Selection.FormulaR1C1 = "PET" ElseIf Selection Like "2*" Then Selection.FormulaR1C1 = "HDPE" Else End If Selection.Offset(1, 0).Select Loop 'Until Selection = "" and was thinking of changing to: Dim c As String Do Until Selection = "" c = Selection Select Case c Case "A" To "az" Selection.FormulaR1C1 = "AL" Case "G" To "gz" Selection.FormulaR1C1 = "GL" Case "P" To "pz" Selection.FormulaR1C1 = "PET" Case "B" To "Bz" Selection.FormulaR1C1 = "BM" Case "1" To "1z" Selection.FormulaR1C1 = "PET" Case "2" To "2z" Selection.FormulaR1C1 = "HDPE" Case else end select Selection.Offset(1, 0).Select Loop 'Until Selection = "" I was wondering if their would be a more efficient way of doing this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
emulating if else Like statements with case
Arnold,
Whilst some would claim otherwise, you could use: Select Case True Case Selection Like "A*" Selection.FormulaR1C1 = "AL" Case Selection Like "G*" Selection.FormulaR1C1 = "GL" ...etc NickHK "Arnold Klapheck" ... I have been given code of: Do Until Selection = "" If Selection Like "A*" Then Selection.FormulaR1C1 = "AL" ElseIf Selection Like "G*" Then Selection.FormulaR1C1 = "GL" ElseIf Selection Like "P*" Then Selection.FormulaR1C1 = "PET" ElseIf Selection Like "B*" Then Selection.FormulaR1C1 = "BM" ElseIf Selection Like "1*" Then Selection.FormulaR1C1 = "PET" ElseIf Selection Like "2*" Then Selection.FormulaR1C1 = "HDPE" Else End If Selection.Offset(1, 0).Select Loop 'Until Selection = "" and was thinking of changing to: Dim c As String Do Until Selection = "" c = Selection Select Case c Case "A" To "az" Selection.FormulaR1C1 = "AL" Case "G" To "gz" Selection.FormulaR1C1 = "GL" Case "P" To "pz" Selection.FormulaR1C1 = "PET" Case "B" To "Bz" Selection.FormulaR1C1 = "BM" Case "1" To "1z" Selection.FormulaR1C1 = "PET" Case "2" To "2z" Selection.FormulaR1C1 = "HDPE" Case else end select Selection.Offset(1, 0).Select Loop 'Until Selection = "" I was wondering if their would be a more efficient way of doing this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
emulating if else Like statements with case
My understanding was that using the "selection" command slows down code, my
ideas was to use it only once and put answer in memory then use that on a select statement, would the processing speed between that and your code below be negligible? I could be going through 10,000 records. Whilst some would claim otherwise, you could use: Select Case True Case Selection Like "A*" Selection.FormulaR1C1 = "AL" Case Selection Like "G*" Selection.FormulaR1C1 = "GL" ...etc |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
emulating if else Like statements with case
Arnold,
It would be better to store in a local variable and use that for comparison, yes. From an earlier thread in this NG, Like is fast if used case insenitive (Option Compare Text), but slows a lot when used with case sensitive (Option Compare Binary) - I think got that the right way round . However, I notice that you only care about the first letter of the cell's value, so Select Case UCase(Left(Selection.Value,1)) Case "A" Selection.Value= "AL" case "G" NickHK "Arnold Klapheck" ... My understanding was that using the "selection" command slows down code, my ideas was to use it only once and put answer in memory then use that on a select statement, would the processing speed between that and your code below be negligible? I could be going through 10,000 records. Whilst some would claim otherwise, you could use: Select Case True Case Selection Like "A*" Selection.FormulaR1C1 = "AL" Case Selection Like "G*" Selection.FormulaR1C1 = "GL" ...etc |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
emulating if else Like statements with case
Thanks for your help, with 931 records the original way took .234 sec, with
the new way you showed me took .078 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Statements to Select Case??? | Excel Discussion (Misc queries) | |||
Case Statements in Excel 2003 | Excel Discussion (Misc queries) | |||
Using Select Case Statements | Excel Programming | |||
Limits on Case Statements | Excel Programming | |||
Case Statements | Excel Programming |