Have the first number of a serial in one cell and the last serial in another cell, and have a sheet fill in the rest?

[BAQI]

Example of what I'm looking for:

A2 = ABCDEF (an item that needs to be serialized)
B2 = 001 (the start serial)
C2 = 008 (the end serial)

A3 = UVWXYZ
B3 = 004
C3 = 011

Another sheet creates the following automatically:

A2 = ABCDEF
B2 = 001

A3 = ABCDEF
B3 = 002

A4 = ABCDEF
B4 = 003

.......

A9 = ABCDEF
B9 = 008

A10 = UVWXYZ
B10 = 004

A11 = UVWXYZ
B11 = 005

......

A17 = UVWXYZ
B17 = 011

I'm trying to keep one page clean, and save time by not having to do repetitive steps.

 

[kweaver]

I threw this VBA together. Will this do it?

Sub Serials()
Dim lr, i, j, k, m As Long
lr = Sheets("Serials").Cells(Rows.Count, "A").End(xlUp).Row
j = 2
For i = 2 To lr
 k = Sheets("Serials").Cells(i, 2)
 For m = k To k + Sheets("Serials").Cells(i, 3) - Sheets("Serials").Cells(i, 2)
  Sheets("NewSerials").Cells(j, 1) = Sheets("Serials").Cells(i, 1)
  Sheets("NewSerials").Cells(j, 2) = m   ' format if need leading zeros
  j = j + 1
 Next m
 k = k + 1
Next i
End Sub

 

[BAQI]

I threw this VBA together. Will this do it?

Sub Serials()
Dim lr, i, j, k, m As Long
lr = Sheets("Serials").Cells(Rows.Count, "A").End(xlUp).Row
j = 2
For i = 2 To lr
 k = Sheets("Serials").Cells(i, 2)
 For m = k To k + Sheets("Serials").Cells(i, 3) - Sheets("Serials").Cells(i, 2)
  Sheets("NewSerials").Cells(j, 1) = Sheets("Serials").Cells(i, 1)
  Sheets("NewSerials").Cells(j, 2) = m   ' format if need leading zeros
  j = j + 1
 Next m
 k = k + 1
Next i
End Sub

Thank you for doing this!

It almost worked. I do need the zeros in the serial - "001" "002" etc. When I run the macro without zeros in the serial, this is what I get:

And 2345 goes to "53" in column B

 

[kweaver]

Before the "End Sub" put this:

Sheets("NewSerials").Range("B:B").NumberFormat = "000"

 

[BAQI]

Before the "End Sub" put this:

Sheets("NewSerials").Range("B:B").NumberFormat = "000"

I'm hoping you can help me with one other issue I'm running into with this. I added this to the end of the sub. I'm then linking this sheet to Brother P-Touch Editor (to print labels). When linked, it isn't showing the "000" format. I believe that is because when you click on the cell itself, in the formula bar it is showing "1" instead of "001".

Is there a workaround for this?

 

[kweaver]

Try:

Sheets("NewSerials").Range("B:B").NumberFormat = "###"

 

[BAQI]

Try:

Sheets("NewSerials").Range("B:B").NumberFormat = "###"

### doesn't work.

 

[kweaver]

I'm hoping you can help me with one other issue I'm running into with this. I added this to the end of the sub. I'm then linking this sheet to Brother P-Touch Editor (to print labels). When linked, it isn't showing the "000" format. I believe that is because when you click on the cell itself, in the formula bar it is showing "1" instead of "001".

Is there a workaround for this?

Ah, true...the appearance in the cell is just "decoration" and the real value is a 1.
Are you doing a mail merge?

 

[kweaver]

You probably need to adjust the document that's being linked to Excel so that you have: MERGEFIELD serial \# 000

 

[BAQI]

You probably need to adjust the document that's being linked to Excel so that you have: MERGEFIELD serial \# 000

The software I'm using is p-touch editor. It's for printing labels.