user form deletes first row contents of table

[Project217]

HI All.

I am trying to add a user form to an excel data table with formulas, I have managed to get the user form to add the data to the table, however it keeps over writing the data on the first row of the data table and not inputting it on there next available row, Can you please help me with this?

This is the VBA I have used.

Private Sub CommandButton1_Click()

Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Master Sheet")
'find first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
With ws
.Range("D4") = TextBox4.Value
.Range("R4") = TextBox5.Value
.Range("C4") = TextBox6.Value
End With
TextBox4.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""


End Sub

Any help on this would be awesome! Thanks.

Rory.

 

[Project217]

Also I tried the Lrow and it came up with a compile syntax error.

 

[Fluff]

Do you have any data in col A?

 

[Project217]

Do you have any data in col A?

No not in master sheet or sheet 1

 

[Fluff]

In that case you would need to use

Lrow = ws.Cells(Rows.count, 2).End(xlUp).Offset(1, 0).Row

assuming that col B will always have a value.

 

[Project217]

No not in master sheet or sheet 1

In that case you would need to use

Lrow = ws.Cells(Rows.count, 2).End(xlUp).Offset(1, 0).Row

assuming that col B will always have a value.

It worked, But it stores the data in rows from 99992,9993 ect.

 

[Fluff]

In that case clear all cells in column B below the last used row.

 

[Project217]

Thank you! That works perfectly, except how do i get it to automatically add the data in my data table "Table2"?

 

[Fluff]

How about

Private Sub CommandButton1_Click()

Dim NxtRw As ListRow
Dim ws As Worksheet
Set ws = Worksheets("Master Sheet")
'find first row in database
Set NxtRw = ws.ListObjects("Table2").ListRows.Add
With NxtRw
   .Range(4) = TextBox4.Value
   .Range(17) = TextBox5.Value
   .Range(1) = TextBox6.Value
End With
TextBox4.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""


End Sub

 

[Project217]

Your a legend Fluff! worked perfectly. but I think when I changed the code to allow my new cell locations I have done something wrong, it now adds the "Name:" text box value 5 into the "Ordered By:" column one row below the rest (See pic attached) I have also attached the line of code you sent me with my changes (so you can hopefully see where I've made the mistake)
Thanks so much for your help.

 

[Fluff]

What column should it be in? from the code you originally posted it looks as though it should be in Col R, in which case use the code in post#18