Re: generate consecutive numbers

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: JulieD (JulieD_at_hctsReMoVeThIs.net.au)
Date: 03/08/05 

Date: Wed, 9 Mar 2005 00:59:46 +0800

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" <anonymous@discussions.microsoft.com> wrote in message 
news:57a801c523fd$a370e4b0$a601280a@phx.gbl...
> 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" <anonymous@discussions.microsoft.com> wrote in
> message
>>news:446001c523fa$784dfbe0$a501280a@phx.gbl...
>>> 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.
>>>
>>
>>
>>.
>>

Relevant Pages

  • No one. believes a word you say anymore Mark ;-)
    ... Considering I have been using WSR for two years now ALL the time. ... Now granted I have only been using Dragon Pro since November last year, but with expert advice and getting my head down to learn the commands, I am quite proficient with this now. ... Catch up Mark for gawd's sake. ... In that instance, Dragon is a toy, that's why your company felt the need to pinch the show numbers command from WSR;-). ...
    (microsoft.public.windows.vista.general)
  • Re: [PATCH] [USB] UAS: Achitecture; TMF; more
    ... Command delivery to the service delivery subsystem should ... First allocate the urbs we'll need up ... into the driver are NOT tagged and some are tagged. ... Tag ID or stream ID is the same, ...
    (Linux-Kernel)
  • Re: despair
    ... %SYSTEM-F-DEVMOUNT, device is already mounted ... It's a command procedure and should be written ... I'm talking about "someone left the wrong tape in" or "someone else is ... You allocate a resource, you ...
    (comp.os.vms)
  • Re: In vi/vim delete lines
    ... I frequently delete to a mark. ... | A buffer containing lines is put only once, ... | If the last command before a `.' ... The basic meta-characters for the replacement pattern are `&' and `~'; ...
    (comp.unix.shell)
  • Re: Populat a NewRecord from Text boxes Revisited
    ... earlier post were a little wide of the mark. ... The Form has two text boxes ... I used a command button (the command button wizard, record operations,Add ... field (fldName in my example). ...
    (comp.databases.ms-access)