UserForm - VBA -cmd function - last cell

  • Created a UserForm, trying to have all data displayed on an excel sheet below the appropriate headers. Data will populate in Row 1 & 2, however, if I submit a third time the data replace previous data in cell 2. Thank you for any guidance!

    Private Sub cmdAdd_Click()
    'Copy input values to sheet.
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
    .Cells(iRow, 2).Value = Me.cboBatch.Value
    .Cells(iRow, 3).Value = Me.cboStrain.Value
    .Cells(iRow, 4).Value = Me.txtQuantity.Value
    .Cells(iRow, 5).Value = Me.cboUnits.Value
    .Cells(iRow, 6).Value = Me.cboVaultLocation.Value
    .Cells(iRow, 7).Value = Me.cboPackageType.Value
    .Cells(iRow, 8).Value = Me.txtNumberofPackages
    .Cells(iRow, 9).Value = Me.txtHarvestDate.Value
    .Cells(iRow, 10).Value = Me.txtDevelopedDate.Value
    .Cells(iRow, 11).Value = Me.txtVaultEntryDate.Value
    .Cells(iRow, 12).Value = Me.cbYES.Value

    End With
    'Clear input controls.
    Me.cboBatch.Value = ""
    Me.cboStrain.Value = ""
    Me.txtQuantity.Value = ""
    Me.cboUnits.Value = ""
    Me.cboVaultLocation.Value = ""
    Me.cboPackageType.Value = ""
    Me.txtNumberofPackages.Value = ""
    Me.txtHarvestDate.Value = ""
    Me.txtDevelopedDate.Value = ""
    Me.txtVaultEntryDate.Value = ""

    End Sub

  • [USER="328791"]XenoCode[/USER] Should I be using a 'region' command? This is my first time ever doing any sort of code. My guess is that I want to try and tell it to go to the bottom of each respected column and track back up until it finds the next empty row. I'm just not sure how to tell it to do this.

    iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!