vba to replace value with a value from another sheet based on criteria

[Godwin117]

I have this VBA that imports a workbook("Sheet1"), instead of replacing the values each time I am trying to have a VBA to do it for me. The values change every so often, so it needs to be as universal as possible. I have no idea where to start for this.
The correct values are in sheet ("Data Validation") Columns E1, F1, G1.
The names of the personnel will be under each of those columns in the "Data Validation" sheet.
If the names (column "B6 and below") on sheet1 are equal to one of those names in the "Data Validation" sheet it will replace the value currently in "Sheet1" column I with the value in the same column as the name, but the first row.
Example is below

Sheet1

Billy, Bob	a
Go, Pro	r
Gordib, Niun	b
Hunker, Naa	g
Pop, Bab	u

Changes to that
|
|
\|/

Sheet1

Billy, Bob	Pizza
Go, Pro	Rolls
Gordib, Niun	Spaghetti
Hunker, Naa	Spaghetti
Pop, Bab	Donuts

Based on that
|
|
\|/



Data Validation sheet

columnE	CoulmnF	Coulumn G
Pizza	Spaghetti	Donuts
Billy, Bob	Gordib, niun	Pop, Bab
	Hunker, Naa

 

[DanteAmor]

How about:

Sub ReplaceValue()
  Dim i As Long, f As Range
  With Sheets("Sheet1")
    For i = 6 To .Range("B" & Rows.Count).End(3).Row
      Set f = Sheets("Data Validation").Range("E:G").Find(.Range("B" & i), , xlValues, xlWhole)
      If Not f Is Nothing Then .Range("I" & i).Value = Sheets("Data Validation").Cells(1, f.Column)
    Next
  End With
End Sub

 

[Godwin117]

Thank you, that worked great!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.