I'm trying to create a code that will produce the following results.

First the user will need to provide basic information.
Job quantity
First Piece
Group size
Job Number


The "Group Size" breaks the "Job Quantity" up into group size chucks.
For example if the "Job Quantity" is 14524 and the "Group Size" is
3000 we would get four groups of 3000 and one group of 2524


Example 1



Job Quantity = 14524
First Piece = 1
Group Size = 3000
Job Number = A12345


Code will produce:

A B C D E
GroupID START END QTY Group BAR CODE
S0001 1 3000 3000 *S1001+1+3000+A12345*
S0002 3001 6000 3000 *S1002+3001+3000+A12345*
S0003 6001 9000 3000 *S1003+6001+3000+A12345*
S0004 9001 12000 3000 *S1004+9001+3000+A12345*
S0005 12001 14524 2524 *S1005+12001+2524+A12345*


As you can see a "*" is added at the beginning and end in column E.
Also everything is separated by a "+" sign. This format is import
because it will be read by a scanner.



Example 2

In this example the first piece 2541



Job Quantity = 18233
First Piece = 2541
Group Size = 2500
Job Number = A12345


Code will produce:

A B C D E
GroupID START END QTY Group BAR CODE
S0001 2541 5040 2500 *S1001+2541+2500+B12345*
S0002 5041 7540 2500 *S1002+5041+2500+B12345*
S0003 7541 10040 2500 *S1003+7541+2500+B12345*
S0004 10041 12540 2500 *S1004+10041+2500+B12345*
S0005 12541 15040 2500 *S1005+12541+2500+B12345*
S0006 15041 17540 2500 *S1006+15041+2500+B12345*
S0007 17541 20040 2500 *S1007+17541+2500+B12345*
S0008 20041 20773 733 *S1008+20041+733+B12345*





Column E which is the Group Bar code should use the bar code font
"CarolinaBar-B39-2.5-22x158x720"

I hope I explained the right.


Where do I start?

Hi Penny. I will be glad to help like in the past. I found the website on
Carolina Bar code the linnk is below.

http://carolinabarcode.com

I downloaded the excel example which gives the method for printing the bar
codes and generating the bar code with the required CRC. I've worked with
Bar codes befroe so I have a good understainding of how to generte the codes.
MY Electrical Engineering and math background has given plenty of classroom
work to be pretty much an expert on generating codes.

I'm not usre from your description if you are having problems figuring out
how to generate the codes of just combining columns A - D into E. Column E
is just a cobination of the field A-D.

I used the download function from the Carolina Website and used this formula
to get your results

Cell E1
=bc3of9(A1&B1&D1&"B12345")

Cell F1 - add the plus signs into string
=LEFT(E1,6)&"+"&MID(E1,7,4)&"+"&MID(E1,11,4)&"+"&R IGHT(E1,7)

The function BC3of9 converts the character to proper format and places the *
at the beginning and end. The Plus signs are not part of the Bar code and
are added only to make it easier to read the numbers.

"Little Penny" wrote:

I'm trying to create a code that will produce the following results.

First the user will need to provide basic information.
Job quantity
First Piece
Group size
Job Number


The "Group Size" breaks the "Job Quantity" up into group size chucks.
For example if the "Job Quantity" is 14524 and the "Group Size" is
3000 we would get four groups of 3000 and one group of 2524


Example 1



Job Quantity = 14524
First Piece = 1
Group Size = 3000
Job Number = A12345


Code will produce:

A B C D E
GroupID START END QTY Group BAR CODE
S0001 1 3000 3000 *S1001+1+3000+A12345*
S0002 3001 6000 3000 *S1002+3001+3000+A12345*
S0003 6001 9000 3000 *S1003+6001+3000+A12345*
S0004 9001 12000 3000 *S1004+9001+3000+A12345*
S0005 12001 14524 2524 *S1005+12001+2524+A12345*


As you can see a "*" is added at the beginning and end in column E.
Also everything is separated by a "+" sign. This format is import
because it will be read by a scanner.



Example 2

In this example the first piece 2541



Job Quantity = 18233
First Piece = 2541
Group Size = 2500
Job Number = A12345


Code will produce:

A B C D E
GroupID START END QTY Group BAR CODE
S0001 2541 5040 2500 *S1001+2541+2500+B12345*
S0002 5041 7540 2500 *S1002+5041+2500+B12345*
S0003 7541 10040 2500 *S1003+7541+2500+B12345*
S0004 10041 12540 2500 *S1004+10041+2500+B12345*
S0005 12541 15040 2500 *S1005+12541+2500+B12345*
S0006 15041 17540 2500 *S1006+15041+2500+B12345*
S0007 17541 20040 2500 *S1007+17541+2500+B12345*
S0008 20041 20773 733 *S1008+20041+733+B12345*





Column E which is the Group Bar code should use the bar code font
"CarolinaBar-B39-2.5-22x158x720"

I hope I explained the right.


Where do I start?

Hi Joel

Thanks for you replay

I have a spreadsheet that does what I have described. What I was
trying to do is recreate what it does using VBA code. It gives much
more flexibility for other things down the road. I can email you a
sample of my spreadsheet if you like. But my main focus was to
recreate what my spreadsheet formulasdo using VBA

I will start by asking the user


Ask1:
jnumber = Application.InputBox("Job Number", Title:="Job Number",
Type:=1)
If jnumber = False Then

End If

