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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
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
 

rs2k

Well-known Member
Joined
Aug 15, 2004
Messages
1,413

ADVERTISEMENT

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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
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
 

rs2k

Well-known Member
Joined
Aug 15, 2004
Messages
1,413

ADVERTISEMENT

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.
 

rs2k

Well-known Member
Joined
Aug 15, 2004
Messages
1,413
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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
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.
 

rs2k

Well-known Member
Joined
Aug 15, 2004
Messages
1,413
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.
 

Forum statistics

Threads
1,148,224
Messages
5,745,472
Members
423,953
Latest member
MrC54

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
Top