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.
.
.
.
|