P
Pat
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(Sheet2!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
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(Sheet2!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