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.

 

[Fluff]

Hi & welcome to MrExcel.
Try it like

.Range("D" & lRow) = TextBox4.Value

 

[Project217]

Hi & welcome to MrExcel.
Try it like

.Range("D" & lRow) = TextBox4.Value

HI Fluff,
Thanks for your reply.

I tried this line

Private Sub CommandButton1_Click()

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


End Sub

and it is coming up with compile error syntax error.

I am using Mac OS if that helps?

 

[Fluff]

You need to remove the row number from the ranges like I showed. Also you have 1row whereas your variable is irow

 

[mikerickson]

I notice that the code uses both eyeRow and oneRow variables.

 

[Project217]

Thanks for your reply.

I Tried to change the 1 row like this

Private Sub CommandButton1_Click()

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


End Sub

and it came up with a Run time error
Method 'Range of objective ' _worksheet' failed

But when I take out the irow or 1 row it works?
Sorry as you can possibly tell I'm very new to this and I'm learning as I go.
Really appreciate the help.

 

[Fluff]

Try it like

Private Sub CommandButton1_Click()

Dim Lrow As Long
Dim ws As Worksheet
Set ws = Worksheets("Master Sheet")
'find first row in database
Lrow = ws.Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row
With ws
   .Range("E" & Lrow) = TextBox4.Value
   .Range("R" & Lrow) = TextBox5.Value
   .Range("B" & Lrow) = TextBox6.Value
End With
TextBox4.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""


End Sub

 

[Project217]

Of course, that makes a lot of sense, thank you all very much!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Project217]

You're welcome & thanks for the feedback.

Hi,
So it keeps overwriting on the same cell or if I use the master sheet it will write the data in row 4000 and something but will overwrite the row every time, I have tried to use the mini sheet add in and attached the code I have used.
Im not sure what I have done wrong, but I did create the Userform on a windows computer and I'm coding it on my Mac don't know if there is a clash between operating systems??

Order Form.xlsm
	B	C	D
1	Column1	Column2	Column3
2	4tryber	erfv4rtg	4rtg4rtg
3
Sheet1