Ask2:
fpiece = Application.InputBox("Enter first piece", Title:="First
Piece", Type:=1)
If fpiece = False Then
End If

Ask3:
Jquanity = Application.InputBox("Enter Job quanity", Title:="Job
Quanity", Type:=1)
If Jquanity = False Then
End If


Ask4:
gsize = Application.InputBox("Enter group quanity", Title:="Group
Quanity", Type:=1)
If gsize = False Then

End If

Can I email you my spreadsheet?




















On Sat, 29 Sep 2007 12:16:00 -0700, Joel
wrote:

Hi Penny. I will be glad to help like in the past. I found the website on
Carolina Bar code the linnk is below.

http://carolinabarcode.com

I downloaded the excel example which gives the method for printing the bar
codes and generating the bar code with the required CRC. I've worked with
Bar codes befroe so I have a good understainding of how to generte the codes.
MY Electrical Engineering and math background has given plenty of classroom
work to be pretty much an expert on generating codes.

I'm not usre from your description if you are having problems figuring out
how to generate the codes of just combining columns A - D into E. Column E
is just a cobination of the field A-D.

I used the download function from the Carolina Website and used this formula
to get your results

Cell E1
=bc3of9(A1&B1&D1&"B12345")

Cell F1 - add the plus signs into string
=LEFT(E1,6)&"+"&MID(E1,7,4)&"+"&MID(E1,11,4)&"+"& RIGHT(E1,7)

The function BC3of9 converts the character to proper format and places the *
at the beginning and end. The Plus signs are not part of the Bar code and
are added only to make it easier to read the numbers.

"Little Penny" wrote:

I'm trying to create a code that will produce the following results.

First the user will need to provide basic information.
Job quantity
First Piece
Group size
Job Number


The "Group Size" breaks the "Job Quantity" up into group size chucks.
For example if the "Job Quantity" is 14524 and the "Group Size" is
3000 we would get four groups of 3000 and one group of 2524


Example 1



Job Quantity = 14524
First Piece = 1
Group Size = 3000
Job Number = A12345


Code will produce:

A B C D E
GroupID START END QTY Group BAR CODE
S0001 1 3000 3000 *S1001+1+3000+A12345*
S0002 3001 6000 3000 *S1002+3001+3000+A12345*
S0003 6001 9000 3000 *S1003+6001+3000+A12345*
S0004 9001 12000 3000 *S1004+9001+3000+A12345*
S0005 12001 14524 2524 *S1005+12001+2524+A12345*


As you can see a "*" is added at the beginning and end in column E.
Also everything is separated by a "+" sign. This format is import
because it will be read by a scanner.



Example 2

In this example the first piece 2541



Job Quantity = 18233
First Piece = 2541
Group Size = 2500
Job Number = A12345


Code will produce:

A B C D E
GroupID START END QTY Group BAR CODE
S0001 2541 5040 2500 *S1001+2541+2500+B12345*
S0002 5041 7540 2500 *S1002+5041+2500+B12345*
S0003 7541 10040 2500 *S1003+7541+2500+B12345*
S0004 10041 12540 2500 *S1004+10041+2500+B12345*
S0005 12541 15040 2500 *S1005+12541+2500+B12345*
S0006 15041 17540 2500 *S1006+15041+2500+B12345*
S0007 17541 20040 2500 *S1007+17541+2500+B12345*
S0008 20041 20773 733 *S1008+20041+733+B12345*





Column E which is the Group Bar code should use the bar code font
"CarolinaBar-B39-2.5-22x158x720"

I hope I explained the right.


Where do I start?

hi
this work on both of your examples
I assumed that all your job would be in the same columns and that you would
be just adding to them.

Sub Macro1()
Dim jq As Long 'job qty
Dim jqm As Long 'job qty minus
Dim jqbr As Long 'job qty run balance
Dim fp As Long 'first piece
Dim gs As Long 'group size
Dim jn As String 'job number
Dim c As Long 'counter
Dim sp As Range 'sheet placement
c = 1

jq = InputBox("Enter the Job quanty.")
fp = InputBox("Enter the first piece quanty.")
gs = InputBox("Enter the qroup size.")
jn = InputBox("Enter the Job Number.")
jq = jq + fp - 1
jqm = jq

Set sp = Cells(65536, 1).End(xlUp).Offset(1, 0)
sp.Select
sp.Value = "S000" & c
c = c + 1
sp.Offset(0, 1).Value = fp
jqrb = jqrb + gs + fp - 1
sp.Offset(0, 2).Value = jqrb
sp.Offset(0, 3).Value = gs
sp.Offset(0, 4).Value = "*" & sp.Value & "+" & _
sp.Offset(0, 1).Value & "+" & _
sp.Offset(0, 3).Value & "+" & jn & "*"
Do While jqm 0
Set sp = Cells(65536, 1).End(xlUp).Offset(1, 0)
sp.Select
sp.Value = "S000" & c
c = c + 1
fp = fp + gs
sp.Offset(0, 1).Value = fp
If jq - jqrb < gs Then
gs = jq - jqrb
jqrb = jqrb + gs
jqm = jqm = gs
Else
jqrb = jqrb + gs
jqm = jqm - gs
End If
sp.Offset(0, 2).Value = jqrb
sp.Offset(0, 3).Value = gs
sp.Offset(0, 4).Value = "*" & sp.Value & "+" & _
sp.Offset(0, 1).Value & "+" & _
sp.Offset(0, 3).Value & "+" & jn & "*"
Loop

End Sub

regards
FSt1

"Little Penny" wrote:

I'm trying to create a code that will produce the following results.

First the user will need to provide basic information.
Job quantity
First Piece
Group size
Job Number


The "Group Size" breaks the "Job Quantity" up into group size chucks.
For example if the "Job Quantity" is 14524 and the "Group Size" is
3000 we would get four groups of 3000 and one group of 2524


Example 1



Job Quantity = 14524
First Piece = 1
Group Size = 3000
Job Number = A12345


Code will produce:

A B C D E
GroupID START END QTY Group BAR CODE
S0001 1 3000 3000 *S1001+1+3000+A12345*
S0002 3001 6000 3000 *S1002+3001+3000+A12345*
S0003 6001 9000 3000 *S1003+6001+3000+A12345*
S0004 9001 12000 3000 *S1004+9001+3000+A12345*
S0005 12001 14524 2524 *S1005+12001+2524+A12345*


As you can see a "*" is added at the beginning and end in column E.
Also everything is separated by a "+" sign. This format is import
because it will be read by a scanner.



Example 2

In this example the first piece 2541



Job Quantity = 18233
First Piece = 2541
Group Size = 2500
Job Number = A12345


Code will produce:

A B C D E
GroupID START END QTY Group BAR CODE
S0001 2541 5040 2500 *S1001+2541+2500+B12345*
S0002 5041 7540 2500 *S1002+5041+2500+B12345*
S0003 7541 10040 2500 *S1003+7541+2500+B12345*
S0004 10041 12540 2500 *S1004+10041+2500+B12345*
S0005 12541 15040 2500 *S1005+12541+2500+B12345*
S0006 15041 17540 2500 *S1006+15041+2500+B12345*
S0007 17541 20040 2500 *S1007+17541+2500+B12345*
S0008 20041 20773 733 *S1008+20041+733+B12345*





Column E which is the Group Bar code should use the bar code font
"CarolinaBar-B39-2.5-22x158x720"

I hope I explained the right.


Where do I start?

All I can say is WOWWWWWWWWWWWWWWWWWWWWWWW

N F-ing Credible

Thanks

I have just one request the Group ID increase as such...


OLD New
S0001 S0001
S0002 S0002
S0003 S0003
S0004 S0004
S0005 S0005
S0006 S0006
S0007 S0007
S0008 S0008
S0009 S0009
S00010 S0010
S00011 S0011
S00012 S0012
S00013 S0013
S00014 S0014
S00015 S0015

Instead of adding a character it moves to the left.









On Sat, 29 Sep 2007 13:39:02 -0700, FSt1
wrote:

hi
this work on both of your examples
I assumed that all your job would be in the same columns and that you would
be just adding to them.

Sub Macro1()
Dim jq As Long 'job qty
Dim jqm As Long 'job qty minus
Dim jqbr As Long 'job qty run balance
Dim fp As Long 'first piece
Dim gs As Long 'group size
Dim jn As String 'job number
Dim c As Long 'counter
Dim sp As Range 'sheet placement
c = 1

jq = InputBox("Enter the Job quanty.")
fp = InputBox("Enter the first piece quanty.")
gs = InputBox("Enter the qroup size.")
jn = InputBox("Enter the Job Number.")
jq = jq + fp - 1
jqm = jq

Set sp = Cells(65536, 1).End(xlUp).Offset(1, 0)
sp.Select
sp.Value = "S000" & c
c = c + 1
sp.Offset(0, 1).Value = fp
jqrb = jqrb + gs + fp - 1
sp.Offset(0, 2).Value = jqrb
sp.Offset(0, 3).Value = gs
sp.Offset(0, 4).Value = "*" & sp.Value & "+" & _
sp.Offset(0, 1).Value & "+" & _
sp.Offset(0, 3).Value & "+" & jn & "*"
Do While jqm 0
Set sp = Cells(65536, 1).End(xlUp).Offset(1, 0)
sp.Select
sp.Value = "S000" & c
c = c + 1
fp = fp + gs
sp.Offset(0, 1).Value = fp
If jq - jqrb < gs Then
gs = jq - jqrb
jqrb = jqrb + gs
jqm = jqm = gs
Else
jqrb = jqrb + gs
jqm = jqm - gs
End If
sp.Offset(0, 2).Value = jqrb
sp.Offset(0, 3).Value = gs
sp.Offset(0, 4).Value = "*" & sp.Value & "+" & _
sp.Offset(0, 1).Value & "+" & _
sp.Offset(0, 3).Value & "+" & jn & "*"
Loop

End Sub

regards
FSt1

"Little Penny" wrote:

I'm trying to create a code that will produce the following results.

First the user will need to provide basic information.
Job quantity
First Piece
Group size
Job Number


The "Group Size" breaks the "Job Quantity" up into group size chucks.
For example if the "Job Quantity" is 14524 and the "Group Size" is
3000 we would get four groups of 3000 and one group of 2524


Example 1



Job Quantity = 14524
First Piece = 1
Group Size = 3000
Job Number = A12345


Code will produce:

A B C D E
GroupID START END QTY Group BAR CODE
S0001 1 3000 3000 *S1001+1+3000+A12345*
S0002 3001 6000 3000 *S1002+3001+3000+A12345*
S0003 6001 9000 3000 *S1003+6001+3000+A12345*
S0004 9001 12000 3000 *S1004+9001+3000+A12345*
S0005 12001 14524 2524 *S1005+12001+2524+A12345*


As you can see a "*" is added at the beginning and end in column E.
Also everything is separated by a "+" sign. This format is import
because it will be read by a scanner.



Example 2

In this example the first piece 2541



Job Quantity = 18233
First Piece = 2541
Group Size = 2500
Job Number = A12345


Code will produce:

A B C D E
GroupID START END QTY Group BAR CODE
S0001 2541 5040 2500 *S1001+2541+2500+B12345*
S0002 5041 7540 2500 *S1002+5041+2500+B12345*
S0003 7541 10040 2500 *S1003+7541+2500+B12345*
S0004 10041 12540 2500 *S1004+10041+2500+B12345*
S0005 12541 15040 2500 *S1005+12541+2500+B12345*
S0006 15041 17540 2500 *S1006+15041+2500+B12345*
S0007 17541 20040 2500 *S1007+17541+2500+B12345*
S0008 20041 20773 733 *S1008+20041+733+B12345*





Column E which is the Group Bar code should use the bar code font
"CarolinaBar-B39-2.5-22x158x720"

I hope I explained the right.


Where do I start?

hi again,
oops. didn't think of that.
read throught the code. 2 PLACES. a line that says....

sp.Value = "S000" & c

replace that line with this....

If Len("S000" & c) 5 Then
sp.Value = "S00" & c
Else
sp.Value = "S000" & c
End If

that should keep your group ids to 5 characters.

Regards
FSt1

"Little Penny" wrote:

All I can say is WOWWWWWWWWWWWWWWWWWWWWWWW

N F-ing Credible

Thanks

I have just one request the Group ID increase as such...


OLD New
S0001 S0001
S0002 S0002
S0003 S0003
S0004 S0004
S0005 S0005
S0006 S0006
S0007 S0007
S0008 S0008
S0009 S0009
S00010 S0010
S00011 S0011
S00012 S0012
S00013 S0013
S00014 S0014
S00015 S0015

Instead of adding a character it moves to the left.









On Sat, 29 Sep 2007 13:39:02 -0700, FSt1
wrote:

hi
this work on both of your examples
I assumed that all your job would be in the same columns and that you would
be just adding to them.

Sub Macro1()
Dim jq As Long 'job qty
Dim jqm As Long 'job qty minus
Dim jqbr As Long 'job qty run balance
Dim fp As Long 'first piece
Dim gs As Long 'group size
Dim jn As String 'job number
Dim c As Long 'counter
Dim sp As Range 'sheet placement
c = 1

jq = InputBox("Enter the Job quanty.")
fp = InputBox("Enter the first piece quanty.")
gs = InputBox("Enter the qroup size.")
jn = InputBox("Enter the Job Number.")
jq = jq + fp - 1
jqm = jq

Set sp = Cells(65536, 1).End(xlUp).Offset(1, 0)
sp.Select
sp.Value = "S000" & c
c = c + 1
sp.Offset(0, 1).Value = fp
jqrb = jqrb + gs + fp - 1
sp.Offset(0, 2).Value = jqrb
sp.Offset(0, 3).Value = gs
sp.Offset(0, 4).Value = "*" & sp.Value & "+" & _
sp.Offset(0, 1).Value & "+" & _
sp.Offset(0, 3).Value & "+" & jn & "*"
Do While jqm 0
Set sp = Cells(65536, 1).End(xlUp).Offset(1, 0)
sp.Select
sp.Value = "S000" & c
c = c + 1
fp = fp + gs
sp.Offset(0, 1).Value = fp
If jq - jqrb < gs Then
gs = jq - jqrb
jqrb = jqrb + gs
jqm = jqm + gs
Else
jqrb = jqrb + gs
jqm = jqm - gs
End If
sp.Offset(0, 2).Value = jqrb
sp.Offset(0, 3).Value = gs
sp.Offset(0, 4).Value = "*" & sp.Value & "+" & _
sp.Offset(0, 1).Value & "+" & _
sp.Offset(0, 3).Value & "+" & jn & "*"
Loop

End Sub

regards
FSt1

"Little Penny" wrote:

I'm trying to create a code that will produce the following results.

First the user will need to provide basic information.
Job quantity
First Piece
Group size
Job Number


The "Group Size" breaks the "Job Quantity" up into group size chucks.
For example if the "Job Quantity" is 14524 and the "Group Size" is
3000 we would get four groups of 3000 and one group of 2524


Example 1



Job Quantity = 14524
First Piece = 1
Group Size = 3000
Job Number = A12345


Code will produce:

A B C D E
GroupID START END QTY Group BAR CODE
S0001 1 3000 3000 *S1001+1+3000+A12345*
S0002 3001 6000 3000 *S1002+3001+3000+A12345*
S0003 6001 9000 3000 *S1003+6001+3000+A12345*
S0004 9001 12000 3000 *S1004+9001+3000+A12345*
S0005 12001 14524 2524 *S1005+12001+2524+A12345*


As you can see a "*" is added at the beginning and end in column E.
Also everything is separated by a "+" sign. This format is import
because it will be read by a scanner.



Example 2

In this example the first piece 2541



Job Quantity = 18233
First Piece = 2541
Group Size = 2500
Job Number = A12345


Code will produce:

A B C D E
GroupID START END QTY Group BAR CODE
S0001 2541 5040 2500 *S1001+2541+2500+B12345*
S0002 5041 7540 2500 *S1002+5041+2500+B12345*
S0003 7541 10040 2500 *S1003+7541+2500+B12345*
S0004 10041 12540 2500 *S1004+10041+2500+B12345*
S0005 12541 15040 2500 *S1005+12541+2500+B12345*
S0006 15041 17540 2500 *S1006+15041+2500+B12345*
S0007 17541 20040 2500 *S1007+17541+2500+B12345*
S0008 20041 20773 733 *S1008+20041+733+B12345*





Column E which is the Group Bar code should use the bar code font
"CarolinaBar-B39-2.5-22x158x720"

I hope I explained the right.


Where do I start?

What happens when job qty is less than group size please? Just in case!

"Little Penny" wrote in message
...
All I can say is WOWWWWWWWWWWWWWWWWWWWWWWW

N F-ing Credible

Thanks

I have just one request the Group ID increase as such...


OLD New
S0001 S0001
S0002 S0002
S0003 S0003
S0004 S0004
S0005 S0005
S0006 S0006
S0007 S0007
S0008 S0008
S0009 S0009
S00010 S0010
S00011 S0011
S00012 S0012
S00013 S0013
S00014 S0014
S00015 S0015

Instead of adding a character it moves to the left.









On Sat, 29 Sep 2007 13:39:02 -0700, FSt1
wrote:

hi
this work on both of your examples
I assumed that all your job would be in the same columns and that you
would
be just adding to them.

Sub Macro1()
Dim jq As Long 'job qty
Dim jqm As Long 'job qty minus
Dim jqbr As Long 'job qty run balance
Dim fp As Long 'first piece
Dim gs As Long 'group size
Dim jn As String 'job number
Dim c As Long 'counter
Dim sp As Range 'sheet placement
c = 1

jq = InputBox("Enter the Job quanty.")
fp = InputBox("Enter the first piece quanty.")
gs = InputBox("Enter the qroup size.")
jn = InputBox("Enter the Job Number.")
jq = jq + fp - 1
jqm = jq

Set sp = Cells(65536, 1).End(xlUp).Offset(1, 0)
sp.Select
sp.Value = "S000" & c
c = c + 1
sp.Offset(0, 1).Value = fp
jqrb = jqrb + gs + fp - 1
sp.Offset(0, 2).Value = jqrb
sp.Offset(0, 3).Value = gs
sp.Offset(0, 4).Value = "*" & sp.Value & "+" & _
sp.Offset(0, 1).Value & "+" & _
sp.Offset(0, 3).Value & "+" & jn & "*"
Do While jqm 0
Set sp = Cells(65536, 1).End(xlUp).Offset(1, 0)
sp.Select
sp.Value = "S000" & c
c = c + 1
fp = fp + gs
sp.Offset(0, 1).Value = fp
If jq - jqrb < gs Then
gs = jq - jqrb
jqrb = jqrb + gs
jqm = jqm = gs
Else
jqrb = jqrb + gs
jqm = jqm - gs
End If
sp.Offset(0, 2).Value = jqrb
sp.Offset(0, 3).Value = gs
sp.Offset(0, 4).Value = "*" & sp.Value & "+" & _
sp.Offset(0, 1).Value & "+" & _
sp.Offset(0, 3).Value & "+" & jn & "*"
Loop

End Sub

regards
FSt1

"Little Penny" wrote:

I'm trying to create a code that will produce the following results.

First the user will need to provide basic information.
Job quantity
First Piece
Group size
Job Number


The "Group Size" breaks the "Job Quantity" up into group size chucks.
For example if the "Job Quantity" is 14524 and the "Group Size" is
3000 we would get four groups of 3000 and one group of 2524


Example 1



Job Quantity = 14524
First Piece = 1
Group Size = 3000
Job Number = A12345


Code will produce:

A B C D E
GroupID START END QTY Group BAR CODE
S0001 1 3000 3000 *S1001+1+3000+A12345*
S0002 3001 6000 3000 *S1002+3001+3000+A12345*
S0003 6001 9000 3000 *S1003+6001+3000+A12345*
S0004 9001 12000 3000 *S1004+9001+3000+A12345*
S0005 12001 14524 2524 *S1005+12001+2524+A12345*


As you can see a "*" is added at the beginning and end in column E.
Also everything is separated by a "+" sign. This format is import
because it will be read by a scanner.



Example 2

In this example the first piece 2541



Job Quantity = 18233
First Piece = 2541
Group Size = 2500
Job Number = A12345


Code will produce:

A B C D E
GroupID START END QTY Group BAR CODE
S0001 2541 5040 2500 *S1001+2541+2500+B12345*
S0002 5041 7540 2500 *S1002+5041+2500+B12345*
S0003 7541 10040 2500 *S1003+7541+2500+B12345*
S0004 10041 12540 2500 *S1004+10041+2500+B12345*
S0005 12541 15040 2500 *S1005+12541+2500+B12345*
S0006 15041 17540 2500 *S1006+15041+2500+B12345*
S0007 17541 20040 2500 *S1007+17541+2500+B12345*
S0008 20041 20773 733 *S1008+20041+733+B12345*





Column E which is the Group Bar code should use the bar code font
"CarolinaBar-B39-2.5-22x158x720"

I hope I explained the right.


Where do I start?

Thanks again

The new addition work but only up to 99

Example



S0095
S0096
S0097
S0098
S0099
S00100
S00101
S00102
S00103


After 99 it changes to 6 digits.


Thanks




On Sat, 29 Sep 2007 16:38:01 -0700, FSt1
wrote:

hi again,
oops. didn't think of that.
read throught the code. 2 PLACES. a line that says....

sp.Value = "S000" & c

replace that line with this....

If Len("S000" & c) 5 Then
sp.Value = "S00" & c
Else
sp.Value = "S000" & c
End If

that should keep your group ids to 5 characters.

Regards
FSt1

"Little Penny" wrote:

All I can say is WOWWWWWWWWWWWWWWWWWWWWWWW

N F-ing Credible

Thanks

I have just one request the Group ID increase as such...


OLD New
S0001 S0001
S0002 S0002
S0003 S0003
S0004 S0004
S0005 S0005
S0006 S0006
S0007 S0007
S0008 S0008
S0009 S0009
S00010 S0010
S00011 S0011
S00012 S0012
S00013 S0013
S00014 S0014
S00015 S0015

Instead of adding a character it moves to the left.









On Sat, 29 Sep 2007 13:39:02 -0700, FSt1
wrote:

hi
this work on both of your examples
I assumed that all your job would be in the same columns and that you would
be just adding to them.

Sub Macro1()
Dim jq As Long 'job qty
Dim jqm As Long 'job qty minus
Dim jqbr As Long 'job qty run balance
Dim fp As Long 'first piece
Dim gs As Long 'group size
Dim jn As String 'job number
Dim c As Long 'counter
Dim sp As Range 'sheet placement
c = 1

jq = InputBox("Enter the Job quanty.")
fp = InputBox("Enter the first piece quanty.")
gs = InputBox("Enter the qroup size.")
jn = InputBox("Enter the Job Number.")
jq = jq + fp - 1
jqm = jq

Set sp = Cells(65536, 1).End(xlUp).Offset(1, 0)
sp.Select
sp.Value = "S000" & c
c = c + 1
sp.Offset(0, 1).Value = fp
jqrb = jqrb + gs + fp - 1
sp.Offset(0, 2).Value = jqrb
sp.Offset(0, 3).Value = gs
sp.Offset(0, 4).Value = "*" & sp.Value & "+" & _
sp.Offset(0, 1).Value & "+" & _
sp.Offset(0, 3).Value & "+" & jn & "*"
Do While jqm 0
Set sp = Cells(65536, 1).End(xlUp).Offset(1, 0)
sp.Select
sp.Value = "S000" & c
c = c + 1
fp = fp + gs
sp.Offset(0, 1).Value = fp
If jq - jqrb < gs Then
gs = jq - jqrb
jqrb = jqrb + gs
jqm = jqm + gs
Else
jqrb = jqrb + gs
jqm = jqm - gs
End If
sp.Offset(0, 2).Value = jqrb
sp.Offset(0, 3).Value = gs
sp.Offset(0, 4).Value = "*" & sp.Value & "+" & _
sp.Offset(0, 1).Value & "+" & _
sp.Offset(0, 3).Value & "+" & jn & "*"
Loop

End Sub

regards
FSt1

"Little Penny" wrote:

I'm trying to create a code that will produce the following results.

First the user will need to provide basic information.
Job quantity
First Piece
Group size
Job Number


The "Group Size" breaks the "Job Quantity" up into group size chucks.
For example if the "Job Quantity" is 14524 and the "Group Size" is
3000 we would get four groups of 3000 and one group of 2524


Example 1



Job Quantity = 14524
First Piece = 1
Group Size = 3000
Job Number = A12345


Code will produce:

A B C D E
GroupID START END QTY Group BAR CODE
S0001 1 3000 3000 *S1001+1+3000+A12345*
S0002 3001 6000 3000 *S1002+3001+3000+A12345*
S0003 6001 9000 3000 *S1003+6001+3000+A12345*
S0004 9001 12000 3000 *S1004+9001+3000+A12345*
S0005 12001 14524 2524 *S1005+12001+2524+A12345*


As you can see a "*" is added at the beginning and end in column E.
Also everything is separated by a "+" sign. This format is import
because it will be read by a scanner.



Example 2

In this example the first piece 2541



Job Quantity = 18233
First Piece = 2541
Group Size = 2500
Job Number = A12345


Code will produce:

A B C D E
GroupID START END QTY Group BAR CODE
S0001 2541 5040 2500 *S1001+2541+2500+B12345*
S0002 5041 7540 2500 *S1002+5041+2500+B12345*
S0003 7541 10040 2500 *S1003+7541+2500+B12345*
S0004 10041 12540 2500 *S1004+10041+2500+B12345*
S0005 12541 15040 2500 *S1005+12541+2500+B12345*
S0006 15041 17540 2500 *S1006+15041+2500+B12345*
S0007 17541 20040 2500 *S1007+17541+2500+B12345*
S0008 20041 20773 733 *S1008+20041+733+B12345*





Column E which is the Group Bar code should use the bar code font
"CarolinaBar-B39-2.5-22x158x720"

I hope I explained the right.


Where do I start?

If my jq (job qty) is less or equal to gs (group size). Or if my gs
(group size) divides evenly into my jq (job qty) my last line looks
like this. How do I prevent that?


S0011 30001 30000 0 *S0011+30001+0+D12345*







On Sun, 30 Sep 2007 09:35:15 +0800, "PY & Associates"
wrote:

What happens when job qty is less than group size please? Just in case!

"Little Penny" wrote in message
.. .
All I can say is WOWWWWWWWWWWWWWWWWWWWWWWW

N F-ing Credible

Thanks

I have just one request the Group ID increase as such...


OLD New
S0001 S0001
S0002 S0002
S0003 S0003
S0004 S0004
S0005 S0005
S0006 S0006
S0007 S0007
S0008 S0008
S0009 S0009
S00010 S0010
S00011 S0011
S00012 S0012
S00013 S0013
S00014 S0014
S00015 S0015

Instead of adding a character it moves to the left.









On Sat, 29 Sep 2007 13:39:02 -0700, FSt1
wrote:

hi
this work on both of your examples
I assumed that all your job would be in the same columns and that you
would
be just adding to them.

Sub Macro1()
Dim jq As Long 'job qty
Dim jqm As Long 'job qty minus
Dim jqbr As Long 'job qty run balance
Dim fp As Long 'first piece
Dim gs As Long 'group size
Dim jn As String 'job number
Dim c As Long 'counter
Dim sp As Range 'sheet placement
c = 1

jq = InputBox("Enter the Job quanty.")
fp = InputBox("Enter the first piece quanty.")
gs = InputBox("Enter the qroup size.")
jn = InputBox("Enter the Job Number.")
jq = jq + fp - 1
jqm = jq

Set sp = Cells(65536, 1).End(xlUp).Offset(1, 0)
sp.Select
sp.Value = "S000" & c
c = c + 1
sp.Offset(0, 1).Value = fp
jqrb = jqrb + gs + fp - 1
sp.Offset(0, 2).Value = jqrb
sp.Offset(0, 3).Value = gs
sp.Offset(0, 4).Value = "*" & sp.Value & "+" & _
sp.Offset(0, 1).Value & "+" & _
sp.Offset(0, 3).Value & "+" & jn & "*"
Do While jqm 0
Set sp = Cells(65536, 1).End(xlUp).Offset(1, 0)
sp.Select
sp.Value = "S000" & c
c = c + 1
fp = fp + gs
sp.Offset(0, 1).Value = fp
If jq - jqrb < gs Then
gs = jq - jqrb
jqrb = jqrb + gs
jqm = jqm = gs
Else
jqrb = jqrb + gs
jqm = jqm - gs
End If
sp.Offset(0, 2).Value = jqrb
sp.Offset(0, 3).Value = gs
sp.Offset(0, 4).Value = "*" & sp.Value & "+" & _
sp.Offset(0, 1).Value & "+" & _
sp.Offset(0, 3).Value & "+" & jn & "*"
Loop

End Sub

regards
FSt1

"Little Penny" wrote:

I'm trying to create a code that will produce the following results.

First the user will need to provide basic information.
Job quantity
First Piece
Group size
Job Number


The "Group Size" breaks the "Job Quantity" up into group size chucks.
For example if the "Job Quantity" is 14524 and the "Group Size" is
3000 we would get four groups of 3000 and one group of 2524


Example 1



Job Quantity = 14524
First Piece = 1
Group Size = 3000
Job Number = A12345


Code will produce:

A B C D E
GroupID START END QTY Group BAR CODE
S0001 1 3000 3000 *S1001+1+3000+A12345*
S0002 3001 6000 3000 *S1002+3001+3000+A12345*
S0003 6001 9000 3000 *S1003+6001+3000+A12345*
S0004 9001 12000 3000 *S1004+9001+3000+A12345*
S0005 12001 14524 2524 *S1005+12001+2524+A12345*


As you can see a "*" is added at the beginning and end in column E.
Also everything is separated by a "+" sign. This format is import
because it will be read by a scanner.



Example 2

In this example the first piece 2541



Job Quantity = 18233
First Piece = 2541
Group Size = 2500
Job Number = A12345


Code will produce:

A B C D E
GroupID START END QTY Group BAR CODE
S0001 2541 5040 2500 *S1001+2541+2500+B12345*
S0002 5041 7540 2500 *S1002+5041+2500+B12345*
S0003 7541 10040 2500 *S1003+7541+2500+B12345*
S0004 10041 12540 2500 *S1004+10041+2500+B12345*
S0005 12541 15040 2500 *S1005+12541+2500+B12345*
S0006 15041 17540 2500 *S1006+15041+2500+B12345*
S0007 17541 20040 2500 *S1007+17541+2500+B12345*
S0008 20041 20773 733 *S1008+20041+733+B12345*





Column E which is the Group Bar code should use the bar code font
"CarolinaBar-B39-2.5-22x158x720"

I hope I explained the right.


Where do I start?

hi again,
sorry i'm late.
just after this line
Do While jqm 0
add this......
If jqrb = jq Then
Exit Do
End If
this will solve the job qty being less than or equal to group size. we call
the group size the lot size here.
as to the other problem....(you have a lot of lots).....in two places in the
code, replace....
If Len("S000" & c) 5 Then
sp.Value = "S00" & c
Else
sp.Value = "S000" & c
End If
with....
If Len("S00" & c) 5 Then
sp.Value = "S0" & c
Else
If Len("S000" & c) 5 Then
sp.Value = "S00" & c
Else
sp.Value = "S000" & c
End If
End If
this should take your groups to 9999. if you have more lots that that, you
need to revamp your system.

regards
FSt1
"Little Penny" wrote:

If my jq (job qty) is less or equal to gs (group size). Or if my gs
(group size) divides evenly into my jq (job qty) my last line looks
like this. How do I prevent that?


S0011 30001 30000 0 *S0011+30001+0+D12345*







On Sun, 30 Sep 2007 09:35:15 +0800, "PY & Associates"
wrote:

What happens when job qty is less than group size please? Just in case!

"Little Penny" wrote in message
.. .
All I can say is WOWWWWWWWWWWWWWWWWWWWWWWW

N F-ing Credible

Thanks

I have just one request the Group ID increase as such...


OLD New
S0001 S0001
S0002 S0002
S0003 S0003
S0004 S0004
S0005 S0005
S0006 S0006
S0007 S0007
S0008 S0008
S0009 S0009
S00010 S0010
S00011 S0011
S00012 S0012
S00013 S0013
S00014 S0014
S00015 S0015

Instead of adding a character it moves to the left.









On Sat, 29 Sep 2007 13:39:02 -0700, FSt1
wrote:

hi
this work on both of your examples
I assumed that all your job would be in the same columns and that you
would
be just adding to them.

Sub Macro1()
Dim jq As Long 'job qty
Dim jqm As Long 'job qty minus
Dim jqbr As Long 'job qty run balance
Dim fp As Long 'first piece
Dim gs As Long 'group size
Dim jn As String 'job number
Dim c As Long 'counter
Dim sp As Range 'sheet placement
c = 1

jq = InputBox("Enter the Job quanty.")
fp = InputBox("Enter the first piece quanty.")
gs = InputBox("Enter the qroup size.")
jn = InputBox("Enter the Job Number.")
jq = jq + fp - 1
jqm = jq

Set sp = Cells(65536, 1).End(xlUp).Offset(1, 0)
sp.Select
sp.Value = "S000" & c
c = c + 1
sp.Offset(0, 1).Value = fp
jqrb = jqrb + gs + fp - 1
sp.Offset(0, 2).Value = jqrb
sp.Offset(0, 3).Value = gs
sp.Offset(0, 4).Value = "*" & sp.Value & "+" & _
sp.Offset(0, 1).Value & "+" & _
sp.Offset(0, 3).Value & "+" & jn & "*"
Do While jqm 0
Set sp = Cells(65536, 1).End(xlUp).Offset(1, 0)
sp.Select
sp.Value = "S000" & c
c = c + 1
fp = fp + gs
sp.Offset(0, 1).Value = fp
If jq - jqrb < gs Then
gs = jq - jqrb
jqrb = jqrb + gs
jqm = jqm = gs
Else
jqrb = jqrb + gs
jqm = jqm - gs
End If
sp.Offset(0, 2).Value = jqrb
sp.Offset(0, 3).Value = gs
sp.Offset(0, 4).Value = "*" & sp.Value & "+" & _
sp.Offset(0, 1).Value & "+" & _
sp.Offset(0, 3).Value & "+" & jn & "*"
Loop

End Sub

regards
FSt1

"Little Penny" wrote:

I'm trying to create a code that will produce the following results.

First the user will need to provide basic information.
Job quantity
First Piece
Group size
Job Number


The "Group Size" breaks the "Job Quantity" up into group size chucks.
For example if the "Job Quantity" is 14524 and the "Group Size" is
3000 we would get four groups of 3000 and one group of 2524


Example 1



Job Quantity = 14524
First Piece = 1
Group Size = 3000
Job Number = A12345


Code will produce:

A B C D E
GroupID START END QTY Group BAR CODE
S0001 1 3000 3000 *S1001+1+3000+A12345*
S0002 3001 6000 3000 *S1002+3001+3000+A12345*
S0003 6001 9000 3000 *S1003+6001+3000+A12345*
S0004 9001 12000 3000 *S1004+9001+3000+A12345*
S0005 12001 14524 2524 *S1005+12001+2524+A12345*


As you can see a "*" is added at the beginning and end in column E.
Also everything is separated by a "+" sign. This format is import
because it will be read by a scanner.



Example 2

In this example the first piece 2541



Job Quantity = 18233
First Piece = 2541
Group Size = 2500
Job Number = A12345


Code will produce:

A B C D E
GroupID START END QTY Group BAR CODE
S0001 2541 5040 2500 *S1001+2541+2500+B12345*
S0002 5041 7540 2500 *S1002+5041+2500+B12345*
S0003 7541 10040 2500 *S1003+7541+2500+B12345*
S0004 10041 12540 2500 *S1004+10041+2500+B12345*
S0005 12541 15040 2500 *S1005+12541+2500+B12345*
S0006 15041 17540 2500 *S1006+15041+2500+B12345*
S0007 17541 20040 2500 *S1007+17541+2500+B12345*
S0008 20041 20773 733 *S1008+20041+733+B12345*





Column E which is the Group Bar code should use the bar code font
"CarolinaBar-B39-2.5-22x158x720"

I hope I explained the right.


Where do I start?

Long day for me today

Thanks for your reply

The code for the GroupID works as advertised.

Unless I did something wrong the job qty being less the group size
still presents a problem. If it's greater than or equal to it works
great. However I was thinking in reality this should not happen. The
user should adjust the group size. Can I add a line of code to say to
the user if the job qty is less then the group size. The group size
should be changed automatically by the code to equal the job qty. That
would do it.


Thanks







On Sun, 30 Sep 2007 09:05:01 -0700, FSt1
wrote:

hi again,
sorry i'm late.
just after this line
Do While jqm 0
add this......
If jqrb = jq Then
Exit Do
End If
this will solve the job qty being less than or equal to group size. we call
the group size the lot size here.
as to the other problem....(you have a lot of lots).....in two places in the
code, replace....
If Len("S000" & c) 5 Then
sp.Value = "S00" & c
Else
sp.Value = "S000" & c
End If
with....
If Len("S00" & c) 5 Then
sp.Value = "S0" & c
Else
If Len("S000" & c) 5 Then
sp.Value = "S00" & c
Else
sp.Value = "S000" & c
End If
End If
this should take your groups to 9999. if you have more lots that that, you
need to revamp your system.

User will key in anything you least expect them to.

Break the user input qty into two parts.
First part=how many group size;
2nd part=remainder.

if first part0, then do as for group size, loop;
if remainder0, then do as for group size but for remainder qty;
if remainder=0 is automatically taken care of

--
Regards

"Little Penny" wrote in message
...
Long day for me today

Thanks for your reply

The code for the GroupID works as advertised.

Unless I did something wrong the job qty being less the group size
still presents a problem. If it's greater than or equal to it works
great. However I was thinking in reality this should not happen. The
user should adjust the group size. Can I add a line of code to say to
the user if the job qty is less then the group size. The group size
should be changed automatically by the code to equal the job qty. That
would do it.


Thanks







On Sun, 30 Sep 2007 09:05:01 -0700, FSt1
wrote:

hi again,
sorry i'm late.
just after this line
Do While jqm 0
add this......
If jqrb = jq Then
Exit Do
End If
this will solve the job qty being less than or equal to group size. we
call
the group size the lot size here.
as to the other problem....(you have a lot of lots).....in two places in
the
code, replace....
If Len("S000" & c) 5 Then
sp.Value = "S00" & c
Else
sp.Value = "S000" & c
End If
with....
If Len("S00" & c) 5 Then
sp.Value = "S0" & c
Else
If Len("S000" & c) 5 Then
sp.Value = "S00" & c
Else
sp.Value = "S000" & c
End If
End If
this should take your groups to 9999. if you have more lots that that, you
need to revamp your system.

Somehow my response on Saturday didn't get posted. But if you are interested
in changing the inputboxes to a dialog window, send me your code and I will
make the changes using a VBA userform.

Look at lesson 8 at this microsoft help page for example of dialog boxes
http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx

The Userform2 dialog box can be easily created.

Send me the file at

"Little Penny" wrote:


Hi Joel

Thanks for you replay

I have a spreadsheet that does what I have described. What I was
trying to do is recreate what it does using VBA code. It gives much
more flexibility for other things down the road. I can email you a
sample of my spreadsheet if you like. But my main focus was to
recreate what my spreadsheet formulasdo using VBA

I will start by asking the user


Ask1:
jnumber = Application.InputBox("Job Number", Title:="Job Number",
Type:=1)
If jnumber = False Then

End If

Ask2:
fpiece = Application.InputBox("Enter first piece", Title:="First
Piece", Type:=1)
If fpiece = False Then
End If

Ask3:
Jquanity = Application.InputBox("Enter Job quanity", Title:="Job
Quanity", Type:=1)
If Jquanity = False Then
End If


Ask4:
gsize = Application.InputBox("Enter group quanity", Title:="Group
Quanity", Type:=1)
If gsize = False Then

End If

Can I email you my spreadsheet?




















On Sat, 29 Sep 2007 12:16:00 -0700, Joel
wrote:

Hi Penny. I will be glad to help like in the past. I found the website on
Carolina Bar code the linnk is below.

http://carolinabarcode.com

I downloaded the excel example which gives the method for printing the bar
codes and generating the bar code with the required CRC. I've worked with
Bar codes befroe so I have a good understainding of how to generte the codes.
MY Electrical Engineering and math background has given plenty of classroom
work to be pretty much an expert on generating codes.

I'm not usre from your description if you are having problems figuring out
how to generate the codes of just combining columns A - D into E. Column E
is just a cobination of the field A-D.

I used the download function from the Carolina Website and used this formula
to get your results

Cell E1
=bc3of9(A1&B1&D1&"B12345")

Cell F1 - add the plus signs into string
=LEFT(E1,6)&"+"&MID(E1,7,4)&"+"&MID(E1,11,4)&"+"& RIGHT(E1,7)

The function BC3of9 converts the character to proper format and places the *
at the beginning and end. The Plus signs are not part of the Bar code and
are added only to make it easier to read the numbers.

"Little Penny" wrote:

I'm trying to create a code that will produce the following results.

First the user will need to provide basic information.
Job quantity
First Piece
Group size
Job Number


The "Group Size" breaks the "Job Quantity" up into group size chucks.
For example if the "Job Quantity" is 14524 and the "Group Size" is
3000 we would get four groups of 3000 and one group of 2524


Example 1



Job Quantity = 14524
First Piece = 1
Group Size = 3000
Job Number = A12345


Code will produce:

A B C D E
GroupID START END QTY Group BAR CODE
S0001 1 3000 3000 *S1001+1+3000+A12345*
S0002 3001 6000 3000 *S1002+3001+3000+A12345*
S0003 6001 9000 3000 *S1003+6001+3000+A12345*
S0004 9001 12000 3000 *S1004+9001+3000+A12345*
S0005 12001 14524 2524 *S1005+12001+2524+A12345*


As you can see a "*" is added at the beginning and end in column E.
Also everything is separated by a "+" sign. This format is import
because it will be read by a scanner.



Example 2

In this example the first piece 2541



Job Quantity = 18233
First Piece = 2541
Group Size = 2500
Job Number = A12345


Code will produce:

A B C D E
GroupID START END QTY Group BAR CODE
S0001 2541 5040 2500 *S1001+2541+2500+B12345*
S0002 5041 7540 2500 *S1002+5041+2500+B12345*
S0003 7541 10040 2500 *S1003+7541+2500+B12345*
S0004 10041 12540 2500 *S1004+10041+2500+B12345*
S0005 12541 15040 2500 *S1005+12541+2500+B12345*
S0006 15041 17540 2500 *S1006+15041+2500+B12345*
S0007 17541 20040 2500 *S1007+17541+2500+B12345*
S0008 20041 20773 733 *S1008+20041+733+B12345*





Column E which is the Group Bar code should use the bar code font
"CarolinaBar-B39-2.5-22x158x720"

I hope I explained the right.


Where do I start?