userform to worksheet

rs2k

Well-known Member
Joined
Aug 15, 2004
Messages
1,413
I have created a userform which sends the data to a worksheet (works OK)
What I want is to add more data to the WS on the next available row. At the moment it just keeps overwriting the previous input in row2 (row1 has headings). I believe it works via xlup. or something like that.
Can anyone help?

Colin.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
You're close.

Say the data you're inputting to the sheet starts in column A:

[A65536].end(xlup).offset(1,0)

End(xlup) will find the last used cell looking up the column, and the offset(1,0) will tell it to use the next row down.
 
Upvote 0
Colin

Can you post your current code?

Generally to get the last row of data you can use something like this:

LastRow = Range("A65536").End(xlUp).Row
 
Upvote 0
Thanks for replying,
current code without any changes. Last night it worked fine (apart from not going to the next line) today, it gives me a "Runtime error 1004"

Private Sub DebitDrop1_Change()
LastRow = Range("B6000").End(xlUp).Row
ActiveWorkbook.Names.Add Name:="Creditors", RefersTo:="=Sheet2!$B$2:$B$" & LastERow
End Sub

The code was taken from this board (with changes)

I thought I made my first breakthrough with VB, now its just toying with me.

Colin.
 
Upvote 0
Which line is causing the error?

Is the active sheet the one you want to create the named range on?

Is LastRow returning the expected value?

Maybe this will work:
Code:
LastRow = ActiveWorkbook.Worksheets("Sheet2").Range("B65536").End(xlUp).Row
 
Upvote 0
Norie,
When I click debug (which I dont know how to use) it highlights this line in yellow

ActiveWorkbook.Names.Add Name:="Creditors", RefersTo:="=Sheet2!$B$2:$B$" & LastERow

The data is going onto Sheet2 in the correct columns, but throws up the error message. I will try your code and see what happens.
Colin.
 
Upvote 0
Right, your code has got rid of the error message (thanks), but it still not going down a row when new data is entered.
Any thoughts/
Colin.
 
Upvote 0
Just noticed that in that line you have LastERow.

Is that just a typo?

If it isn't then the value of that variable will be empty.

BTW if that is the case a good way to help avoid is to use Option Explicit.

This forces you to declare all variables.

You can set VBA to add it automatically by going to Tools>Options and checking Require Variable Declaration on the Editor tab.
 
Upvote 0
This is the original full code (removed the E, no error message) but still wont go to the next row down

Private Sub DebitDrop1_Change()
LastRow = Range("B6000").End(xlUp).Row
ActiveWorkbook.Names.Add Name:="Creditors", RefersTo:="=Sheet2!$B$2:$B$" & LastRow
End Sub

Colin.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

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