Generating Next Avail No.

[splash]

Hi all,

I have to create a simple "job number generator" - so the user selects certain criteria - then a job number is created from the critiera PLUS the next unique number.

I have three selection boxes, where the user selects their criteria from drop down menus.

Then based on the choice, I generate a job number, such as 001 AB Axxxxx, the xxxxx indicates that the next unused number should be used.

For example;
001 AB A10001
008 AB A10002
007 AC A10003

So, I have my selection boxes and a "generate next job number" command button, but need to write some code that takes the results from the drop downs (simple vlookup) then adds the next available number and puts the results in the next blank row.

Any help, as usual, would be much appreciated.

Thanks

Splash.

 

[WinteE]

Hi,

From your example it looks like the next number should be A10004, so why do you use a command button to get the next number ? Guess you can do this with an IF statement, and calculate the number when the cells beside it are filled.

Or is there a relation between the number and the selected data ?

Erik

 

[splash]

No help on this one, guys?

 

[splash]

Hi,

From your example it looks like the next number should be A10004, so why do you use a command button to get the next number ? Guess you can do this with an IF statement, and calculate the number when the cells beside it are filled.

Or is there a relation between the number and the selected data ?

Erik

Hi Erik - thanks for the response.

I have a command button working (thanks for the tip) and it generates the next number perfectly - now I need it to paste the results on the next available blank line - any ideas?

 

[Peter_SSs]

I have a command button working (thanks for the tip) and it generates the next number perfectly - now I need it to paste the results on the next available blank line - any ideas?

If the next number is held in variable NextNumber and the results go in column E then try adding this to your code.

Range("E" & Rows.Count).End(xlUp).Offset(1).Value = NextNumber

 

[Michael M]

So, if the next blank required is in column "C" paste the value to

lr = cells(rows.count,"C").end(xlup).row+1

Too quick Peter !

 

[splash]

Hi Gents
Apologies - I am really struggling with this one.
I think I need to better explain - I have three drop down options for the user, in B7, B8 and B9. Ideally, the user will select an option from each drop down list and then hit a macro button "generate new job number" which will take the three choices from B7,8 and 9 and then add the next avaiable number to create a job number on the next line.

So,
B7 - user selects 'A44'
B8 - user selects '05'
B9 - user selects 'G'

Upon hitting the "generate job number" command button the job number A44-05-G1010 (1010 being the next number not used) is pasted into the next available empty row (say Row 11). The next user would be, for example, B77-05-F1011, posted in row 11.

As always - any help really is appreciated.

Regards

Splash.

 

[Peter_SSs]

Assuming that the resulting values go in column B below the input cells and that there is already at least one code with a 4-digit final value existing in column B, try this (in a copy of your workbook)

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()<br>    <SPAN style="color:#00007F">Dim</SPAN> rInput <SPAN style="color:#00007F">As</SPAN> Range, Blnks <SPAN style="color:#00007F">As</SPAN> Range, LastCell <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> Sel <SPAN style="color:#00007F">As</SPAN> Range, ActCell <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> NewCode <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> sInput <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "B7:B9" <SPAN style="color:#007F00">'<- Input range</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rInput = Range(sInput)<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> Blnks = rInput.SpecialCells(xlCellTypeBlanks)<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>    <SPAN style="color:#00007F">If</SPAN> Blnks <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> Sel = Selection<br>        <SPAN style="color:#00007F">Set</SPAN> ActCell = ActiveCell<br>        <SPAN style="color:#00007F">Set</SPAN> LastCell = Range("B" & Rows.Count).End(xlUp)<br>        <SPAN style="color:#00007F">With</SPAN> rInput<br>            NewCode = .Cells(1, 1).Value _<br>                & Format(.Cells(2, 1).Value, "-00-") _<br>                & .Cells(3, 1).Value _<br>                & Right(LastCell.Value, 4) + 1<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        LastCell.Offset(1).Value = NewCode<br>        Sel.Select<br>        ActCell.Activate<br>    <SPAN style="color:#00007F">Else</SPAN><br>        Blnks.Select<br>        MsgBox "Input cells incomplete"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>