0

So basically i am trying to add data to a table using a user form that opens with a button on the main worksheet, this all works fine and the data inputted into the form is put into the "logs" worksheet, however instead of adding to the next available row below the table it adds it 20+ rows down, images attached below.

User form:

User Form

Data:

data

Code:

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Logs")

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

With ws
'  .Unprotect Password:="password"
  .Cells(iRow, 1).Value = Me.txtDateSelection.Value
  .Cells(iRow, 2).Value = Me.txtTicker.Value
  .Cells(iRow, 3).Value = Me.txtTransactionType.Value
  .Cells(iRow, 4).Value = Me.txtValue.Value
  .Cells(iRow, 5).Value = Me.txtFee.Value
  .Cells(iRow, 6).Value = Me.txtCoinValue.Value
'  .Protect Password:="password"
End With

'clear the data
Me.txtDateSelection.Value = ""
Me.txtTicker.Value = ""
Me.txtTransactionType.Value = ""
Me.txtValue.Value = ""
Me.txtFee.Value = ""
Me.txtCoinValue.Value = ""



End Sub```
Rhys Keown
  • 29
  • 3
  • 3
    Use `ws.Range("A" & ws.Rows.Count).End(xlUp).Offset(1).Row` to find the last row instead. From my experience (and by [popular demand](https://stackoverflow.com/a/38882823/6706419)), this is the standard/best practice method. I would only deviate from this method if there was a specific reason to – urdearboy Mar 05 '21 at 19:53

1 Answers1

1

You can insert a row directly into the table regardless of the table location with the ListRows.Add method.

With Sheets("Sheet1").ListObjects("Table1").ListRows.Add.Range
    .Cells(1, 1) = Me.txtDateSelection.Value
    .Cells(1, 2) = Me.txtTicker.Value
    .Cells(1, 3) = Me.txtTransactionType.Value
    .Cells(1, 4) = Me.txtValue.Value
    .Cells(1, 5) = Me.txtFee.Value
    .Cells(1, 6) = Me.txtCoinValue.Value
End With
CDP1802
  • 13,871
  • 2
  • 7
  • 17