Excel 2010 "CASE" vs "If... Then" 1

[PWD]

I had a quick look to see if there was any mileage in differentiating between:-

For i = 1 To LastRow

        If Left(Range("B" & i), 4) = "Food" Then
            Rows(i & ":" & i).Interior.Color = RGB(180, 201, 95)
            
        ElseIf Range("B" & i) = "Funeralcare" Then
            Rows(i & ":" & i).Interior.Color = RGB(128, 136, 188)
            
        ElseIf Range("B" & i) = "Specialist Retail Business" Then
            Rows(i & ":" & i).Interior.Color = RGB(232, 184, 104)
            
        ElseIf Range("B" & i) = "Pharmacy" Then
            Rows(i & ":" & i).Interior.Color = RGB(186, 217, 210)
        End If

Next i

and,

For i = 1 To LastRow

Select Case Range("B" & i)

Case "Food", "Food "
Rows(i & ":" & i).Interior.Color = RGB(180, 201, 95)

Case "Funeralcare"
Rows(i & ":" & i).Interior.Color = RGB(128, 136, 188)

Case "Specialist Retail Business"
Rows(i & ":" & i).Interior.Color = RGB(232, 184, 104)

Case "Pharmacy"
Rows(i & ":" & i).Interior.Color = RGB(186, 217, 210)

End Select

Next i

For about 6,500 lines. There doesn't appear to be anything "out there" to help me form an opinion. Does anyone have any thoughts?



Many thanks,
D€$

 

[judgeh59]

I haven't ever ran speed tests between the two but indent the Select..Case the same as the If...then, for me, it's easier to read the code...also, on side note, if that is just a space after the "Food " then you might consider a TRIM statement...just my 2cents...

Ernest

Be Alert, America needs more lerts

 

[PWD]

Hi. I'm only just getting into this & I'm not sure what the data will be; it could be "Food & Drink" or whatever. I'm just trying to get ahead of the game & make the code as efficient as possible prior to actually 'going live' with it.

"Git-R-Done!!"

Many thanks,
D€$

 

[Andrzejek]

AFAIK, the two are the same speed and are treated the same way by the compiler, they are just a lot easier to read and maintain.

You may also cosider:

Dim R As integer
Dim G As Integer
Dim B as Integer

For i = 1 To LastRow
  Select Case Trim$(Range("B" & i))
    Case "Food"
      R = 180
      G = 201
      B = 95
    Case "Funeralcare"
      R = 128
      G = 136
      B = 188
    Case "Specialist Retail Business"
      R = 232
      G = 184
      B = 104
    Case "Pharmacy"
      R = 186
      G = 217
      B = 210
  End Select
  Rows(i & ":" & i).Interior.Color = RGB(R, G, B)
Next i

Have fun.

---- Andy

 

[SkipVought]

if you had a field value containing "Food" & "Drink" then

Select Case YourField
  Case "Food", "Drink"
    'eat it or drink it
end select

would be equivalent to...

If YourField = "Food" Or YourField = "Drink" Then
    'eat it or drink it
end if

I like the Select Case strucutre!



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

Another example of Select statement:

[blue]
Select Case[/blue] intSomeValue
    [blue]Case[/blue] 1, 4, 7, 12 [blue]To[/blue] 23, 25, 30 [blue]To[/blue] 48
    
    [blue]Case[/blue] 3, 6, 9 To 11, 24, 49 [blue]To[/blue] 55
    
    [blue]Case[/blue] 2, 5, 8, 26 [blue]To[/blue] 29
    [blue]
    Case Else

End Select[/blue]

If you try to re-write it as [tt]If - Then - ElseIf - ElseIf - Else - End If[/tt] statement you will see why people like Select statement

Have fun.

---- Andy

 

[DaveInIowa]

I'm not totally sure but I have a sneaking suspicion that from a performance perspective, the "Left(Range..." would be evaluated for each If/ElseIf if the preceding Boolean was false.

I'd also suggest that for either method, you arrange the order of the possible values such that the most likely ones are at the top of the list and the least likely towards the bottom.

 

[SkipVought]

here's something else you might consider...

    Dim nColor As Variant
    
    For i = 1 To LastRow
    
        Select Case Trim(Range("B" & i))
        
            Case "Food"
                nColor = RGB(180, 201, 95)
            
            Case "Funeralcare"
                nColor = RGB(128, 136, 188)
            
            Case "Specialist Retail Business"
                nColor = RGB(232, 184, 104)
            
            Case "Pharmacy"
                nColor = RGB(186, 217, 210)
        
        End Select
        
        Rows(i & ":" & i).Interior.Color = nColor

    Next i

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Good morning all. Thanks for the input. I agree that the Select/Case is more flexible & easier to read. "Food" is the #1, so it's now at the top of the list. The others are quite negligable. I'm going with Skip's suggestion as I think it looks rather neat.

Many thanks,
D€$

 

[DaveInIowa]

Don't forget a "Case Else" at the end to set nColor to a default value then.