Populate if Specific Text found

[The Animal]

Hi
Maybe someone can point me in the right direction.
I have a tab that I am using as a key (Award Abbrev) that I want to use to populate cells in another tab in the same workbook (Staff Details)
I have created specific text in I2:I60 in "Award Abbrev" that on the corresponding row has values in R,U,X,AA.
On the "Staff Details" tab if the specific text is entered in the I column of that row, then the data that is found in R,U,X,AA in "Award Abbrev" will the populate in R,U,X,AA in Staff Details.
Any help would be great
Thanks Stephen

 

[PCL]

In sheet "Staff Details" code put

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Const InCol As String = "I"
Dim InColInt As Integer
Const OutCol As String = "R/U/X/AA"
Const WsRefName As String = "Award Abbrev"
Dim WS As Worksheet
Dim V
    InColInt = Cells(1, InCol).Column
    If (Target.Column <> InColInt) Then Exit Sub
    For Each V In Split(OutCol, "/")
        Cells(Target.Row, V) = SearVal(Target.Value, V)
    Next V
End Sub

in a module put
You will adjust Constante as needed

Option Explicit
Function SearVal(ByVal WkVal, ByVal OutWkCol As String) As Variant
Dim WS As Worksheet
Dim WkRg As Range
Dim F
Const WsName As String = "Award Abbrev"
Const WkRgAdd As String = "I2:I60"


    SearVal = ""
    Set WS = Sheets(WsName)
    Set WkRg = WS.Range(WkRgAdd)
    With WkRg
    Set F = .Find(What:=WkVal, After:=.Cells(1, 1), LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlRows, SearchDirection:=xlNext, _
            MatchCase:=False)
    End With
    If (F Is Nothing) Then Exit Function
    Application.EnableEvents = False
    SearVal = WS.Cells(F.Row, OutWkCol)
    Application.EnableEvents = True


End Function

 

[The Animal]

Great thanks so much