CODEWITHSUNDEEP
vbaexcel
Luis
Luis

Reputation: 139

How to include multiple columns in a select case statement

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".

Attempt 1:

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

Attempt 2:

nExtend = 1 'Set this as a default.
Select Case MonthComboBox.value
    ****Case "Current Month"
        iCol = "C" & "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

Actual 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" & .Rows.Count).End(xlUp).Row

        NewPart = True
        For r = 7 To l_row
            If Trim(.Range("A" & r)) = "" Then Exit For
            If LCase(.Range("A" & 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

Upvotes: 0

Views: 722

Answers (1)

YowE3K
YowE3K

Reputation: 23974

I would suggest that you change your Select Case statement as follows:

Select Case MonthComboBox.value
    Case "Current Month"
        iCol = "C:C,S:S"
    Case "Current Month +1"
        iCol = "N:R"
    Case "Current Month +2"
        iCol = "O:R"
    Case "Current Month +3"
        iCol = "P:R"
    Case "Current Month +4"
        iCol = "Q:R"
End Select

(You won't need nExtend - it's built into the column specifications you set up in iCol.)

And then your For Each cel loops can both be changed to be

For Each cel In Intersect(.Rows(irow), .Range(iCol))

Your full revised code would be as follows:

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 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

    Select Case MonthComboBox.value
        Case "Current Month"
            iCol = "C:C,S:S"
        Case "Current Month +1"
            iCol = "N:R"
        Case "Current Month +2"
            iCol = "O:R"
        Case "Current Month +3"
            iCol = "P:R"
        Case "Current Month +4"
            iCol = "Q:R"
    End Select

    actWarehouse = Me.warehousecombobox.ListIndex + 1

    With ws
        .Cells(irow, "A").value = Me.PartTextBox.value


        For Each cel In Intersect(.Rows(irow), .Range(iCol))
            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" & .Rows.Count).End(xlUp).Row

        NewPart = True
        For r = 7 To l_row
            If Trim(.Range("A" & r)) = "" Then Exit For
            If LCase(.Range("A" & 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 Intersect(.Rows(irow), .Range(iCol))
            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

Upvotes: 1

Related Questions