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.

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

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" wrote:

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.

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

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" wrote:

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" wrote:

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.

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

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" wrote:

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" wrote:

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" wrote:

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.

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

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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.

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

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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.

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

You are the BEST, Joel! It works great.

Thank You



"Joel" wrote:


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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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.

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

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.

On Thursday, July 24, 2008 11:11 AM buscher7 wrote:

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.


On Thursday, July 24, 2008 11:32 AM Joe wrote:

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" wrote:


On Thursday, July 24, 2008 1:08 PM buscher7 wrote:

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" wrote:


On Thursday, July 24, 2008 5:09 PM Joe wrote:

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" wrote:


On Friday, July 25, 2008 9:23 AM buscher7 wrote:

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" wrote:


On Friday, July 25, 2008 11:07 AM Joe wrote:

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" wrote:


On Friday, July 25, 2008 12:44 PM buscher7 wrote:

You are the BEST, Joel! It works great.

Thank You



"Joel" wrote:


Submitted via EggHeadCafe
Microsoft SQL Server Developer For Beginners
http://www.eggheadcafe.com/training-...QL-Server.aspx

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.

On 12/29/2010 10:14, John Stairs wrote:
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.

On Thursday, July 24, 2008 11:11 AM buscher7 wrote:

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.


On Thursday, July 24, 2008 11:32 AM Joe wrote:

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" wrote:


On Thursday, July 24, 2008 1:08 PM buscher7 wrote:

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" wrote:


On Thursday, July 24, 2008 5:09 PM Joe wrote:

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" wrote:


On Friday, July 25, 2008 9:23 AM buscher7 wrote:

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" wrote:


On Friday, July 25, 2008 11:07 AM Joe wrote:

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" wrote:


On Friday, July 25, 2008 12:44 PM buscher7 wrote:

You are the BEST, Joel! It works great.

Thank You



"Joel" wrote:


Submitted via EggHeadCafe
Microsoft SQL Server Developer For Beginners
http://www.eggheadcafe.com/training-...QL-Server.aspx

--
Dave Peterson

Make that space then underscore.

Range("C"& RowCount) =<spaceunderscore


Merry Xmas, Gord

On Wed, 29 Dec 2010 10:37:03 -0600, Dave Peterson
wrote:

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