title: Nested Select Case for multiple conditions
body: <p><strong>Situation:</strong> 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).</p>
  
  <p><strong>Ex:</strong> If my A5 is "Bond", it concatenates the contents of A5 and B5 and pastes it to J5.</p>
  
  <p><strong>Obs1:</strong> There are dozens of sub conditions, for the first, second, third and fourth column of data. </p>
  
  <p><strong>What I tried so far:</strong> 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.</p>
  
  <p><strong>Problem:</strong> 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.</p>
  
  <p><strong>Question:</strong> What is the best way to work with nested Select Case when there are multiple conditions?</p>
  
  <p><strong>Obs2:</strong> 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. </p>
  
  <p><strong>Code1:</strong> This is what I got so far using the Select Case:</p>
  
  <pre><code>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) &amp; " " &amp; w.Worksheets("Fixer").Cells(lRow, 2) &amp; " " &amp; w.Worksheets("Fixer").Cells(lRow, 3) &amp; " " &amp; w.Worksheets("Fixer").Cells(lRow, 4) &amp; " " &amp; w.Worksheets("Fixer").Cells(lRow, 5)
  Case Is = "Collateral"
      result = w.Worksheets("Fixer").Cells(lRow, 1) &amp; " " &amp; w.Worksheets("Fixer").Cells(lRow, 2) &amp; " " &amp; 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) &amp; " " &amp; w.Worksheets("Fixer").Cells(lRow, 2) &amp; " " &amp; 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) &amp; " " &amp; w.Worksheets("Fixer").Cells(lRow, 2) &amp; " " &amp; 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) &amp; " " &amp; w.Worksheets("Fixer").Cells(lRow, 2) &amp; " " &amp; w.Worksheets("Fixer").Cells(lRow, 3)
  Case Else
      result = w.Worksheets("Fixer").Cells(lRow, 1) &amp; " " &amp; 
  w.Worksheets("Fixer").Cells(lRow, 2)
  End Select
  
  w.Worksheets("Fixer").Cells(lRow, 10).Value = result
  
  Next lRow
  
  End Function
  </code></pre>
  
  <p><strong>Code 2:</strong> And this is a little part of the code where I used the nested Ifs:</p>
  
  <pre><code>ElseIf w.Worksheets("Fixer").Cells(lRow, 1) = "General" Then
      w.Worksheets("Fixer").Cells(lRow, 10) = 
  w.Worksheets("Fixer").Cells(lRow, 1) &amp; " " &amp; 
  w.Worksheets("Fixer").Cells(lRow, 2) &amp; " " &amp; 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
  </code></pre>
  
  <p><strong>Obs3:</strong> To better explain how my data is organized, here is a small part of it.
  <a href="https://i.stack.imgur.com/VD4ko.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/VD4ko.jpg" alt="Data Example"></a></p>