generate consecutive numbers
Can I ask someone how I would go about getting the
results I want for the following points. 1. If I wanted to generate consecutive numbers when I hit a command button how would this be done? 2. How would I get the first number in cell A1 then A2 and so on? 3. If I want to start the numbers at 457. Thanks for any help in this matter. |
Hi Mark
how many consecutive numbers would you want to create at any one time ... on the second click on the command button would you want to replace the number in A2 with the next number after A1 or would you want to find the next blank line and put the next consecutive number in there? what do you want to happen if someone re-orders the column .. is the next number to be generated one more the physically last number in the column or one more than the maximum number in the column? Cheers JulieD "Mark" wrote in message ... Can I ask someone how I would go about getting the results I want for the following points. 1. If I wanted to generate consecutive numbers when I hit a command button how would this be done? 2. How would I get the first number in cell A1 then A2 and so on? 3. If I want to start the numbers at 457. Thanks for any help in this matter. |
Julied
This sheet is to be used for the whole year and we normally allocate numbers in a book from 1 to appox 550. On the second click of the command button I would want to find the next blank line and put the next consecutive number in there. This spreadsheet is to allocate a unique batch number for the year starting from 1. So at the start of the financial year I would allocate number 1 to the first batch the 2 to the second then three and so on. Thanks Mark -----Original Message----- Hi Mark how many consecutive numbers would you want to create at any one time ... on the second click on the command button would you want to replace the number in A2 with the next number after A1 or would you want to find the next blank line and put the next consecutive number in there? what do you want to happen if someone re-orders the column .. is the next number to be generated one more the physically last number in the column or one more than the maximum number in the column? Cheers JulieD "Mark" wrote in message ... Can I ask someone how I would go about getting the results I want for the following points. 1. If I wanted to generate consecutive numbers when I hit a command button how would this be done? 2. How would I get the first number in cell A1 then A2 and so on? 3. If I want to start the numbers at 457. Thanks for any help in this matter. . |
Hi Mark
here's some code for you --- Sub inc_nums() ActiveSheet.UsedRange If Range("A1").Value = "" Then Range("A1").Value = InputBox("Enter starting value", "Starting value", 1) ElseIf Not IsNumeric(Range("A1")) Then MsgBox "ERROR - The entry in cell A1 is not numeric", vbCritical + vbOKOnly, "Error" Else Range("A1").SpecialCells(xlCellTypeLastCell).Offse t(1, 0).Value = Application.WorksheetFunction.Max(Range("A:A")) + 1 End If End Sub ---- to use it, right mouse click on the sheet tab you want to use it on and choose view code. then from the VBE window choose insert / module and copy & paste the code above onto the right hand side of the screen ... if any lines go red it's probably due to line wrap problems, so click at the end of the previous line and press the delete key. then use alt & f11 to switch back to your workbook ... add a button (i use the drawing toolbar), right mouse click on it choose, assign macro and then choose the inc_nums macro. let me know how you get on Cheers JulieD "mark" wrote in message ... Julied This sheet is to be used for the whole year and we normally allocate numbers in a book from 1 to appox 550. On the second click of the command button I would want to find the next blank line and put the next consecutive number in there. This spreadsheet is to allocate a unique batch number for the year starting from 1. So at the start of the financial year I would allocate number 1 to the first batch the 2 to the second then three and so on. Thanks Mark -----Original Message----- Hi Mark how many consecutive numbers would you want to create at any one time ... on the second click on the command button would you want to replace the number in A2 with the next number after A1 or would you want to find the next blank line and put the next consecutive number in there? what do you want to happen if someone re-orders the column .. is the next number to be generated one more the physically last number in the column or one more than the maximum number in the column? Cheers JulieD "Mark" wrote in message ... Can I ask someone how I would go about getting the results I want for the following points. 1. If I wanted to generate consecutive numbers when I hit a command button how would this be done? 2. How would I get the first number in cell A1 then A2 and so on? 3. If I want to start the numbers at 457. Thanks for any help in this matter. . |
soz Julie but the follwoing lines appear in red
Range("A1").Value = InputBox("Enter starting value", "Starting MsgBox "ERROR - The entry in cell A1 is not numeric", vbCritical + Range("A1").SpecialCells(xlCellTypeLastCell).Offse t(1, 0).Value = and then the following error message appears. Can't execute code in break mode thanks -----Original Message----- Hi Mark here's some code for you --- Sub inc_nums() ActiveSheet.UsedRange If Range("A1").Value = "" Then Range("A1").Value = InputBox("Enter starting value", "Starting value", 1) ElseIf Not IsNumeric(Range("A1")) Then MsgBox "ERROR - The entry in cell A1 is not numeric", vbCritical + vbOKOnly, "Error" Else Range("A1").SpecialCells (xlCellTypeLastCell).Offset(1, 0).Value = Application.WorksheetFunction.Max(Range("A:A")) + 1 End If End Sub ---- to use it, right mouse click on the sheet tab you want to use it on and choose view code. then from the VBE window choose insert / module and copy & paste the code above onto the right hand side of the screen ... if any lines go red it's probably due to line wrap problems, so click at the end of the previous line and press the delete key. then use alt & f11 to switch back to your workbook ... add a button (i use the drawing toolbar), right mouse click on it choose, assign macro and then choose the inc_nums macro. let me know how you get on Cheers JulieD "mark" wrote in message ... Julied This sheet is to be used for the whole year and we normally allocate numbers in a book from 1 to appox 550. On the second click of the command button I would want to find the next blank line and put the next consecutive number in there. This spreadsheet is to allocate a unique batch number for the year starting from 1. So at the start of the financial year I would allocate number 1 to the first batch the 2 to the second then three and so on. Thanks Mark -----Original Message----- Hi Mark how many consecutive numbers would you want to create at any one time ... on the second click on the command button would you want to replace the number in A2 with the next number after A1 or would you want to find the next blank line and put the next consecutive number in there? what do you want to happen if someone re-orders the column .. is the next number to be generated one more the physically last number in the column or one more than the maximum number in the column? Cheers JulieD "Mark" wrote in message .. . Can I ask someone how I would go about getting the results I want for the following points. 1. If I wanted to generate consecutive numbers when I hit a command button how would this be done? 2. How would I get the first number in cell A1 then A2 and so on? 3. If I want to start the numbers at 457. Thanks for any help in this matter. . . |
I have it working now, thanks for all the info.
can i ask you one thing. i have three work sheets to run with this macro the first sheet is numeric however the next two start with the following letters before the number (Sheet 2 = RCA1 then RCA2)and (Sheet 3 = RCACR1 then RCACR2) can you help me please Mark -----Original Message----- soz Julie but the follwoing lines appear in red Range("A1").Value = InputBox("Enter starting value", "Starting MsgBox "ERROR - The entry in cell A1 is not numeric", vbCritical + Range("A1").SpecialCells(xlCellTypeLastCell).Offs et(1, 0).Value = and then the following error message appears. Can't execute code in break mode thanks -----Original Message----- Hi Mark here's some code for you --- Sub inc_nums() ActiveSheet.UsedRange If Range("A1").Value = "" Then Range("A1").Value = InputBox("Enter starting value", "Starting value", 1) ElseIf Not IsNumeric(Range("A1")) Then MsgBox "ERROR - The entry in cell A1 is not numeric", vbCritical + vbOKOnly, "Error" Else Range("A1").SpecialCells (xlCellTypeLastCell).Offset(1, 0).Value = Application.WorksheetFunction.Max(Range("A:A") ) + 1 End If End Sub ---- to use it, right mouse click on the sheet tab you want to use it on and choose view code. then from the VBE window choose insert / module and copy & paste the code above onto the right hand side of the screen ... if any lines go red it's probably due to line wrap problems, so click at the end of the previous line and press the delete key. then use alt & f11 to switch back to your workbook ... add a button (i use the drawing toolbar), right mouse click on it choose, assign macro and then choose the inc_nums macro. let me know how you get on Cheers JulieD "mark" wrote in message ... Julied This sheet is to be used for the whole year and we normally allocate numbers in a book from 1 to appox 550. On the second click of the command button I would want to find the next blank line and put the next consecutive number in there. This spreadsheet is to allocate a unique batch number for the year starting from 1. So at the start of the financial year I would allocate number 1 to the first batch the 2 to the second then three and so on. Thanks Mark -----Original Message----- Hi Mark how many consecutive numbers would you want to create at any one time ... on the second click on the command button would you want to replace the number in A2 with the next number after A1 or would you want to find the next blank line and put the next consecutive number in there? what do you want to happen if someone re-orders the column .. is the next number to be generated one more the physically last number in the column or one more than the maximum number in the column? Cheers JulieD "Mark" wrote in message . .. Can I ask someone how I would go about getting the results I want for the following points. 1. If I wanted to generate consecutive numbers when I hit a command button how would this be done? 2. How would I get the first number in cell A1 then A2 and so on? 3. If I want to start the numbers at 457. Thanks for any help in this matter. . . . |
Mark the easiest would be to use a helper column, where you generate the numbers (Say Column A). In the next column, Say B, enter the following formula ="RCA" & A1. For the next sheet you would enter ="RCACR" & A1. Fill down by putting your mouse cursor on the bottom right corner of Cell B1, clicking and dragging it doan as far as you want to go. I use a similar system to record orders. I do not use a macro for this. Instead, in Cell A2 I entered the following formula, and then filled it down =IF(A1="","",IF(C2="","",A1+1). In this way, whenever you enter any data into Column C, the number in A is updated. In you case you can then hide Column A, so that only Column B's Number (RCA) is shown "Mark" wrote: I have it working now, thanks for all the info. can i ask you one thing. i have three work sheets to run with this macro the first sheet is numeric however the next two start with the following letters before the number (Sheet 2 = RCA1 then RCA2)and (Sheet 3 = RCACR1 then RCACR2) can you help me please Mark -----Original Message----- soz Julie but the follwoing lines appear in red Range("A1").Value = InputBox("Enter starting value", "Starting MsgBox "ERROR - The entry in cell A1 is not numeric", vbCritical + Range("A1").SpecialCells(xlCellTypeLastCell).Offs et(1, 0).Value = and then the following error message appears. Can't execute code in break mode thanks -----Original Message----- Hi Mark here's some code for you --- Sub inc_nums() ActiveSheet.UsedRange If Range("A1").Value = "" Then Range("A1").Value = InputBox("Enter starting value", "Starting value", 1) ElseIf Not IsNumeric(Range("A1")) Then MsgBox "ERROR - The entry in cell A1 is not numeric", vbCritical + vbOKOnly, "Error" Else Range("A1").SpecialCells (xlCellTypeLastCell).Offset(1, 0).Value = Application.WorksheetFunction.Max(Range("A:A") ) + 1 End If End Sub ---- to use it, right mouse click on the sheet tab you want to use it on and choose view code. then from the VBE window choose insert / module and copy & paste the code above onto the right hand side of the screen ... if any lines go red it's probably due to line wrap problems, so click at the end of the previous line and press the delete key. then use alt & f11 to switch back to your workbook ... add a button (i use the drawing toolbar), right mouse click on it choose, assign macro and then choose the inc_nums macro. let me know how you get on Cheers JulieD "mark" wrote in message ... Julied This sheet is to be used for the whole year and we normally allocate numbers in a book from 1 to appox 550. On the second click of the command button I would want to find the next blank line and put the next consecutive number in there. This spreadsheet is to allocate a unique batch number for the year starting from 1. So at the start of the financial year I would allocate number 1 to the first batch the 2 to the second then three and so on. Thanks Mark -----Original Message----- Hi Mark how many consecutive numbers would you want to create at any one time ... on the second click on the command button would you want to replace the number in A2 with the next number after A1 or would you want to find the next blank line and put the next consecutive number in there? what do you want to happen if someone re-orders the column .. is the next number to be generated one more the physically last number in the column or one more than the maximum number in the column? Cheers JulieD "Mark" wrote in message . .. Can I ask someone how I would go about getting the results I want for the following points. 1. If I wanted to generate consecutive numbers when I hit a command button how would this be done? 2. How would I get the first number in cell A1 then A2 and so on? 3. If I want to start the numbers at 457. Thanks for any help in this matter. . . . |
All times are GMT +1. The time now is 03:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com