Userform output range

[VanillaSky]

Hello.

I am attempting to create table as below:

https://i.gyazo.com/3edbf9938d1ae6af454ceff56f22a4d5.png

I've created userform to input new data in B:G range. However I can't make it work properly, as userform is looking for empty rows
to insert new entries when there are not empty cells in A and I columns. Please note the code below:



Sub CommandButton_Submit_Click()

Dim iRow As Long

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

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


I assume that I should specify Range for data output B:G columns, however my poor knowledge of VBA does not allow me to do that. :crying:

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.

[SamT]

Option Explicit

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 ClearData() 'Better = "ClearDataControls," or "Clear_ioCtrls."
With Me
.TextBox_PI_Case = ""
.TextBox_Company_Na = ""
.TextBox_Status = ""
.TextBox_RoR = ""
.TextBox_Comnts = ""
.TextBox_PI_Case.SetFocus
End With
End Sub


Private Sub OutPutData()
Dim NextRow As Range

Set NextRow = Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Offset(1).Resize(1, 6)
With Me
NextRow.Cells(1) = .TextBox_PI_Case
NextRow.Cells(2) = .TextBox_Company_Na
NextRow.Cells(3) = .TextBox_Status
NextRow.Cells(4) = .TextBox_RoR
NextRow.Cells(5) = .TextBox_Comnts
NextRow.Cells(6) = Date
End With
End Sub


Cells(Rows.count, "Column Letter or number") = Bottommost Cell in Column
End(xlUp) = Last used Cell in Column Same as Ctrl+Up Arrow on keyboard
Offset(1) = Next Cell down
.Resize(1,6) = That Cell and the 5 Cells to the right

The Range Variable "NextRow", or better, "NextAvailableRecordRow" only contains 6 cells, all on the first empty row in column "B" in this code. The Cells are indexed (Numbered) from left to right.

[NoSparks]

when there are not empty cells in A and I columns
Correct me if I'm mistaken but won't

.Cells(Rows.Count, "B").End(xlUp)
stop when it runs into the bottom of the table even though there are empty B cells above ?

[VanillaSky]

Works like a charm, thank you so very much!

[SamT]

Correct me if I'm mistaken but won't

.Cells(Rows.Count, "B").End(xlUp)
stop when it runs into the bottom of the table even though there are empty B cells above ?

Yes it will. One must plan ahead.

[SamT]

Works like a charm, thank you so very much!

I marked the thread "solved." you can manage this by using Thread Tools on your own threads.