title: How to include multiple columns in a select case statement
body: <p>I have made a select case statement that I use in a combobox. Please refer below for code. However in the example provided I am only able to use one column at a time I have tried a couple of things examples. End result would be if "current month" is selected use columns "C" and "S".</p>
  
  <p>Attempt 1:</p>
  
  <pre><code>nExtend = 1 'Set this as a default.
  Select Case MonthComboBox.value
      **Case "Current Month"
          iCol = "C"
      Case "Current Month"
          iCol = "S"**
      Case "Current Month +1"
          iCol = "N"
          nExtend = 5
      Case "Current Month +2"
          iCol = "O"
          nExtend = 4
      Case "Current Month +3"
          iCol = "P"
          nExtend = 3
      Case "Current Month +4"
          iCol = "Q"
          nExtend = 2
  End Select
  </code></pre>
  
  <p>Attempt 2:</p>
  
  <pre><code>nExtend = 1 'Set this as a default.
  Select Case MonthComboBox.value
      ****Case "Current Month"
          iCol = "C" &amp; "S"**
      Case "Current Month +1"
          iCol = "N"
          nExtend = 5
      Case "Current Month +2"
          iCol = "O"
          nExtend = 4
      Case "Current Month +3"
          iCol = "P"
          nExtend = 3
      Case "Current Month +4"
          iCol = "Q"
          nExtend = 2
  End Select
  </code></pre>
  
  <p>Actual Code:</p>
  
  <pre><code>Private Sub cmdAdd_Click()
  
  
      Dim irow As Long
      Dim lastRow As Long
      Dim iCol As String
      Dim c As Range
      Dim ws As Worksheet
      Dim value As Long
      Dim NewPart As Boolean
      Dim ws_warehouse(7) As Worksheet    '7 is total warehouse tab you have
      Dim nExtend As Integer
      Dim cel As Range
  
      Set ws = Worksheets("Main")
  
      Set ws_warehouse(1) = Worksheets("Elkhart East")
      Set ws_warehouse(2) = Worksheets("Tennessee")
      Set ws_warehouse(3) = Worksheets("Alabama")
      Set ws_warehouse(4) = Worksheets("North Carolina")
      Set ws_warehouse(5) = Worksheets("Pennsylvania")
      Set ws_warehouse(6) = Worksheets("Texas")
      Set ws_warehouse(7) = Worksheets("West Coast")
  
      Set c = ws.Range("A7:A1048576").Find(What:=Me.PartTextBox.value, SearchOrder:=xlRows, _
              SearchDirection:=xlPrevious, LookIn:=xlValues, LookAt:=xlWhole)
      If c Is Nothing Then
      'find first empty row in database
          lastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
          irow = lastRow + 1
          NewPart = True
      Else
      'find row where the part is
          irow = ws.Cells.Find(What:=Me.PartTextBox.value, SearchOrder:=xlRows, _
              SearchDirection:=xlPrevious, LookIn:=xlValues).Row
          NewPart = False
      End If
  
      'check for a part number
      If Trim(Me.PartTextBox.value) = "" Then
        Me.PartTextBox.SetFocus
        MsgBox "Please Enter A Part Number"
        Exit Sub
      End If
  
      If Trim(Me.MonthComboBox.value) = "" Then
        Me.MonthComboBox.SetFocus
        MsgBox "Please Enter A Month"
        Exit Sub
      End If
  
      If Trim(Me.AddTextBox.value) = "" Then
        Me.AddTextBox.SetFocus
        MsgBox "Please Enter A Value To Add Or Substract"
        Exit Sub
      End If
  
      nExtend = 1 'Set this as a default.
      Select Case MonthComboBox.value
          Case "Current Month"
              iCol = "C"
  
          Case "Current Month +1"
              iCol = "N"
              nExtend = 4
          Case "Current Month +2"
              iCol = "O"
              nExtend = 3
          Case "Current Month +3"
              iCol = "P"
              nExtend = 2
          Case "Current Month +4"
              iCol = "Q"
      End Select
  
      actWarehouse = Me.warehousecombobox.ListIndex + 1
  
      With ws
          .Cells(irow, "A").value = Me.PartTextBox.value
  
  
          For Each cel In .Cells(irow, iCol).Resize(, nExtend)
              cel.value = cel.value + CLng(Me.AddTextBox.value)
              cel.Interior.ColorIndex = 6
          Next cel
      End With
  
      With ws_warehouse(actWarehouse)
          'find part number
          l_row = .Range("A" &amp; .Rows.Count).End(xlUp).Row
  
          NewPart = True
          For r = 7 To l_row
              If Trim(.Range("A" &amp; r)) = "" Then Exit For
              If LCase(.Range("A" &amp; r)) = LCase(Me.PartTextBox.Text) Then
                  irow = r
                  Exit For
                  NewPart = False
              End If
          Next r
          If NewPart Then irow = r
  
          .Cells(irow, "A").value = Me.PartTextBox.value
          For Each cel In .Cells(irow, iCol).Resize(, nExtend)
              cel.value = cel.value + CLng(Me.AddTextBox.value)
              cel.Interior.ColorIndex = 6
          Next cel
      End With
  
      'clear the data
      Me.PartTextBox.value = ""
      Me.MonthComboBox.value = ""
      Me.AddTextBox.value = ""
      Me.PartTextBox.SetFocus
      Me.warehousecombobox.value = ""
  
  End Sub
  
  Private Sub cmdClose_Click()
      Unload Me
  End Sub
  </code></pre>