Solved: find number then +1

[ashgull80]

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]

[vba]
Dim vec As Variant

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

[ashgull80]

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]

I thought all of the numbers were in one cell.
You probably need

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

[ashgull80]

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]

Number in the last row, or the highest number?

[ashgull80]

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]

Then MAX() + 1 should do it fine.

[ashgull80]

ok do i need to tell it which column and row to start looking in?

[Aussiebear]

Place it in the column below the current last number

[ashgull80]

but i need it to be displayed in textbox in a userform.

[Bob Phillips]

Post #4 gave you what you want.

[ashgull80]

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]

Sorry, it should be Worksheets not Worksheet.

[ashgull80]

its now saying subscript out of range

[Bob Phillips]

That means you don't have a Sheet4.

[ashgull80]

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.