Nested Select Case for multiple conditions

Question

Situation: I have a code that goes through some data in a worksheet and given what is in a certain cell it pastes something on another column (same row).

Ex: If my A5 is "Bond", it concatenates the contents of A5 and B5 and pastes it to J5.

Obs1: There are dozens of sub conditions, for the first, second, third and fourth column of data.

What I tried so far: I was able to create a very long Nested If chain and account for all conditions. I also was able to use Select case to account for the first column conditions.

Problem: Now I am trying to use nested Select Case to account for this conditions (considering that the If chain is gargantuan, and too long to be efficient). Problem is I am not being able to properly account for the nested Select Cases for multiple conditions.

Question: What is the best way to work with nested Select Case when there are multiple conditions?

Obs2: From previous research I found posts here about nested if, especially when there is a true or false value. This does not work for me, because each layer has many more conditions.

Code1: This is what I got so far using the Select Case:

Function fxr2()

Dim lRow As Long, LastRow As Long
Dim w As Workbook
Dim ws As Worksheet

Set w = ThisWorkbook

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

LastRow = Worksheets("Fixer").Cells(Rows.Count, "A").End(xlUp).Row

For lRow = 7 To LastRow

Dim type1 As String, result As String
type1 = w.Worksheets("Fixer").Cells(lRow, 1).Text

Select Case type1
Case Is = "Bail-in"
    result = w.Worksheets("Fixer").Cells(lRow, 1)
Case Is = "Basel"
    result = w.Worksheets("Fixer").Cells(lRow, 1) & " " & w.Worksheets("Fixer").Cells(lRow, 2) & " " & w.Worksheets("Fixer").Cells(lRow, 3) & " " & w.Worksheets("Fixer").Cells(lRow, 4) & " " & w.Worksheets("Fixer").Cells(lRow, 5)
Case Is = "Collateral"
    result = w.Worksheets("Fixer").Cells(lRow, 1) & " " & w.Worksheets("Fixer").Cells(lRow, 2) & " " & w.Worksheets("Fixer").Cells(lRow, 3)
Case Is = "Design"
    result = w.Worksheets("Fixer").Cells(lRow, 1)
Case Is = "General"
    result = w.Worksheets("Fixer").Cells(lRow, 1) & " " & w.Worksheets("Fixer").Cells(lRow, 2) & " " & w.Worksheets("Fixer").Cells(lRow, 3)
Case Is = "Investment"
    result = w.Worksheets("Fixer").Cells(lRow, 1)
Case Is = "Lower"
    result = w.Worksheets("Fixer").Cells(lRow, 1) & " " & w.Worksheets("Fixer").Cells(lRow, 2) & " " & w.Worksheets("Fixer").Cells(lRow, 3)
Case Is = "Recapitalization"
    result = w.Worksheets("Fixer").Cells(lRow, 1)
Case Is = "Refinance"
    result = w.Worksheets("Fixer").Cells(lRow, 1)
Case Is = "Upper"
    result = w.Worksheets("Fixer").Cells(lRow, 1) & " " & w.Worksheets("Fixer").Cells(lRow, 2) & " " & w.Worksheets("Fixer").Cells(lRow, 3)
Case Else
    result = w.Worksheets("Fixer").Cells(lRow, 1) & " " & 
w.Worksheets("Fixer").Cells(lRow, 2)
End Select

w.Worksheets("Fixer").Cells(lRow, 10).Value = result

Next lRow

End Function

Code 2: And this is a little part of the code where I used the nested Ifs:

ElseIf w.Worksheets("Fixer").Cells(lRow, 1) = "General" Then
    w.Worksheets("Fixer").Cells(lRow, 10) = 
w.Worksheets("Fixer").Cells(lRow, 1) & " " & 
w.Worksheets("Fixer").Cells(lRow, 2) & " " & w.Worksheets("Fixer").Cells(lRow, 3)

    If w.Worksheets("Fixer").Cells(lRow, 4) = "Base" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Inte" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Tier" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "v" Or w.Worksheets("Fixer").Cells(lRow, 4) = "Ba" Or w.Worksheets("Fixer").Cells(lRow, 4) = "Bas" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Int" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Inte" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Inter" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Tie" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Tier-" Then
        w.Worksheets("Fixer").Cells(lRow, 11) = ""

    ElseIf w.Worksheets("Fixer").Cells(lRow, 4) = "" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Upp" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Uppe" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Upper" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "I" Or w.Worksheets("Fixer").Cells(lRow, 4) = "L" Or w.Worksheets("Fixer").Cells(lRow, 4) = "T" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "U" Then
        w.Worksheets("Fixer").Cells(lRow, 11) = ""

    ElseIf w.Worksheets("Fixer").Cells(lRow, 4) = "Design" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Inve" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Inv" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Low" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Lowe" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Proj" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Pro" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Ref" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Refi" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Stock" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Inve" Then
        w.Worksheets("Fixer").Cells(lRow, 11) = 
