Userform Textbox data to new row in specified range

[Tojomv]

Hi,


I am struggling to get my project to work as I want. I am just learning.


I am creating an invoice system where the user enters the data via a userform and struggling to find a method to go to the next row.
Once the user has entered the first line of data and clicks CommandButton Add, the userform is cleared and the user needs to enter the second line of data in the same textboxes.

The problem that I have is the data has to be entered into a specific range. i.e. first row is D13 to F13, then once added the second row
needs to be copied to D14 to F14 and so on.


I can get it to find the last row and enter the data using the following but not in the specific cells I require


Sheets("Sheet1").Activate


Lastrow = Sheets("Sheet1").Range("D" & Rows.count).End(xlUp).Row


Cells(Lastrow + 1, "D").Value = TxtQty.Value
Cells(Lastrow + 1, "E").Value = TxtDes.Value
Cells(Lastrow + 1, "F").Value = TxtRate.Value


If TxtRate.Value = "" Then

Range("F13").Value = TxtMRate.Value

End If


TxtQty.Value = ""
TxtDes.Value = ""
TxtRate.Value = ""
TxtMRate.Value = ""




I have also tried the following where it will copy the data to the correct cells but can't work out how to move to the next row once the
Button is clicked and data is cleared.




Range("D13").Value = TxtQty.Value
Range("E13").Value = TxtDes.Value
Range("F13").Value = TxtRate.Value

If TxtRate.Value = "" Then

Range("F13").Value = TxtMRate.Value

End If


TxtQty.Value = ""
TxtDes.Value = ""
TxtRate.Value = ""
TxtMRate.Value = ""


Hope someone can assist, This is the last piece of code required to complete the project.


Regards and Thanks in Advance.

 

[Fluff]

Hi & welcome to the board.
In what way is your 1st code not working?

 

[Tojomv]

Hi & welcome to the board.
In what way is your 1st code not working?

The 1st code searches for the first empty line in range D not D13, the invoice does not have data in all of cells in column D so will place the data outside of the invoice template. So if D1 has no data then the code would write the data from the userform to that.

I have worked out a long winded way of getting it to do what I require (not tested yet) by creating multiple buttons to add the data, each button linked to a particular range i.e. D13:F13, then when the button is clicked that button is then hidden and the next button is made visible and data will be written to range D14:F14 and so on.

If there is an easier way then I would be grateful.

 

[mikerickson]

So the issue is that when column D is empty, the LastRow variable gets the wrong value.
You could try testing all three columns to find the LastRow value.

Dim oneColumn as Range

LastRow = 0
For each oneColumn in Range("D:F").Columns
    LastRow = WorksheetFunction.Max(oneColumn.Cells(Rows.Count, 1).End(xlup).Row, LastRow)
Next oneColumn

 

[Fluff]

In that case try

lastrow = Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp).Row
If lastrow < 13 Then lastrow = 13

 

[Tojomv]

Thanks for getting back to me.

I am not sure if that will work. I will try and clarify my aim.

The Userform has 4 TextBoxes.
User enters Data in 3 of the boxes and then clicks cmdbutton. The data in TxtBox1 goes into D13, TxtBox2 into E13, and TxtBox 3 or 4 whichever is used goes into F13. When the user then adds data back into the textboxes (having been cleared on click of cmdbutton) the data then needs to go into D14, E14 and F14 and so on for up to max 10 rows.

Regards

 

[Fluff]

Apologies that should have been

If lastrow < 12 Then lastrow = 12

 

[Tojomv]

Apologies that should have been

If lastrow < 12 Then lastrow = 12

I will give it a go thanks.

I have managed to do it the long way round but will try your method on copy invoice first.

Regards