Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a formula to create an order number
Hi People I am trying to deign a form and excel. I need a function to auto create a order number (basically a function/formula that add 1 onto the previous order number when a new order is created) Hope that makes sence Any help would be fab Mark -- slorryy ------------------------------------------------------------------------ slorryy's Profile: http://www.excelforum.com/member.php...o&userid=34183 View this thread: http://www.excelforum.com/showthread...hreadid=539451 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a formula to create an order number
Mark
Have a look at John McGimpsey's VBA method. http://www.mcgimpsey.com/excel/sequentialnums.html Gord Dibben MS Excel MVP On Fri, 5 May 2006 17:56:40 -0500, slorryy wrote: Hi People I am trying to deign a form and excel. I need a function to auto create a order number (basically a function/formula that add 1 onto the previous order number when a new order is created) Hope that makes sence Any help would be fab Mark Gord Dibben MS Excel MVP |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a formula to create an order number
On Fri, 5 May 2006 17:56:40 -0500, slorryy
wrote: Hi People I am trying to deign a form and excel. I need a function to auto create a order number (basically a function/formula that add 1 onto the previous order number when a new order is created) Hope that makes sence Any help would be fab Mark Perhaps the simplest way is to hold the current order number as a variable in a cell. Then with the event that triggers a new order, add a line of code that increases the order number variable by 1. So suppose your order numbers are are of the form ON1234, ON1235 etc, where there is a prefix "ON" to a sequential number, and the variable cell is named say "Onumber" add a line of code Range("Onumber")= "ON" & Right(Range("Onumber"), Len(Range("Onumber")) - 2) + 1 HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a formula to create an order number
Richard Buttrey Wrote: On Fri, 5 May 2006 17:56:40 -0500, slorryy wrote: Hi People I am trying to deign a form and excel. I need a function to auto create a order number (basically a function/formula that add 1 onto the previous order number when a new order is created) Hope that makes sence Any help would be fab Mark Perhaps the simplest way is to hold the current order number as a variable in a cell. Then with the event that triggers a new order, add a line of code that increases the order number variable by 1. So suppose your order numbers are are of the form ON1234, ON1235 etc, where there is a prefix "ON" to a sequential number, and the variable cell is named say "Onumber" add a line of code Range("Onumber")= "ON" & Right(Range("Onumber"), Len(Range("Onumber")) - 2) + 1 HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ Hi Richard, Sounds like that will work. But the only thing i cant work out is what event will trigger the new order. I was hopeing the new order number would be triggered when the file is opened. can you think of another way to do it? The file i am working on is here if you wish to have a look http://www.slorryy.com/tekbo.xls Thanks -- slorryy ------------------------------------------------------------------------ slorryy's Profile: http://www.excelforum.com/member.php...o&userid=34183 View this thread: http://www.excelforum.com/showthread...hreadid=539451 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a formula to create an order number
On Sat, 6 May 2006 18:54:52 -0500, slorryy
wrote: Richard Buttrey Wrote: On Fri, 5 May 2006 17:56:40 -0500, slorryy wrote: Hi People I am trying to deign a form and excel. I need a function to auto create a order number (basically a function/formula that add 1 onto the previous order number when a new order is created) Hope that makes sence Any help would be fab Mark Perhaps the simplest way is to hold the current order number as a variable in a cell. Then with the event that triggers a new order, add a line of code that increases the order number variable by 1. So suppose your order numbers are are of the form ON1234, ON1235 etc, where there is a prefix "ON" to a sequential number, and the variable cell is named say "Onumber" add a line of code Range("Onumber")= "ON" & Right(Range("Onumber"), Len(Range("Onumber")) - 2) + 1 HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ Hi Richard, Sounds like that will work. But the only thing i cant work out is what event will trigger the new order. I was hopeing the new order number would be triggered when the file is opened. can you think of another way to do it? The file i am working on is here if you wish to have a look http://www.slorryy.com/tekbo.xls Thanks Yes the Workbook Open event could trigger the new order number. But what then? Are you eventually going to save the new order with a new name or just print it? Either way you would need to save immediately the blank order that you open as soon as the workbook open event has triggered the change of number. That way the next time you open the order it will continue to open with the last number already in place. So in the VBA Project window, expand the Microsoft Excel Objects list by clicking on the '+' sign, double click on the ThisWorkbook object, and over on the right hand side of the VBA window, select the Workbook Object from the left hand drop down and then the 'Open' event from the right hand drop down window. Now enter the following code. Private Sub Workbook_Open() Range("Onumber") = Range("Onumber") + 1 ActiveWorkbook.Save End Sub I have named the cell G6 on Sheet1of your tekbo.xls template as "Onumber" HTH - let me know if not. __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a formula to create an order number
Hi Richard Thanks for your help regarding this subject, I have decided to just enter the numbers manually as I am not educated inVB. Cheers for your time. -- slorryy ------------------------------------------------------------------------ slorryy's Profile: http://www.excelforum.com/member.php...o&userid=34183 View this thread: http://www.excelforum.com/showthread...hreadid=539451 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
How do create a formula to evalute a # to return 1 of 4 conditions | Excel Worksheet Functions | |||
How to create specific formula | Excel Worksheet Functions | |||
Need number of months or weeks passed from formula | Excel Worksheet Functions |