Serial Number start and end range auto-fill into cells

[buscher75]

This is my goal, to auto-fill serial number RED0001 through serial number
RED0100, for example. I would like to be able to add the prefix, RED, in one
cell and the start and end number in thier own cells also. These serial
numbers are converted to barcodes, so somewhere I will need to add an *
asterisk on both sides of the serial number.

I’m not sure the best way to approach this. I would need to fill the cells
left to right (3 columns), top to bottom for printing purposes. Any ideas
would be greatly appreciated.

 

[Joel]

Put Prefix in cell A1, Start Serial Number in B1, end Serial Number in C1.
The code below will create the data you wnat starting in Row 3. Column A is
the Prefix, column B is the Serial Number, and column C is the Bar code.

Sub CreateNumbers()

Prefix = Range("A1")
StartNumber = Range("B1")
EndNumber = Range("C1")

RowCount = 3
For Count = StartNumber To EndNumber
Range("A" & RowCount) = Prefix
Range("B" & RowCount) = Count
Range("C" & RowCount) = _
"*" & Prefix & Format(Count, "#000") & "*"

RowCount = RowCount + 1
Next Count



End Sub

 

[buscher75]

I tried your code and it worked. THANK YOU However, I have two questions
for you.
1. My serial number RED0001 comes out as RED1, it drops the zeros. Is
there a way to prevent this? Whether it is RED0001 or RED000001, I need to
keep the zeros.

2. The VB code returns the completed serial number in one column. Is there
a way to fill into 3 columns? Starting in cell D1, then E1, then F1 and back
over to D2, E2, F2 and repeat until we get to the last number? This would
help in utilizing the full page for printing.

I appreciate you time.

 

[Joel]

The zeroes in the following line determines the number of zeroes in the
results shown in my original code in column C. Adding more zeroes will
increase the number of digits in the results. The line below made the
results 3 digits.

Format(Count, "#000")


Can you give me a couple of lines of how you want your results to appear.
My code put the serial number in column B. If you put the bar code in D, E ,
and F then where do you want the corresponding serial numbers to go?

 

[buscher75]

Hi Joe, I hope I'm understanding your question correcty. My plan is to
format the cells of your output, column C, to a barcode font. This is what's
current on my excel sheet, after the macro runs:

A1 = RED, B1 = 0001, C1 = 0010
The output start in Cell A3

A B C
3 RED 0001 *RED0001*
4 RED 0002 *RED0002*
ect.....

From what we have created so far, I would like to move the output from
column C to the following. The visual output of column A and B does not
matter. The user only need to be able to print the created serial number
starting in cell D1-

D E F
1 *RED0001* *RED0002* *RED0003*
2 *RED0004* *RED0005* *RED0006*
3 *RED0007* *RED0008* *RED0009*
4 *RED0010*

I appreciate you help.

 

[Joel]

Sub CreateNumbers()

Prefix = Range("A1")
StartNumber = Range("B1")
EndNumber = Range("C1")

RowCount = 3
ColCount = 4
For Count = StartNumber To EndNumber
If ColCount = 4 Then
Range("A" & RowCount) = Prefix
Range("B" & RowCount) = Count
Range("C" & RowCount) = _
"*" & Prefix & Format(Count, "#0000") & "*"
End If

Cells(RowCount, ColCount) = _
"*" & Prefix & Format(Count, "#0000") & "*"

If ColCount = 6 Then
RowCount = RowCount + 1
ColCount = 4
Else
ColCount = ColCount + 1
End If
Next Count



End Sub

 

[buscher75]

You are the BEST, Joel! It works great.

Thank You

 

[John Stairs]

This is very similar to something I would like to do as well, but when i use your code I get a " Compile Error: Syntax Error" on the "Range("C" & RowCount) = _" line. I am using Excel 2007. Would that make a difference? Or am I missing something here?

Thanking you in advance.

 

[Dave Peterson]

The physical line should actually end with an underscore followed by a space
character.

It's the symbol that VBA uses for a continuation character(s).

Since there doesn't look like there's anything wrong with that line, my guess is
that the error on the next line.

Did you really continue that line with more code (on the next physical line)?
Or did you put an empty line right after this line?

If you have trouble, you should share that portion of the code.

 

[Gord Dibben]

Make that space then underscore.

Range("C"& RowCount) =<space>underscore


Merry Xmas, Gord