auto sequential number generator

[rvanbrussel]

Hi:

Not very good a VBA and found from Mr. Excel routine that will automatically generate a sequential number for each new sales order:

Sub SeqNumGenerator()
Range("G4").Value = Range("G4").Value + 1
End Sub

We print out 20-25 sales orders at a time to fill in manually when a customer comes into our shop. What I need to do next and have not been able to figure out is how to send the print command after the next number has been generated. Any help is appreciated. We do the sequential number/print process manually now and it is labor intensive. Thank you in advance for any help and direction

 

[rvanbrussel]

Hi David:

Yes you are correct our numbering format is YYMM-XXX where that complete number is in G6, so there is a dash included with the number in G6. So from what I understand you to mean, the easy fix is to have the YYMM (only advanced 1X/month) in one cell, say G5 and the unique number per work order (XXX) in G6???

Public Sub Print_Invoices()
Dim invoiceRange As Range, printRange As Range
Set invoiceRange = Range("G6")
Set printRange = Range("A3:G49")
x = 0
Do
printRange.PrintOut Copies:=1
invoiceRange.Value = invoiceRange.Value + 1
x = x + 1
Loop While x < Range("C1").Value
End Sub
[CODE]

The notation "Copies to be printed" is in A1:B1 (merged cells) and the variable value XX (number of copies to be printed) is in cell C1

Also when I do select the "start macro" button I made (which is a drawn shape extended across E1:G1) it will print out the 2 copies (the values I designated in C1) but at the end of the routine Excel give me a dialogue box error "Type Mismatch"

Remember I have a MAC so the F8 feature is not the same. The commands under the debug menu are Command key CMD | =Step Into/CMD O =Step Over/CMD T = Step Out...so not sure what each of those mean and will look at the help menu to figure out

 

[rvanbrussel]

So I have used the CMD | or Step Into debugger... it skips over the Dim line and when it gets to the invoice.Range.value line I get dialogue box runtime error '13": and Type Mismatch

 

[davidam]

To make your code appear properly, I think you need to type Code, not CODE, and you need the / in the second box. No matter, your code looks fine. The type mismatch is probably from the issue with the dash.
As to the fix, having the work order # in two cells would work but then you have to make big changes. I would be more inclined to have a derived value in one of the cells along the top and then make G6 equal to that cell. Lets say, for example, you make the invoiceRange = Range("K1") and that this just has a number that increase with the generator. Then put your prefix in J1, say you type in 1405 Then in L1 have this formula: =J1&"-"&Text(K1,"00#"). Make G6 = L1. If you put a 1 in K1, the invoice number would be 1405-001.
The above will do the trick, but if it was me, I would go even further and have a couple of Data Validation drop-downs with month and year and then derive the prefix from these, but then I can't help myself! If you want, I will show you how to do these.

 

[rvanbrussel]

Ohhh I got it the

 is for debugging...got it now. Love the fix and I will write in that formula. And yes how would the DataValidation look so I can change YYMM at the appropriate time. 
Thanks so much...my name is Roger

 

[davidam]

Hi Roger,
It should be working fine now but for fun when you have the time:
I am always fussy about arranging where to put things so treat all these ranges as flexible.
In a cell, lets assume it is J2, type Jan In J3 type Feb, in k2 type 1, in K3 type 2, in L2 type 2014, in L3 type 2015.
Select all 6 cells and drag them down to Row 13 You should see Jan down to Dec, 1 down to 12, and 2014 down to 2025.
Now for you dropdowns: I don't know where you will have a good place to put them, but for months, lets say it is E2. Select E2 and go to Data>DataValidation Select List and then put in $J$2:$J$13
For years ,lets sayG2, do the same and put in $L$2:$L$13. I would do as you have done and put an instruction like Select Month and Select year, to the left of each of these and I also like to put a box around the data validation cells. Now when you click on either of these cells you should see a list of months or years,depending on which one. Now, somewhere in the vicinity, probably out of view,like in M2, put a cell with a vLookup referencing the cell with the month dropdown: =VLOOKUP(E2,$J$2:$J$13,2) and that should give you a month number, Lets assume this is cell M2. So in M2 you have the month, in G2 you have the year, and K1 you have the invoice #
New Formula in J1 would be =Right(G2,2)&Text(M2,"0#")&"-"&Text(K1,"00#").
Again, locations to be determined by you, but you get the picture. Hope it all works out for you and let me know if you have any problems.
David

 

[rvanbrussel]

Hi David:

Thanks so much for all your help. I learned a lot in the process. The SeqNumGenerator works perfectly. I have not completed the DataValidation part yet but will tackle that next but I just wanted you to know all is working well.


Regards,
Roger

 

[davidam]

Roger,
Thanks for letting me know. I have gained so much from the people on this forum and it is a pleasure to be able to help.
Regards,
David