VBA UserForm ListObjects

[JHud2022]

Hello! I am currently working on a project that requires me to insert data from a UserForm into the next blank row of an already created table. I first ran into the problem where it would only insert the new data from the UserForm into a newly created last row of the table, because some rows contain needed formulas. I was able to amend what the VBA I have to put the data into the next blank row, but only once. If I try to enter more data, it replaces only the first line in the table.
Hopefully this made sense. Summed up, I need to figure out how to amend my VBA code to allow a UserForm to populate input data into a table for the next blank row with formulas in different cells in the blank row. Below is my code so far. Like I said, everything works, except it only populates in the first row of the table, continuously replacing only the first row's data.

Private Sub OKButton_Click()

Dim rng As Range
Set rng = ActiveSheet.ListObjects("SunSch").Range

Dim LastRow As Long

LastRow = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).End(xlUp).Row
           
rng.Parent.Cells(LastRow + 1, 1).Value = StoreTextBox.Value
rng.Parent.Cells(LastRow + 1, 7).Value = TrailerTextBox.Value
rng.Parent.Cells(LastRow + 1, 8).Value = TractorTextBox.Value
rng.Parent.Cells(LastRow + 1, 9).Value = ProTextBox.Value
rng.Parent.Cells(LastRow + 1, 10).Value = LoadTextBox.Value
rng.Parent.Cells(LastRow + 1, 12).Value = DriverTextBox.Value
rng.Parent.Cells(LastRow + 1, 15).Value = StopTextBox.Value
rng.Parent.Cells(LastRow + 1, 19).Value = ConfirmedTextBox.Value
   
If DispatchCheck1.Value = True Then
    rng.Parent.Cells(LastRow + 1, 11).Value = "Yes"
ElseIf DispatchCheck2.Value = True Then
    rng.Parent.Cells(LastRow + 1, 11).Value = "No"
Else
    rng.Parent.Cells(LastRow + 1, 11).Value = ""
End If

If InfoCheck1.Value = True Then
    rng.Parent.Cells(LastRow + 1, 20).Value = "Yes"
ElseIf InfoCheck2.Value = True Then
    rng.Parent.Cells(LastRow + 1, 20).Value = "No"
Else
    rng.Parent.Cells(LastRow + 1, 20).Value = ""
End If

If LiveCheck1.Value = True Then
    rng.Parent.Cells(LastRow + 1, 30).Value = "Yes"
ElseIf LiveCheck2.Value = True Then
    rng.Parent.Cells(LastRow + 1, 30).Value = "No"
Else
    rng.Parent.Cells(LastRow + 1, 30).Value = ""
End If

If CancelCheck1.Value = True Then
    rng.Parent.Cells(LastRow + 1, 31).Value = "Yes"
ElseIf CancelCheck2.Value = True Then
    rng.Parent.Cells(LastRow + 1, 31).Value = "No"
Else
    rng.Parent.Cells(LastRow + 1, 31).Value = ""
End If

If SweepCheck1.Value = True Then
    rng.Parent.Cells(LastRow + 1, 32).Value = "Yes"
ElseIf SweepCheck2.Value = True Then
    rng.Parent.Cells(LastRow + 1, 32).Value = "No"
Else
    rng.Parent.Cells(LastRow + 1, 32).Value = ""
End If

If RevCheck1.Value = True Then
    rng.Parent.Cells(LastRow + 1, 33).Value = "Yes"
ElseIf RevCheck2.Value = True Then
    rng.Parent.Cells(LastRow + 1, 33).Value = "No"
Else
    rng.Parent.Cells(LastRow + 1, 33).Value = ""
End If

If BackhaulCheck1.Value = True Then
    rng.Parent.Cells(LastRow + 1, 34).Value = "Yes"
ElseIf BackhaulCheck2.Value = True Then
    rng.Parent.Cells(LastRow + 1, 34).Value = "No"
Else
    rng.Parent.Cells(LastRow + 1, 34).Value = ""
End If

End Sub

There are formulas in every cell besides the first in the included pictures. The first row starts in row 10. My range is SunSch which is my table name. I have to use the table name as my range so if there are more fields required, then the table can expand with the data.

I am continuing to try to figure it out, but any help would be greatly appreciated!

Thank you!!!

 

[DanteAmor]

Hi and welcome to MrExce:

Change this:

LastRow = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).End(xlUp).Row

For this:

LastRow = rng.Find("*", rng.Cells(1), xlValues, xlPart, xlByRows, xlPrevious, False).Row

 

[DanteAmor]

I optimized your code a bit.
Try this:

Private Sub OKButton_Click()
  Dim rng As Range
  Dim LastRow As Long
  
  Set rng = ActiveSheet.ListObjects("SunSch").Range
  LastRow = rng.Find("*", rng.Cells(1), xlValues, xlPart, xlByRows, xlPrevious, False).Row + 1
  rng.Parent.Cells(LastRow, 1).Value = StoreTextBox.Value
  rng.Parent.Cells(LastRow, 7).Value = TrailerTextBox.Value
  rng.Parent.Cells(LastRow, 8).Value = TractorTextBox.Value
  rng.Parent.Cells(LastRow, 9).Value = ProTextBox.Value
  rng.Parent.Cells(LastRow, 10).Value = LoadTextBox.Value
  rng.Parent.Cells(LastRow, 12).Value = DriverTextBox.Value
  rng.Parent.Cells(LastRow, 15).Value = StopTextBox.Value
  rng.Parent.Cells(LastRow, 19).Value = ConfirmedTextBox.Value
  
  With rng.Parent.Cells(LastRow, 11)
    If DispatchCheck1 Then .Value = "Yes" Else If DispatchCheck2 Then .Value = "No" Else .Value = ""
  End With
  With rng.Parent.Cells(LastRow, 20)
    If InfoCheck1 Then .Value = "Yes" Else If InfoCheck2 Then .Value = "No" Else .Value = ""
  End With
  With rng.Parent.Cells(LastRow, 30)
    If LiveCheck1 Then .Value = "Yes" Else If LiveCheck2 Then .Value = "No" Else .Value = ""
  End With
  With rng.Parent.Cells(LastRow, 31)
    If CancelCheck1 Then .Value = "Yes" Else If CancelCheck2 Then .Value = "No" Else .Value = ""
  End With
  With rng.Parent.Cells(LastRow, 32)
    If SweepCheck1 Then .Value = "Yes" Else If SweepCheck2 Then .Value = "No" Else .Value = ""
  End With
  With rng.Parent.Cells(LastRow, 33)
    If RevCheck1 Then .Value = "Yes" Else If RevCheck2 Then .Value = "No" Else .Value = ""
  End With
  With rng.Parent.Cells(LastRow, 34)
    If BackhaulCheck1 Then .Value = "Yes" Else If BackhaulCheck2 Then .Value = "No" Else .Value = ""
  End With
End Sub

 

[JHud2022]

That worked perfectly! I was working to try to figure this out all last night, and it turns out it was a pretty easy fix thanks to you!

Thank you so much!

 

[DanteAmor]

Im glad to help you, thanks for the feedback.

 

[JHud2022]

DanteAmor, I have one more question for you. I am trying to use the same UserForm for different sheets. I decided to export and import the UserForm for each of my sheets. I change just the range to match each table name on each sheet respectively. Now the issue is that when I add data via UserForm, every sheet after my first sheet that you helped me with puts the new data at the bottom of my table like it. Thoughts?