CODEWITHSUNDEEP
excelvba
Rhys Keown
Rhys Keown

Reputation: 29

User form adds data 20+ rows below where it should in Excel

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```

Upvotes: 0

Views: 43

Answers (1)

CDP1802
CDP1802

Reputation: 16184

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

Upvotes: 1

Related Questions