Printable View
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
Code:Dim vec As Variant
vec = Split(ActiveCell.Value, ",")
Me.Textbox1.Text = vec(UBound(vec)) + 1
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
I thought all of the numbers were in one cell.
You probably need
Code:Me.TextBox1.Text = Application.Max(Worksheet("Sheet2").Columns(2)) + 1
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
Number in the last row, or the highest number?
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
Then MAX() + 1 should do it fine.
ok do i need to tell it which column and row to start looking in?
Place it in the column below the current last number
but i need it to be displayed in textbox in a userform.
Post #4 gave you what you want.
ah thank you
i am getting an error : compile error, sub or function not defined
here is the code i have used
Code:Private Sub UserForm_Initialize()
Me.txtjobNumber.Text = Application.Max(Worksheet("sheet4").Columns(2)) + 1
End Sub
Sorry, it should be Worksheets not Worksheet.
its now saying subscript out of range
That means you don't have a Sheet4.
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.