Setting up barcode

There are two sheets connected to this question

Sheet1:
column D = product names
column I = unique 13 digit barcode

When new products are added at the bottom of the list a barcode is inserted
into col I using the code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim counter
If Target.Cells.Count 1 Then Exit Sub
If Target.Column 4 Then Exit Sub
On Error GoTo errhandler
Application.EnableEvents = False
With Target
If .Value = "" Then
.Offset(0, 5).ClearContents
Else
counter = Application.WorksheetFunction.Max(Me.Range("I:I")) + 1
.Offset(0, 5).NumberFormat = "0000000006521"
.Offset(0, 5).Value = counter
End If
End With

errhandler:
Application.EnableEvents = True
End Sub

After a new item is entered the sheet is sorted on col D.



Sheet2:

Column C = product name
Column AG = unique 13 digit barcode

In col AG a formula is applied to get the barcode from sheet1

{=IF(ISERROR(INDEX(Sheet1!$I$9:$I$2000,MATCH(Sheet 2!C85,Sheet1!$D$9:$D$2000,
0))),"",INDEX(Sheet1!$I$9:$I$2000,MATCH(Sheet2!C85 ,Sheet1!$D$9:$D$2000,0)))}

The problem I am getting is that the formula does not read the barcode in
sheet1 as displayed in the cell but the number displayed on the Formula Bar
which the code generates.

Can anyone suggest what I need to do. Thanks in advance for any help given.

Pat

Hi Pat,
In Excel barcodes are "DISPLAYED" in a cell. It's a font thing.
All you will ever get is the underlying alphanumeric value.

If you want to "SEE" the barcode returned by the formula, change the font to
your barcode font.
(you can do that manually or with VBA but not a worksheet formula)

--


John

johnf202 at hotmail dot com


"Pat" wrote in message
...
: There are two sheets connected to this question
:
: Sheet1:
: column D = product names
: column I = unique 13 digit barcode
:
: When new products are added at the bottom of the list a barcode is
inserted
: into col I using the code
:
: Private Sub Worksheet_Change(ByVal Target As Range)
: Dim counter
: If Target.Cells.Count 1 Then Exit Sub
: If Target.Column 4 Then Exit Sub
: On Error GoTo errhandler
: Application.EnableEvents = False
: With Target
: If .Value = "" Then
: .Offset(0, 5).ClearContents
: Else
: counter = Application.WorksheetFunction.Max(Me.Range("I:I")) + 1
: .Offset(0, 5).NumberFormat = "0000000006521"
: .Offset(0, 5).Value = counter
: End If
: End With
:
: errhandler:
: Application.EnableEvents = True
: End Sub
:
: After a new item is entered the sheet is sorted on col D.
:
:
:
: Sheet2:
:
: Column C = product name
: Column AG = unique 13 digit barcode
:
: In col AG a formula is applied to get the barcode from sheet1
:
:
{=IF(ISERROR(INDEX(Sheet1!$I$9:$I$2000,MATCH(Sheet 2!C85,Sheet1!$D$9:$D$2000,
:
0))),"",INDEX(Sheet1!$I$9:$I$2000,MATCH(Sheet2!C85 ,Sheet1!$D$9:$D$2000,0)))}
:
: The problem I am getting is that the formula does not read the barcode in
: sheet1 as displayed in the cell but the number displayed on the Formula
Bar
: which the code generates.
:
: Can anyone suggest what I need to do. Thanks in advance for any help
given.
:
: Pat
:
:

Hello Jaf
I think I have confused the issue here. It is the numeric value that I am
dealing with here and not your typical barcode as you would see on products
every day. What I will be doing with the numeric value is turning it into a
barcode, via another program. So instead of having the subject: 'Setting up
barcode' it should have been 'Setting up alphanumeric values'

Does this help in understanding my problem?

Pat


"jaf" wrote in message
...
Hi Pat,
In Excel barcodes are "DISPLAYED" in a cell. It's a font thing.
All you will ever get is the underlying alphanumeric value.

If you want to "SEE" the barcode returned by the formula, change the font
to
your barcode font.
(you can do that manually or with VBA but not a worksheet formula)

--


John

johnf202 at hotmail dot com


"Pat" wrote in message
...
: There are two sheets connected to this question
:
: Sheet1:
: column D = product names
: column I = unique 13 digit barcode
:
: When new products are added at the bottom of the list a barcode is
inserted
: into col I using the code
:
: Private Sub Worksheet_Change(ByVal Target As Range)
: Dim counter
: If Target.Cells.Count 1 Then Exit Sub
: If Target.Column 4 Then Exit Sub
: On Error GoTo errhandler
: Application.EnableEvents = False
: With Target
: If .Value = "" Then
: .Offset(0, 5).ClearContents
: Else
: counter = Application.WorksheetFunction.Max(Me.Range("I:I")) + 1
: .Offset(0, 5).NumberFormat = "0000000006521"
: .Offset(0, 5).Value = counter
: End If
: End With
:
: errhandler:
: Application.EnableEvents = True
: End Sub
:
: After a new item is entered the sheet is sorted on col D.
:
:
:
: Sheet2:
:
: Column C = product name
: Column AG = unique 13 digit barcode
:
: In col AG a formula is applied to get the barcode from sheet1
:
:

{=IF(ISERROR(INDEX(Sheet1!$I$9:$I$2000,MATCH(Sheet 2!C85,Sheet1!$D$9:$D$2000,
:

0))),"",INDEX(Sheet1!$I$9:$I$2000,MATCH(Sheet2!C85 ,Sheet1!$D$9:$D$2000,0)))}
:
: The problem I am getting is that the formula does not read the barcode
in
: sheet1 as displayed in the cell but the number displayed on the Formula
Bar
: which the code generates.
:
: Can anyone suggest what I need to do. Thanks in advance for any help
given.
:
: Pat
:
: