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?