Userform output range

[VanillaSky]

Hello.

I am attempting to create table as below:

I've created userform to input new data for PI_Case to Opening_Date cells. However I can't make it work properly, as userform is looking for empty rows
to insert new entries. Please note the code below:

<code>Sub CommandButton_Submit_Click()

Dim iRow As Long

Dim ws As Worksheet
Set ws = Worksheets("List")

myDate = Date

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'check for a Name number
If Trim(Me.TextBox_Company_Name.Value) = "" Then
  Me.TextBox_Company_Name.SetFocus
  MsgBox "Please complete the form"
  Exit Sub
End If

'copy the data to the database

ws.Cells(iRow, 1).Value = Me.TextBox_PI_Case.Value
ws.Cells(iRow, 2).Value = Me.TextBox_Company_Name.Value
ws.Cells(iRow, 3).Value = Me.TextBox_Status.Value
ws.Cells(iRow, 4).Value = Me.TextBox_RoR.Value
ws.Cells(iRow, 5).Value = Me.TextBox_Comments.Value
ws.Cells(iRow, 6).Value = myDate
MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"
'clear the data
Me.TextBox_PI_Case.Value = ""
Me.TextBox_Company_Name.Value = ""
Me.TextBox_Status.Value = ""
Me.TextBox_RoR.Value = ""
Me.TextBox_Comments.Value = ""
Me.TextBox_PI_Case.SetFocus

End Sub
</code>

I assume that I should specify Range for data output for PI_Case to Opening_Data columns, however my poor knowledge does not allow me to do that.

Is there any possibility to make it work or VBA only allows to insert data from userform starting with first column?

Thank you in advance.

 

[My Aswer Is This]

Try this:

Dim irow As Long
irow = Cells(Rows.Count, "A").End(xlUp).Row + 1

 

[VanillaSky]

Thank you for your response.

It works only if the cells on the left and right are empty.

 

[My Aswer Is This]

So your not always entering new data in column 1 ??

Thank you for your response.

It works only if the cells on the left and right are empty.

 

[VanillaSky]

As I said i want to enter new data in PI_Case to Opening_Date cells. So that would be from 2nd to 7th column.

Sorry if I wasn't clear

 

[VanillaSky]

Solution:

Private Sub OutPutData()
    Dim NextRow As Range
     
    Set NextRow = Worksheets("List").Cells(Rows.Count, "B").End(xlUp).Offset(1).Resize(1, 6)
    With Me
    
        NextRow.Cells(1) = .TextBox_PI_Case
        NextRow.Cells(2) = .TextBox_Company_Name
        NextRow.Cells(3) = "NEW"
        NextRow.Cells(4) = .TextBox_RoR
        NextRow.Cells(5) = .TextBox_Comments
        NextRow.Cells(6) = Date
    End With
End Sub


Private Sub ClearData() 'Better = "ClearDataControls," or "Clear_ioCtrls."
    With Me
        .TextBox_PI_Case = ""
        .TextBox_Company_Name = ""
        .TextBox_RoR = ""
        .TextBox_Comments = ""
        .TextBox_PI_Case.SetFocus
    End With
End Sub


 
Sub CommandButton_Submit_Click()
     
     'check for a Name number
    If Trim(Me.TextBox_Company_Name.Value) = "" Then
        Me.TextBox_Company_Name.SetFocus
        MsgBox "Please complete the form"
        Exit Sub
    End If
     
     'copy the data to the database
    OutPutData
     
    MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"
     
     'clear the data
    ClearData
     
End Sub


Private Sub CommandButton_Cancel_Click()
Unload Me
End Sub