(semi) Automatic Sequential numbers - 001-01 - 100-10

[elcentro3m]

Series beginning 001 ending 100

and in sequence as follows:

001-01
001-02
001-03
001-04
001-05
001-06
001-07
001-08
001-09
001-10

002-01
002-02
002-03
002-04
002-05
002-06
002-07
002-08
002-09
002-10

up to

100-01
100-02
100-03
100-04
100-05
100-06
100-07
100-08
100-09
100-10

I'm thinking there's got to be a way to
automate or semi-automate this process
rather than typing them all in manually.

Anyone?

Thanx

 

[Norie]

Try this.

Private Sub NumSeq()
Dim rng As Range
    Set rng = Range("A1")
    For I = 1 To 100
        rng = Format(I, "000") & "-01"
        rng.AutoFill rng.Resize(10)
        Set rng = rng.Offset(10)
    Next I
End Sub

 

[njimack]

In A1, enter 001-01
In A2, enter =LEFT(A1,4)&TEXT(RIGHT(A1,2)+1,"00")
In A3, enter =IF(RIGHT(A2)="0","",IF(A2="",TEXT(LEFT(A1,3)+1,"000")&"-01",LEFT(A2,4)&TEXT(RIGHT(A2,2)+1,"00")))
Copy A3 down to A1099

Book11
	A	B	C	D
1	001-01
2	001-02
3	001-03
4	001-04
5	001-05
6	001-06
7	001-07
8	001-08
9	001-09
10	001-10
11
12	002-01
13	002-02
14	002-03
Sheet1

 

[elcentro3m]

Great!!!

Both work.

I had to do only a little digging
to figure out how to run the code:

Change the Private to Public by
removing the word "Private"
from the code, then, run it
like any other Macro from
Tools / Macro.

Source:

Excel: run private object (AllExperts.com)

Thanx.