S
Suzanne
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
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