Home |
Search |
Today's Posts |
#1
|
|||
|
|||
how do I increment a numbering system in excel
I wish to set up a Purchase Order system using Excel and i would like the PO
number to automatically increment. Can anyone pleaseadvise me how to do this? |
#2
|
|||
|
|||
Hi Allan,
See J.E. McGimpsey's suggestions at: http://www.mcgimpsey.com/excel/udfs/sequentialnums.html --- Regards, Norman "Allan" wrote in message ... I wish to set up a Purchase Order system using Excel and i would like the PO number to automatically increment. Can anyone pleaseadvise me how to do this? |
#3
|
|||
|
|||
Hi Norman
Sorry but I do not fully understand the macro. I have an Excel wookbook and in worksheet named "Sheet1" in cell I6 I wish to insert an new order number each time the workbook is opened that is the last PO number +1. I cannot see what to do with the link that you kindly sent to me. Can you help me further? Cheers Allan "Norman Jones" wrote: Hi Allan, See J.E. McGimpsey's suggestions at: http://www.mcgimpsey.com/excel/udfs/sequentialnums.html --- Regards, Norman "Allan" wrote in message ... I wish to set up a Purchase Order system using Excel and i would like the PO number to automatically increment. Can anyone pleaseadvise me how to do this? |
#4
|
|||
|
|||
Hi Allan,
If you are unfamiliar with macros, see the notes by JE McGimpsey on another page of his site: http://www.mcgimpsey.com/excel/index.html#usingvba Also see David McRitchie's 'Getting Started With Macros And User Defined Functions at: http://www.mvps.org/dmcritchie/excel/getstarted.htm If you are still experiencing problems, post back. --- Regards, Norman "Allan" wrote in message ... Hi Norman Sorry but I do not fully understand the macro. I have an Excel wookbook and in worksheet named "Sheet1" in cell I6 I wish to insert an new order number each time the workbook is opened that is the last PO number +1. I cannot see what to do with the link that you kindly sent to me. Can you help me further? Cheers Allan "Norman Jones" wrote: Hi Allan, See J.E. McGimpsey's suggestions at: http://www.mcgimpsey.com/excel/udfs/sequentialnums.html --- Regards, Norman "Allan" wrote in message ... I wish to set up a Purchase Order system using Excel and i would like the PO number to automatically increment. Can anyone pleaseadvise me how to do this? |
#5
|
|||
|
|||
Hi Norman
With your assistance I have solved the problem. Many Thanks Allan "Allan" wrote: I wish to set up a Purchase Order system using Excel and i would like the PO number to automatically increment. Can anyone pleaseadvise me how to do this? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I increment a numbering system in excel
"Norman Jones" wrote: Hi Allan, If you are unfamiliar with macros, see the notes by JE McGimpsey on another page of his site: http://www.mcgimpsey.com/excel/index.html#usingvba Also see David McRitchie's 'Getting Started With Macros And User Defined Functions at: http://www.mvps.org/dmcritchie/excel/getstarted.htm If you are still experiencing problems, post back. --- Regards, Norman "Allan" wrote in message ... Hi Norman Sorry but I do not fully understand the macro. I have an Excel wookbook and in worksheet named "Sheet1" in cell I6 I wish to insert an new order number each time the workbook is opened that is the last PO number +1. I cannot see what to do with the link that you kindly sent to me. Can you help me further? Cheers Allan "Norman Jones" wrote: Hi Allan, See J.E. McGimpsey's suggestions at: http://www.mcgimpsey.com/excel/udfs/sequentialnums.html --- Regards, Norman "Allan" wrote in message ... I wish to set up a Purchase Order system using Excel and i would like the PO number to automatically increment. Can anyone pleaseadvise me how to do this? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I increment a numbering system in excel
Hi Norman,
I am working with the VBA code that you've refered many people to and it kinda works. The date function works however the sequential numbering is not working. I've looked at the code over and over again and there are no errors according to the editor. I'm using Excel 2000 and trying to create my own invoice template. below is the code i'm using. Thank you Ken Sub INVOICE_NUMBER() Const sAPPLICATION As String = "Excel" Const sSECTION As String = "Invoice" Const sKEY As String = "Invoice_key" Const nDEFAULT As Long = 1& Dim nNumber As Long With ThisWorkbook.Sheets("Invoice") With .Range("E3") If IsEmpty(.Value) Then .Value = Date .NumberFormat = "dd mmm yyyy" End If End With With .Range("E4") If IsEmpty(.Value) Then nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT) .NumberFormat = "@" .Value = Format(nNumber, "0000") SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1& End If End With End With "Norman Jones" wrote: Hi Allan, If you are unfamiliar with macros, see the notes by JE McGimpsey on another page of his site: http://www.mcgimpsey.com/excel/index.html#usingvba Also see David McRitchie's 'Getting Started With Macros And User Defined Functions at: http://www.mvps.org/dmcritchie/excel/getstarted.htm If you are still experiencing problems, post back. --- Regards, Norman "Allan" wrote in message ... Hi Norman Sorry but I do not fully understand the macro. I have an Excel wookbook and in worksheet named "Sheet1" in cell I6 I wish to insert an new order number each time the workbook is opened that is the last PO number +1. I cannot see what to do with the link that you kindly sent to me. Can you help me further? Cheers Allan "Norman Jones" wrote: Hi Allan, See J.E. McGimpsey's suggestions at: http://www.mcgimpsey.com/excel/udfs/sequentialnums.html --- Regards, Norman "Allan" wrote in message ... I wish to set up a Purchase Order system using Excel and i would like the PO number to automatically increment. Can anyone pleaseadvise me how to do this? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I increment a numbering system in excel
You may want to be more specific about what "not working" means.
Fungusfreakland wrote: Hi Norman, I am working with the VBA code that you've refered many people to and it kinda works. The date function works however the sequential numbering is not working. I've looked at the code over and over again and there are no errors according to the editor. I'm using Excel 2000 and trying to create my own invoice template. below is the code i'm using. Thank you Ken Sub INVOICE_NUMBER() Const sAPPLICATION As String = "Excel" Const sSECTION As String = "Invoice" Const sKEY As String = "Invoice_key" Const nDEFAULT As Long = 1& Dim nNumber As Long With ThisWorkbook.Sheets("Invoice") With .Range("E3") If IsEmpty(.Value) Then .Value = Date .NumberFormat = "dd mmm yyyy" End If End With With .Range("E4") If IsEmpty(.Value) Then nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT) .NumberFormat = "@" .Value = Format(nNumber, "0000") SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1& End If End With End With "Norman Jones" wrote: Hi Allan, If you are unfamiliar with macros, see the notes by JE McGimpsey on another page of his site: http://www.mcgimpsey.com/excel/index.html#usingvba Also see David McRitchie's 'Getting Started With Macros And User Defined Functions at: http://www.mvps.org/dmcritchie/excel/getstarted.htm If you are still experiencing problems, post back. --- Regards, Norman "Allan" wrote in message ... Hi Norman Sorry but I do not fully understand the macro. I have an Excel wookbook and in worksheet named "Sheet1" in cell I6 I wish to insert an new order number each time the workbook is opened that is the last PO number +1. I cannot see what to do with the link that you kindly sent to me. Can you help me further? Cheers Allan "Norman Jones" wrote: Hi Allan, See J.E. McGimpsey's suggestions at: http://www.mcgimpsey.com/excel/udfs/sequentialnums.html --- Regards, Norman "Allan" wrote in message ... I wish to set up a Purchase Order system using Excel and i would like the PO number to automatically increment. Can anyone pleaseadvise me how to do this? -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I increment a numbering system in excel
Hi Dave,
All that comes up in cell E4 is a blank. is there something I did wrong with the coding? Thanks Ken "Fungusfreakland" wrote: Hi Norman, I am working with the VBA code that you've refered many people to and it kinda works. The date function works however the sequential numbering is not working. I've looked at the code over and over again and there are no errors according to the editor. I'm using Excel 2000 and trying to create my own invoice template. below is the code i'm using. Thank you Ken Sub INVOICE_NUMBER() Const sAPPLICATION As String = "Excel" Const sSECTION As String = "Invoice" Const sKEY As String = "Invoice_key" Const nDEFAULT As Long = 1& Dim nNumber As Long With ThisWorkbook.Sheets("Invoice") With .Range("E3") If IsEmpty(.Value) Then .Value = Date .NumberFormat = "dd mmm yyyy" End If End With With .Range("E4") If IsEmpty(.Value) Then nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT) .NumberFormat = "@" .Value = Format(nNumber, "0000") SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1& End If End With End With "Norman Jones" wrote: Hi Allan, If you are unfamiliar with macros, see the notes by JE McGimpsey on another page of his site: http://www.mcgimpsey.com/excel/index.html#usingvba Also see David McRitchie's 'Getting Started With Macros And User Defined Functions at: http://www.mvps.org/dmcritchie/excel/getstarted.htm If you are still experiencing problems, post back. --- Regards, Norman "Allan" wrote in message ... Hi Norman Sorry but I do not fully understand the macro. I have an Excel wookbook and in worksheet named "Sheet1" in cell I6 I wish to insert an new order number each time the workbook is opened that is the last PO number +1. I cannot see what to do with the link that you kindly sent to me. Can you help me further? Cheers Allan "Norman Jones" wrote: Hi Allan, See J.E. McGimpsey's suggestions at: http://www.mcgimpsey.com/excel/udfs/sequentialnums.html --- Regards, Norman "Allan" wrote in message ... I wish to set up a Purchase Order system using Excel and i would like the PO number to automatically increment. Can anyone pleaseadvise me how to do this? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I increment a numbering system in excel
The code worked fine for me.
You are looking at E4 of the Invoice worksheet, right? In JE's code, his procedure was named Workbook_Open (and that is located behind the ThisWorkbook module). That means that each time this workbook was opened, the code would run. If the cell was empty (B1 in JE's code, E4 in yours), then that cell will get the value of what's found in the registry plus 1. Since you renamed the procedure (and I assume that you moved it to a general module), how does your code get run? Do you have a button on the worksheet that runs the code? Fungusfreakland wrote: Hi Dave, All that comes up in cell E4 is a blank. is there something I did wrong with the coding? Thanks Ken "Fungusfreakland" wrote: Hi Norman, I am working with the VBA code that you've refered many people to and it kinda works. The date function works however the sequential numbering is not working. I've looked at the code over and over again and there are no errors according to the editor. I'm using Excel 2000 and trying to create my own invoice template. below is the code i'm using. Thank you Ken Sub INVOICE_NUMBER() Const sAPPLICATION As String = "Excel" Const sSECTION As String = "Invoice" Const sKEY As String = "Invoice_key" Const nDEFAULT As Long = 1& Dim nNumber As Long With ThisWorkbook.Sheets("Invoice") With .Range("E3") If IsEmpty(.Value) Then .Value = Date .NumberFormat = "dd mmm yyyy" End If End With With .Range("E4") If IsEmpty(.Value) Then nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY, nDEFAULT) .NumberFormat = "@" .Value = Format(nNumber, "0000") SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber + 1& End If End With End With "Norman Jones" wrote: Hi Allan, If you are unfamiliar with macros, see the notes by JE McGimpsey on another page of his site: http://www.mcgimpsey.com/excel/index.html#usingvba Also see David McRitchie's 'Getting Started With Macros And User Defined Functions at: http://www.mvps.org/dmcritchie/excel/getstarted.htm If you are still experiencing problems, post back. --- Regards, Norman "Allan" wrote in message ... Hi Norman Sorry but I do not fully understand the macro. I have an Excel wookbook and in worksheet named "Sheet1" in cell I6 I wish to insert an new order number each time the workbook is opened that is the last PO number +1. I cannot see what to do with the link that you kindly sent to me. Can you help me further? Cheers Allan "Norman Jones" wrote: Hi Allan, See J.E. McGimpsey's suggestions at: http://www.mcgimpsey.com/excel/udfs/sequentialnums.html --- Regards, Norman "Allan" wrote in message ... I wish to set up a Purchase Order system using Excel and i would like the PO number to automatically increment. Can anyone pleaseadvise me how to do this? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
How to write a macro to get the system time in excel 97 | Excel Discussion (Misc queries) | |||
Making A Databse system on Excel? | Excel Discussion (Misc queries) | |||
How do I input for an automatic increment in a cell on Excel? | Excel Worksheet Functions |