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?