if then vs select case

[codedreamer]

I am trying to understand the relationship between if then else statements and select case. So I tried to convert if then to select case, but I do not know how to do it. This is all I could come up with. Pointing to easy to understand article or a simple explanation would be appreciated.

Thank you,


codedreamer.

[pre]

If range (“a1”).value = “apple” then
If range (“b2”).value = 3 then
(“C3”).value =4
(“d3”).value = 5
Endif
Else
If range (“a1”).value = “pear” then
If range (“b2”).value = 4 then
(“C3”).value =8
(“d3”).value = 10
Endif
If range (“a1”).value = “orange” then
If range (“b2”).value = 1 then
(“C3”).value =6
(“d3”).value = 9
Endif

Endif

Select case fruit
Case apple,pear,orange
no idea what is next

[/pre]

 

[Marc L]

Hi !   Don't you see the VBA's Help and its example ?!


  Select Case testexpression

  [Case expressionlist-n

  [statements-n]] ...

  [Case Else

  [elsestatements]]

  End Select


 

In your "case" :

[pre]

    Select Case [A1]
Case "apple"
If [B2] = 3 Then [C3] = 4: [D3] = 5

Case "pear"
If [B2] = 4 Then [C3] = 8: [D3] = 10

Case "orange"
If [B2] = 1 Then [C3] = 6: [D3] = 9
End Select

[/pre]

 

[shrivallabha]

You can use If-Endif and Select-EndSelect interchangeably in most of the cases.

One more construct that would apply to your case:

[pre]

Select Case Range("a1").Value & Range("b2").Value
Case "apple" & 3
Range("C3").Value = 4: Range("d3").Value = 5
Case "pear" & 4
Range("C3").Value = 8: Range("d3").Value = 10
Case "orange" & 1
Range("C3").Value = 6: Range("d3").Value = 9
Case Else
Range("C3").Value = "": Range("d3").Value = ""
End Select

[/pre]

 

[codedreamer]

Thank you very much for your examples. I will study them and hopefully understand the concept.


Codedreamer

 

[Hui]

Codedreamer


If you structure your If..End If correctly you will speed it up and make it more readable like:

[pre]

Dim Fruit As String
Dim Numb As Integer

Fruit = Range("A1").Value
Numb = Range("B2").Value

If Fruit = "apple" And Numb = 3 Then
Range("C3:D3").Value = Array(4,5)
ElseIf Fruit = "pear" And Numb = 4 Then
Range("C3:D3").Value = array(8,10)
ElseIf Fruit = "orange" And Numb = 1 Then
Range("C3:D3").Value = Array(6,9)
Else
'Do something else
End If

[/pre]

Effectively replicating the structure of the Case..End Case structure


Also notice how I look up the value of the Fruit and Numb once at the start, this makes it run a lot faster, than retrieving those values every time

 

[NARAYANK991]

Hi ,


I think you should decide for yourself whether IF ... ELSEIF ... END IF is better than SELECT CASE .... END SELECT.


The following link gives you more information to help you make up your mind :


http://en.wikipedia.org/wiki/Switch_statement


Narayan

 

[codedreamer]

Thank you so much Hui, the way you wrote the if then endif is really nice. Narayank991 the article is very interesting, never thought to go to wikipedia. I think I got it now. Thank you very much to all of you.


Codedreamer