Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
slorryy
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gord Dibben
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Richard Buttrey
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
slorryy
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Richard Buttrey
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
slorryy
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Return SEARCHED Column Number of Numeric Label and Value Sam via OfficeKB.com Excel Worksheet Functions 23 January 30th 06 07:16 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 03:28 AM
How do create a formula to evalute a # to return 1 of 4 conditions Larry Excel Worksheet Functions 4 May 29th 05 12:58 AM
How to create specific formula STS Excel Worksheet Functions 4 May 2nd 05 01:44 AM
Need number of months or weeks passed from formula Cowtoon Excel Worksheet Functions 9 November 12th 04 10:06 PM


All times are GMT +1. The time now is 04:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"