Vadlidation code not working

[Guest]

I'm trying to get "Look up one - fill in the other" code to work

Worksheet (HAZARDS): (Column formatting = "General")

COL B COL C
1 Hazard CAS Code Hazard Name
2 110-44-1 (E,E)-2,4-HEXADIENOIC ACID (SORBIC ACID)
3 68399724 [2,6 -BIBENZOTHIAZOLE]-7-SULFONIC ACID,
4 992-59-6 0-TOLIDINE
5 85847 1-(PHENYLAZO)-2-NAPHTHYLAMINE

Names:
CASCODE: =OFFSET(HAZARDS!$B$2,0,0,COUNTA(HAZARDS!$B:$B)-1,1)
HAZNAME: =OFFSET(CASCODE,0,1)


Worksheet (DATA ENTRY)

Option Explicit
_______________________________________________

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim wsHAZARDS As Worksheet
Dim CASCODEROW As Long
Dim HAZNAMEROW As Long

On Error GoTo errHandler
Set wsHAZARDS = Worksheets("HAZARDS")

If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False

Select Case Target.Column
Case 2
With Target
If .Value = "" Then
.Offset(0, 1).Value = ""
Else
CASCODEROW = Application.Match(.Value, wsHAZARDS.Range("CASCODE"), 0)
.Offset(0, 1).Value = wsHAZARDS.Range("HAZNAME")(CASCODEROW).Value
End If
End With
Case 3
With Target
If .Value = "" Then
.Offset(0, -1).Value = ""
Else
HAZNAMEROW = Application.Match(.Value, wsHAZARDS.Range("HAZNAME"), 0)
.Offset(0, -1).Value = wsHAZARDS.Range("CASCODE")(HAZNAMEROW).Value
End If
End With
Case Else
'do nothing
End Select

exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox Err.Number & ": " & Err.Description
GoTo exitHandler

End Sub


Thanks -- Suzanne

 

[Guest]

Worried this is getting buried... I could REALLY use some help on this!!!

Is it something to do with the "row"? I already had to adjust the error
code to eliminate an error when entering data in other sections.

exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:GoTo exitHandler

Thanks -- Suz

I'm trying to get "Look up one - fill in the other" code to work

Worksheet (HAZARDS): (Column formatting = "General")

COL B COL C
1 Hazard CAS Code Hazard Name
2 110-44-1 (E,E)-2,4-HEXADIENOIC ACID (SORBIC ACID)
3 68399724 [2,6 -BIBENZOTHIAZOLE]-7-SULFONIC ACID,
4 992-59-6 0-TOLIDINE
5 85847 1-(PHENYLAZO)-2-NAPHTHYLAMINE

Names:
CASCODE: =OFFSET(HAZARDS!$B$2,0,0,COUNTA(HAZARDS!$B:$B)-1,1)
HAZNAME: =OFFSET(CASCODE,0,1)


Worksheet (DATA ENTRY)

Option Explicit
_______________________________________________

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim wsHAZARDS As Worksheet
Dim CASCODEROW As Long
Dim HAZNAMEROW As Long

On Error GoTo errHandler
Set wsHAZARDS = Worksheets("HAZARDS")

If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False

Select Case Target.Column
Case 2
With Target
If .Value = "" Then
.Offset(0, 1).Value = ""
Else
CASCODEROW = Application.Match(.Value, wsHAZARDS.Range("CASCODE"), 0)
.Offset(0, 1).Value = wsHAZARDS.Range("HAZNAME")(CASCODEROW).Value
End If
End With
Case 3
With Target
If .Value = "" Then
.Offset(0, -1).Value = ""
Else
HAZNAMEROW = Application.Match(.Value, wsHAZARDS.Range("HAZNAME"), 0)
.Offset(0, -1).Value = wsHAZARDS.Range("CASCODE")(HAZNAMEROW).Value
End If
End With
Case Else
'do nothing
End Select

exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox Err.Number & ": " & Err.Description
GoTo exitHandler

End Sub


Thanks -- Suzanne