CODEWITHSUNDEEP
vbaexcel
Luis
Luis

Reputation: 139

Add userform Value to Multiple Columns

Hey guys this one is a complicated one. I have created a userform that will add a value from a user to a certain column (THIS IS CURRENT CODE). However instead of just adding it to that column I want it to add it to the subsequent columns. For example if I were to select current +1 and value=500 I want that value to add it to column N and subsequent "O", "P" "Q". I thought it would be easy as adding it to the case but it's not that easy

        Case "Current Month +1"
            iCol = "N" and "O" and "P" and "Q"

        Case "Current Month +2"
            iCol = "O" and "P" and "Q"

****It's not this easy

Private Sub cmdAdd_Click()
    If TrialVersion Then Exit Sub

    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

    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"

        Case "Current Month +1"
            iCol = "N"

        Case "Current Month +2"
            iCol = "O"

        Case "Current Month +3"
            iCol = "P"

        Case "Current Month +4"
            iCol = "Q"
    End Select

    actWarehouse = Me.warehousecombobox.ListIndex + 1

    With ws
        .Cells(irow, "A").value = Me.PartTextBox.value
        .Cells(irow, iCol).value = .Cells(irow, iCol).value + CLng(Me.AddTextBox.value)
    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
        .Cells(irow, iCol).value = .Cells(irow, iCol).value + CLng(Me.AddTextBox.value)
    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

Private Sub UserForm_Initialize()

    'Empty NameTextBox
     PartTextBox.value = ""

    'Empty PhoneTextBox
     AddTextBox.value = ""

    'Empty DinnerComboBox

    'Fill DinnerComboBox
    With MonthComboBox
         .AddItem "Current Month"
         .AddItem "Current Month +1"
         .AddItem "Current Month +2"
         .AddItem "Current Month +3"
         .AddItem "Current Month +4"

    End With

    With warehousecombobox
        .AddItem "Elkhart East"
        .AddItem "Tennessee"
        .AddItem "Alabama"
        .AddItem "North Carolina"
        .AddItem "Pennsylvania"
        .AddItem "Texas"
        .AddItem "West Coast"
    End With

End Sub

Upvotes: 0

Views: 371

Answers (1)

basodre
basodre

Reputation: 5770

EDIT TO INCLUDE ALL SOURCE CODE:

Private Sub cmdAdd_Click()
    If TrialVersion Then Exit Sub

    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)
        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
        .Cells(irow, iCol).value = .Cells(irow, iCol).value + CLng(Me.AddTextbox.value)
    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

Private Sub UserForm_Initialize()

    'Empty NameTextBox
     PartTextBox.value = ""

    'Empty PhoneTextBox
     AddTextbox.value = ""

    'Empty DinnerComboBox

    'Fill DinnerComboBox
    With MonthComboBox
         .AddItem "Current Month"
         .AddItem "Current Month +1"
         .AddItem "Current Month +2"
         .AddItem "Current Month +3"
         .AddItem "Current Month +4"

    End With

    With warehousecombobox
        .AddItem "Elkhart East"
        .AddItem "Tennessee"
        .AddItem "Alabama"
        .AddItem "North Carolina"
        .AddItem "Pennsylvania"
        .AddItem "Texas"
        .AddItem "West Coast"
    End With

End Sub

If the columns are always consecutive, maybe you can add a second integer variable that tells the code how many columns to include. As an example, in your post, you show that Current Month + 1 starts at Column N and extends 3 columns to the right (O, P, Q). Your code could look something like:

Select Case MonthComboBox.value
    Case "Current Month + 1"
        iCol = "C"
        nExtend = 4

Then when you handle the values, the code would look something like:

.Cells(irow, iCol).Resize(,nExtend).value = .Cells(irow, iCol).value + CLng(Me.AddTextBox.value)

Note: Even though you are technically extending 3 columns, the input to the Resize function is 4. Also note the leading comma because we are not changing the row resize.

EDIT BASED ON NEW INFO:

Use a loop to change each value in the range, such as below:

Select Case MonthComboBox.value
    Case "Current Month + 1"
        iCol = "C"
        nExtend = 4 

For Each cel in .Cells(irow, iCol).Resize(,nExtend)
    cel.Value = cel.Value + CLng(Me.AddTextBox.Value)
Next cel

Upvotes: 1

Related Questions