Macro Required for Lookup Function
Put the following in the Input sheet's code module.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim bFound As Boolean
Dim iEnd As Integer
Dim c As Range
Dim rng As Range
If Target.Column = 8 Then
If Target = "" Then
Target.Offset(0, -7) = ""
Target.Offset(0, -6) = ""
Target.Offset(0, -1) = ""
Else
iEnd = Sheets("Schedule").Range("A2").End(xlDown).Row
Set rng = Sheets("Schedule").Range("A2:A" & iEnd)
For Each c In rng
If Target = c Then
Target.Offset(0, -7) = c.Offset(0, 2)
Target.Offset(0, -6) = c.Offset(0, 1)
Target.Offset(0, -1) = c.Offset(0, 3)
bFound = True
Exit For
End If
Next c
If bFound = False Then
MsgBox ("Ref No not found.")
Target = ""
End If
End If
End If
End Sub
Hth,
Merjet
|