generate consecutive numbers

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

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
 
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
 
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
 
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).Offset(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
Click to expand...
(xlCellTypeLastCell).Offset(1, 0).Value =
 
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
 
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
 
Back
Top