w.Worksheets("Fixer").Cells(lRow, 4)

    ElseIf w.Worksheets("Fixer").Cells(lRow, 4) = "LBO" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Working" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Work" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Wor" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Gre" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Gree" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Green" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Interc" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Intercom" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Intercompany" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Intermed" Then
        w.Worksheets("Fixer").Cells(lRow, 11) = 
w.Worksheets("Fixer").Cells(lRow, 4)

    ElseIf w.Worksheets("Fixer").Cells(lRow, 4) = "Low" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Lower" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "Lowe" Or 
w.Worksheets("Fixer").Cells(lRow, 4) = "No" Or w.Worksheets("Fixer").Cells(lRow, 4) = "Pen" Or w.Worksheets("Fixer").Cells(lRow, 4) = "Pens" Or w.Worksheets("Fixer").Cells(lRow, 4) = "Pension" Or w.Worksheets("Fixer").Cells(lRow, 4) = "Projec" Or w.Worksheets("Fixer").Cells(lRow, 4) = "Project" Or w.Worksheets("Fixer").Cells(lRow, 4) = "Refin" Or w.Worksheets("Fixer").Cells(lRow, 4) = "Refina" Then
        w.Worksheets("Fixer").Cells(lRow, 11) = w.Worksheets("Fixer").Cells(lRow, 4)

    ElseIf w.Worksheets("Fixer").Cells(lRow, 4) = "Refinanc" Or w.Worksheets("Fixer").Cells(lRow, 4) = "Refinance" Or w.Worksheets("Fixer").Cells(lRow, 4) = "Stoc" Or w.Worksheets("Fixer").Cells(lRow, 4) = "Sto" Or w.Worksheets("Fixer").Cells(lRow, 4) = "w" Or w.Worksheets("Fixer").Cells(lRow, 4) = "Wor" Or w.Worksheets("Fixer").Cells(lRow, 4) = "W" Or w.Worksheets("Fixer").Cells(lRow, 4) = "Tier-1" Or w.Worksheets("Fixer").Cells(lRow, 4) = "Tier-2" Then
        w.Worksheets("Fixer").Cells(lRow, 11) = w.Worksheets("Fixer").Cells(lRow, 4)

    End If

Obs3: To better explain how my data is organized, here is a small part of it.

Many of your cases do the same thing. You can combine them as follows: `Case "Collateral", "General", "Lower"` — jsheeran, Apr 13 '17 at 09:33
I can't tell what exactly is your problem. Is it not working or are you just looking for a more efficient way to do things? — SJR, Apr 13 '17 at 09:46
@jsheeran Indeed you are right, my problem is that each of these can summon different conditions for the subsequent columns. For example, "General" will concatenate A5, B5 and C5, and will influence the content in D4, whereas "Collateral" concatenates A5 and B5 and does not influence other columns. — DGMS89, Apr 13 '17 at 09:47
@SJR I was able to do it with Ifs, not I am trying to do the same thing with Select Case. But I am not being able to do the sub-conditions. — DGMS89, Apr 13 '17 at 09:47
Not sure it's worth trying to completely eliminate If. You could make your code more readable by using `With` clauses or assigning your sheet to a variable. — SJR, Apr 13 '17 at 09:50
@SJR Indeed, that is a great idea, would make the code more manageable. But for size purposes, is it not better to work with Select Case in a situation like this? (to avoid the If chain) — DGMS89, Apr 13 '17 at 09:55
I think the main advantage of `Select Case` is that's easier on the eye. I doubt there is any speed advantage to speak of (though others here will know better). Certainly not worth tying yourself in knots in order to avoid If! — SJR, Apr 13 '17 at 09:58
Can't you use a wildcard in your nested ifs? For example In* for Int, Inte, Inter and Ti* for Tie, Tier, Tier- — Luuklag, Apr 13 '17 at 10:05
@Luuklag In some cases yes, that is possible. But in some other cases it does not work, for example: Tier could either be Tier-1 or Tier-2. Or for In, it could be international, or interbank, so it depends. — DGMS89, Apr 13 '17 at 10:14

