# Vba - if vs select case

#### Minkowski

##### Board Regular
Hello everyone i want to simplify my code if possible here

Code:
``````Case "1"
If Range("A1") = "bbb" And Range("B1") = "aaa" And
Range("I1") = "ccc" Or Range("I1") = "ddd" Or Range("I1") = "eee" Then
Range("N1").Value = 25
ElseIf Range("A1") = "aaa" And Range("B1") = "bbb" And
Range("I1") = "ccc" Or Range("I1") = "ddd" Or Range("I1") = "eee" Then
Range("N" & I).Value = 35
ElseIf Range("A1") = "bbb" And Range("B1") = "aaa" Then
Range("N1").Value = 30``````
can i make it shorter? something like:
Code:
``````Case "1"
Or "bbbaaaeee":Range("N1)=25
ElseIf Range("A1"&"B1"&"I1")= "aaabbbccc" Or "aaabbbddd"
Or "aaabbbeee":Range("N1)=35
ElseIf Range("A1"&"B1")= "bbbaaa": Range("N1")=30``````
i know i can do it with select case
Code:
``````Case "1"
Select Case Range("A1") & Range("B1")
Case "bbbaaa": Range("N1") = 30
End Select
Select Case Range("A1") & Range("B1") & Range("I1")
Case "aaabbbccc","aaabbbddd","aaabbbeee": Range("N1") = 35
End Select
.....``````
but i don't like going into 2 select cases, i was hoping for even more compact code,thanks in advance

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### Ruddles

##### Well-known Member
can i make it shorter? something like:
Rich (BB code):
``````Case "1"
Or "bbbaaaeee":Range("N1)=25
ElseIf Range("A1"&"B1"&"I1")= "aaabbbccc" Or "aaabbbddd"
Or "aaabbbeee":Range("N1)=35
ElseIf Range("A1"&"B1")= "bbbaaa": Range("N1")=30``````

No, you can't do that: each condition - the bits in red - must be complete. "bbbaaaddd" is not a condition. You have to repeat the whole thing - both of the bits you're comparing.

i know i can do it with select case...
but i don't like going into 2 select cases

If you indent your code correctly it becomes much more easy to read and understand.

If you want to compact your code, you can remove some of it to separate functions or procedures. For example, your main Select Case - does it contain a lot of Case clauses? If so, the code in each of those could be made separate procedures or the values you're assigning to the various ranges could be returned by a function.

But don't get too hung up on producing the smallest possible code. Quite often it's more important for it to be easily understood, especially when you come back to it several weeks later and can't remember why you coded something the way you did.

#### Ruddles

##### Well-known Member
For example, for Case "1" you'd code:-
Code:
``````[FONT=Fixedsys]Case "1"[/FONT]
[FONT=Fixedsys]   Range("N1").Value = N1_Value(Range("A1").Value, Range("B1").Value, Range("I1").Value)[/FONT]``````

Then you'd have a function like this:-
Code:
``````[FONT=Fixedsys]Function N1_Value(R1 As String, R2 As String, R3 As String) As Integer[/FONT]

[FONT=Fixedsys] If R1 = "bbb" And R2 = "aaa" And R3 = "ccc" Or R3 = "ddd" Or R3 = "eee" Then[/FONT]
[FONT=Fixedsys]   N1_Value = 25[/FONT]
[FONT=Fixedsys] ElseIf R1 = "aaa" And R2 = "bbb" And R3 = "ccc" Or R3 = "ddd" Or 3 = "eee" Then[/FONT]
[FONT=Fixedsys]   N1_Value = 35[/FONT]
[FONT=Fixedsys] ElseIf R1 = "bbb" And R2 = "aaa" Then[/FONT]
[FONT=Fixedsys]   N1_Value = 30[/FONT]
[FONT=Fixedsys] End If[/FONT]

[FONT=Fixedsys]End Function[/FONT]``````

You could stick any functions away at the end of the module or even in a separate module. That way you wouldn't need to see them every time you looked at your main code - you'd just regard them as 'black boxes' into which you inject some data and which then return the required values.

#### Minkowski

##### Board Regular
that is very interesting and helpful
thank you very much!!! Replies
1
Views
77
Replies
0
Views
68
Replies
7
Views
290
Replies
1
Views
171
Replies
7
Views
824

### Forum statistics

1,141,220
Messages
5,705,091
Members
421,378
Latest member
CarlosDuran ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back