Macro to re-number data

[Little Penny]

I have a spread sheet that could be a few hundred rows or 50000 plus
row of data. In the first column is a 38 character (letters and
numbers) data string. But in that string there is a six digit sequence
number. The numbers always run consecutively. The first cell A1 could
start a 000001 or 014235 or any six digit number including leading
zeros. The sequence number starts at character 9 - 14. I'm trying to
create a macro that will resequence the characters (9-14) staring from
a sequence number I chose. For example"

The first sequene number below is 010926 thru 010932
A
1 ABCDASAY01092600040004000000000000XXXX
2 ABCDASAY01092700040004000000000000XXXX
3 ABCDASAY01092800040004000000000000XXXX
4 ABCDASAY01092900040004000000000000XXXX
5 ABCDASAY01093000040004000000000000XXXX
6 ABCDASAY01093100040004000000000000XXXX
7 ABCDASAY01093200040004000000000000XXXX


f I wanted to resequence column at starting at 004421 the macro would
return this



A
1 ABCDASAY00442100040004000000000000XXXX
2 ABCDASAY00442200040004000000000000XXXX
3 ABCDASAY00442300040004000000000000XXXX
4 ABCDASAY00442400040004000000000000XXXX
5 ABCDASAY00442500040004000000000000XXXX
6 ABCDASAY00442600040004000000000000XXXX
7 ABCDASAY00442700040004000000000000XXXX





Thanks

 

[Dave Peterson]

Maybe you could use a formula like:
="ABCDASAY"&text(row()+1,"000000")&"00040004000000000000XXXX"
(starting in row 1)

or
=left(a1,8)&text(row()+1,"000000")&mid(a1,15,255)
in B1

Then drag the formula down as far as you need.
And then convert the formulas to values (edit|Copy, edit|Paste special|values).

 

[Guest]

That did Joel.


Thank you Thank you Thank you.


Sub resequence()
Dim SequenceCount
Dim RowCount
Dim Prefix
Dim Suffix
Dim SequenceString

SequenceCount = _
Val(InputBox("Enter Start Sequence Number"))
RowCount = 1
Do While Cells(RowCount, "A") <> ""
Prefix = Left(Cells(RowCount, "A").Value, 8)
Suffix = Mid(Cells(RowCount, "A").Value, 15)
SequenceString = _
Format(SequenceCount, "0#####")
Cells(RowCount, "B") = Prefix & _
SequenceString & Suffix
SequenceCount = SequenceCount + 1
RowCount = RowCount + 1
Loop
End Sub

 

[Little Penny]

Thanks again Joel

That did it................


One day I hope i'm as good as you with this stuff.

But i'm learning


Thanks