Shai Rado · Accepted Answer · 2017-04-13T10:30:13.170

Part 1 (Code 1) of your post could look like the shorter and simplified version below (explanation inside the code comments):

Function fxr2()

Dim lRow As Long, LastRow As Long
Dim w As Workbook
Dim ws As Worksheet

Set w = ThisWorkbook
Set ws = w.Worksheets("Fixer") '<-- set the worksheet

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Dim type1 As String, result As String '<-- There's no need to Dim them every time inside the loop

' use With statement, will simplify and shorten your code later
With ws
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row '<-- fully qualify Rows.Count with "Fixer" sheet

    For lRow = 7 To LastRow
        type1 = .Cells(lRow, 1).Text

        Select Case type1
            Case "Bail-in", "Investment", "Recapitalization", "Refinance", "Design"
                result = .Cells(lRow, 1)

            Case "Basel"
                result = .Cells(lRow, 1) & " " & .Cells(lRow, 2) & " " & .Cells(lRow, 3) & " " & .Cells(lRow, 4) & " " & .Cells(lRow, 5)

            Case "Collateral", "General", "Lower", "Upper"
                result = .Cells(lRow, 1) & " " & .Cells(lRow, 2) & " " & .Cells(lRow, 3)

            Case Else
                result = .Cells(lRow, 1) & " " & .Cells(lRow, 2)

        End Select

        .Cells(lRow, 10).Value = result
    Next lRow
End With

End Function

All you have below in Code 2 are 2 Case conditions, constructed of multiple Strings you are trying to comapre with:

Select Case .Cells(lRow, 4)
    Case "Base", "Inte", "Tier", "v", "Ba", "Bas", "Int", "Inte", "Inter", "Tie", "Tier-", "", "Upp", "Uppe", "Upper", "I", "L", "T"
        .Cells(lRow, 11) = ""

    Case "Design", "Inve", "Inv", "Low", "Lowe", "Proj", "Pro", "Ref", "Refi", "Refin", "Refina", "Refinanc", "Refinance", "Stock", "Inve", "LBO", "Working", "Work", "Wor", "Gre", _
             "Gree", "Green", "Interc", "Intercom", "Intercompany", "Intermed", "Refinanc", "Stoc", "No", "Pen", "Pens", "Pension", "Projec", "Project", _
             "Sto", "Stoc", "w", "Wor", "Tier-1", "Tier-2"
        .Cells(lRow, 11) = .Cells(lRow, 4)

End Select

Not sure this is exactly where you want to put it, but it's just an example of how to use Select Case nested in another Select Case.

Edited "Merged" Code

Function fxr2()

Dim lRow As Long, LastRow As Long
Dim w As Workbook
Dim ws As Worksheet

Set w = ThisWorkbook
Set ws = w.Worksheets("Fixer") '<-- set the worksheet

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Dim type1 As String, result As String '<-- There's no need to Dim them every time inside the loop

' use With statement, will simplify and shorten your code later
With ws
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row '<-- fully qualify Rows.Count with "Fixer" sheet

    For lRow = 7 To LastRow
        type1 = .Cells(lRow, 1).Text

        Select Case type1
            Case "Bail-in", "Investment", "Recapitalization", "Refinance", "Design"
                .Cells(lRow, 10).Value = .Cells(lRow, 1)

            Case "Basel"
                .Cells(lRow, 10).Value = .Cells(lRow, 1) & " " & .Cells(lRow, 2) & " " & .Cells(lRow, 3) & " " & .Cells(lRow, 4) & " " & .Cells(lRow, 5)

            Case "Collateral", "General", "Lower", "Upper"
                .Cells(lRow, 10).Value = .Cells(lRow, 1) & " " & .Cells(lRow, 2) & " " & .Cells(lRow, 3)

                ' ===== Added the Nested case here (just for example) =====
                 Select Case .Cells(lRow, 4)
                    Case "Base", "Inte", "Tier", "v", "Ba", "Bas", "Int", "Inte", "Inter", "Tie", "Tier-", "", "Upp", "Uppe", "Upper", "I", "L", "T"
                        .Cells(lRow, 11) = ""

                    Case "Design", "Inve", "Inv", "Low", "Lowe", "Proj", "Pro", "Ref", "Refi", "Refin", "Refina", "Refinanc", "Refinance", "Stock", "Inve", "LBO", "Working", "Work", "Wor", "Gre", _
                             "Gree", "Green", "Interc", "Intercom", "Intercompany", "Intermed", "Refinanc", "Stoc", "No", "Pen", "Pens", "Pension", "Projec", "Project", _
                             "Sto", "Stoc", "w", "Wor", "Tier-1", "Tier-2"
                        .Cells(lRow, 11) = .Cells(lRow, 4)

                End Select
                ' ==== End of Nested Select Case ====

            Case Else
                .Cells(lRow, 10).Value = .Cells(lRow, 1) & " " & .Cells(lRow, 2)

        End Select
    Next lRow
