emulating if else Like statements with case

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

 

[NickHK]

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]

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.

 

[NickHK]

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]

Thanks for your help, with 931 records the original way took .234 sec, with
the new way you showed me took .078