PDA

View Full Version : Solved: find number then +1

ashgull80
04-11-2012, 09:20 AM
hi
i have a textbox that i would like to display the next number in a list.
the list is on a sheet and is a list of job numbers, 12001,12002 ect ect,
when i open a userform i want the textbox to display the next number, so if the last number in the list is 12002 i want the textbox in the userform to disply 12003,
how can i do this?
thanks ash

Bob Phillips
04-11-2012, 09:30 AM
Dim vec As Variant

vec = Split(ActiveCell.Value, ",")
Me.Textbox1.Text = vec(UBound(vec)) + 1

ashgull80
04-11-2012, 09:41 AM
where in that code do i put the cell to start looking for the list?
the list is on shhet 2 and starts in column 2 row 3

Bob Phillips
04-11-2012, 09:49 AM
I thought all of the numbers were in one cell.
You probably need

Me.TextBox1.Text = Application.Max(Worksheet("Sheet2").Columns(2)) + 1

ashgull80
04-11-2012, 01:27 PM
the numbers are all in there own rows, so 12001 in row 3, 12002 in row 4 ect ect, i need it to find the last row with data and add 1 to that number.
thank you

Bob Phillips
04-11-2012, 01:41 PM
Number in the last row, or the highest number?

ashgull80
04-11-2012, 01:45 PM
well they go in order as it a job number and as a job comes in thats the next number. so starts the year at 12001, then 12002 ect ect which is why i want the text box to display the last rows number +1 as the userform is creating a new job so will the job next number

Bob Phillips
04-11-2012, 01:47 PM
Then MAX() + 1 should do it fine.

ashgull80
04-11-2012, 01:51 PM
ok do i need to tell it which column and row to start looking in?

Aussiebear
04-11-2012, 02:07 PM
Place it in the column below the current last number

ashgull80
04-11-2012, 02:20 PM
but i need it to be displayed in textbox in a userform.

Bob Phillips
04-11-2012, 04:00 PM
Post #4 gave you what you want.

ashgull80
04-12-2012, 12:47 AM
ah thank you
i am getting an error : compile error, sub or function not defined
here is the code i have used

Private Sub UserForm_Initialize()
Me.txtjobNumber.Text = Application.Max(Worksheet("sheet4").Columns(2)) + 1
End Sub

Bob Phillips
04-12-2012, 12:52 AM
Sorry, it should be Worksheets not Worksheet.

ashgull80
04-12-2012, 12:58 AM
its now saying subscript out of range

Bob Phillips
04-12-2012, 01:01 AM
That means you don't have a Sheet4.

ashgull80
04-12-2012, 01:12 AM
i get confused because sometimes the code is the worksheet code not the worksheet name, changed it to the worksheet name and is now workinmg perfect. thank you so much for your help.