Display next available number

  • Thread starter Thread starter clayton
  • Start date Start date
C

clayton

I hope someone can help with this.

Column A I have numbers that look like TSC-10-001,TSC-10-102
TSC-10-052. Then they go to TSC-20-051, TSC-20-422, TSC-20-10, etc..
have set up data validation so that there is no duplicated allowed bu
it is very difficult to find the next available number.
I need some way of finding the next available number for say TSC-70. S
if the last TSC-70 entered was TSC-70-177 it would tell me "The nex
available number for TSC-70 is TSC-70-178"
Any Ideas on this one
 
Assuming that you always have 3 numbers to the right of the last -. (if you
don't, it can be fixed)
I broke this down into segments so you can see how it works.

Sub nextavailnum()
x = Cells(Rows.Count, "n").End(xlUp)
MsgBox x
y = Left([x], Len([x]) - 3)
MsgBox y
MsgBox "The next avail num is " & y & Right(x, 3) + 1
End Sub
 
BTW The [ ] around [X] may be eliminated to be just X

--
Don Guillett
SalesAid Software
(e-mail address removed)
Don Guillett said:
Assuming that you always have 3 numbers to the right of the last -. (if you
don't, it can be fixed)
I broke this down into segments so you can see how it works.

Sub nextavailnum()
x = Cells(Rows.Count, "n").End(xlUp)
MsgBox x
y = Left([x], Len([x]) - 3)
MsgBox y
MsgBox "The next avail num is " & y & Right(x, 3) + 1
End Sub
Click to expand...
 
Mr. Guillett,

Thank you for your reply :)

It looks as though I may not have explained myself exactly right or
didn’t do something correct.

The code you gave gives me the next available number for th
TSC-*40*-324 part. I need it to show the next number IN the 40 group
So the next one would be TSC-40-325 in this case. Also, I am usin
buttons for this. There will be a button for each group IE: 10, 20, 30
40, etc... I need them to find only the next number in its own group. O
maybe just one button that prompts for the group number?
I don’t know VB so your help is greatly appreciated :)

Also in this column are some text headers. IE: "Document #" Is there
way to avoid errors of having that text in there
 
NOT what you asked for. Your request below
I need some way of finding the next available number for say TSC-70. So
if the last TSC-70 entered was TSC-70-177 it would tell me "The next
available number for TSC-70 is TSC-70-178"
Click to expand...
 
As I interpret what I wrote it is asking for the next number of the 4
group... Im sorry I did not make myself more clear on that. ButI thought that covered it.
 
Clayton,

Your existing numbers are not consecutive (there appear to be missing
numbers), and they appear to be not sequential (not in order), so should the
yielded number be the next sequential number IF it doesn't already exist?
Or should it be the lowest possible number not currently in use (e.g.: 1, 2,
3, 5 so it gives 4)? Your TSC-70-177 example doesn't fit the examples of
existing data, which are in the TSC-10 group. Does the column of existing
numbers have all the groups (TSC-10, TSC-20, etc.) in it, or are the groups
in separate columns? Where is the solution to look to examine the currently
used numbers -- in the column that the active cell is in?
 
Hi Earl, Thanks for your reply.

To answer you first question the next available number should be th
lowest available number that does not exist in that group of numbers.
If say TSC-40-004 does not exist but TSC-40-001, 002, 003 and 005 d
then it would show TSC-40-004 as the next available number.
If the lowest existing number in the TSC-50 group is TSC-50-042 the i
would show TSC-50-001 as being the next available number.

Each group can have the same ending numbers. For example there can be
TSC-10-001 and a TSC-20-001 and so on.

Also, the existing numbers are not in order from top to bottom. The
might run like:
TSC-20-005
TSC-20-003
TSC-20-017
etc...


The group numbers TSC10, TSC20 ect... are in the same column as th
data. There is also other text in this column such as Document #


Ok, Im not real clear on what you are asking about where the solutio
to look to examine. If I answer wrong it’s because I don’t full
understand.
There could be a popup to ask for the section to be checked. You coul
enter a number here or maybe select from a dropdown or something. Bu
entering a number would be just the 10, 20 or 50 ect..
Clicking ok would result in the next available number for the group yo
entered
 
I don't know excel of VB very well at all. Can this even be done or am I
spinning my wheels?
 
Back
Top