End With

End Function

Thanks for the answer. Indeed, using the with and the other changes you suggested certainly make the code much more manageable. But how can I use sub conditions within each Select Case (original question)? — DGMS89, Apr 13 '17 at 09:57
@DGMS89 where should the `w.Worksheets("Fixer").Cells(lRow, 4)` be ? inside the `Select Case type1` , like combined ? or after ? — Shai Rado, Apr 13 '17 at 09:58
@DGMS89 see my edited answer , so the question now is where you want to place these conditionds ? inside the first `Select Case` ? or these are different criterias ? — Shai Rado, Apr 13 '17 at 10:11
Yes. For example, as I have in code2: Select the case1 where I have "General" in A5, then concatenate A5, B5 and C5; after that, still inside this case, Select Case1.1 where D5 is "Invest" (then do something), or Select Case1.2 where D5 is "Tier" (then do something else), For each initial case (let us say that the initial case is the condition for column 1), there are "subcases". Sorry if cannot explain it better. — DGMS89, Apr 13 '17 at 10:19

score 1 · Answer 2 · answered Apr 13 '17 at 10:03

Case can be nested in the same way IFs can:

Select Case a
    Case 10
        Select Case b
            Case 1
                'a is 10, b is 1
            Case 2
                'a is 10, b is 2
            Case 3
                'a is 10, b is 3
        End Select
    Case 20
        Select Case b
            Case 1
                'a is 20, b is 1
            Case 2
                'a is 20, b is 2
            Case 3
                'a is 20, b is 3
        End Select
End Select

score 1 · Answer 3 · answered Apr 13 '17 at 10:20

This is probably not what you have expected as an answer, but the logic works flawlessly, if you apply it to your own case.

Let's assume, that VBA was regularly updated by Microsoft. At least as often as C#. Then we would have had something, called [FLAGS] in it, and this problem would have been really easy. However, we don't have it, thus we should build something like this alone.

Imagine, that you had 7 products (AAA, BBB, CCC, DDD, EEE, FFF, GGG) and you wanted to know which one you have selected. I assume that this is the core of your problem. This is quite easy, if you use binary mathematics - then the first product is given value of 1, the second is 2, the third is 4, the forth is 8 and etc.

27 means that you have selected 1+2+8+16. (AAA, BBB, DDD, EEE)
28 means that you have selected 4+8+16. (CCC, DDD, EEE)

Thus, if we imagine that you have the number and you want the products, then something like this may work. The Number is lngNumber and the LngToBinary gives you the binary value of the number. In the Sub TestMe, instead of printing the products, you may actually do some action with them.

Option Explicit
Option Private Module

Public Sub TestMe()

    Dim arrProducts     As Variant
    Dim lngCounter      As Long
    Dim lngValue        As Long
    Dim strBinary       As String
    Dim lngNumber       As Long

    arrProducts = Array("AAA", "BBB", "CCC", "DDD", "EEE", "FFF", "GGG")
                           '1,     2,     4,     8,    16,    32,    64
    lngNumber = 28 '1+2+8+16
    strBinary = StrReverse(LngToBinary(lngNumber))

    For lngCounter = 1 To Len(strBinary)
        lngValue = Mid(strBinary, lngCounter, 1)

        If lngValue Then
            Debug.Print arrProducts(lngCounter - 1)
        End If

    Next lngCounter

End Sub

Function LngToBinary(ByVal n As Long) As String

    Dim k As Long

    LngToBinary = vbNullString

    If n < -2 ^ 15 Then
        LngToBinary = "0"
        n = n + 2 ^ 16
        k = 2 ^ 14

    ElseIf n < 0 Then

        LngToBinary = "1"
        n = n + 2 ^ 15
        k = 2 ^ 14

    Else

        k = 2 ^ 15

    End If

    Do While k >= 1
        LngToBinary = LngToBinary & Fix(n / k)
        n = n - k * Fix(n / k)
        k = k / 2
    Loop

End Function

More about [FLAGS] here: https://msdn.microsoft.com/en-us/library/system.flagsattribute(v=vs.110).aspx

Nested Select Case for multiple conditions

3 Answers3