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?

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?

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

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

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