Userform Textbox data to new row in specified range

Tojomv

New Member
Joined
Dec 31, 2014
Messages
18
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.
 
Last edited:

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Hi & welcome to the board.
In what way is your 1st code not working?
 
Upvote 0
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.
 
Upvote 0
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.


Code:
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
 
Upvote 0
In that case try
Code:
lastrow = Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp).Row
If lastrow < 13 Then lastrow = 13
 
Upvote 0
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
 
Upvote 0
Apologies that should have been
Code:
If lastrow < 12 Then lastrow = 12
 
Upvote 0

Forum statistics

Threads
1,224,521
Messages
6,179,280
Members
452,902
Latest member
Knuddeluff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top