How to have VBA to perform vlookups when command button is clicked

[Zimmerman]

How can I get VBA to perform a vlookup in a range of cells. Right now I have this formulas in cells like below.

Cell A8 = IF(C8>"",VLOOKUP($A$7,'Employee Data'!$B$4:$D$27,2,0),"")
Cell A9 = IF(C9>"",VLOOKUP($A$7,'Employee Data'!$B$4:$D$27,2,0),"")
Cell A10 = IF(C10>"",VLOOKUP($A$7,'Employee Data'!$B$4:$D$27,2,0),"")
Cell A11 = IF(C11>"",VLOOKUP($A$7,'Employee Data'!$B$4:$D$27,2,0),"")
Cell A12 = IF(C12>"",VLOOKUP($A$7,'Employee Data'!$B$4:$D$27,2,0),"")
Cell A13 = IF(C13>"",VLOOKUP($A$7,'Employee Data'!$B$4:$D$27,2,0),"")
Cell A14 = IF(C14>"",VLOOKUP($A$7,'Employee Data'!$B$4:$D$27,2,0),"")
Cell A15 = IF(C15>"",VLOOKUP($A$7,'Employee Data'!$B$4:$D$27,2,0),"")

Cell A22 = IF(C22>"",VLOOKUP($A$21,'Employee Data'!$B$4:$D$27,2,0),"")
Cell A23 = IF(C23>"",VLOOKUP($A$21,'Employee Data'!$B$4:$D$27,2,0),"")
Cell A24 = IF(C24>"",VLOOKUP($A$21,'Employee Data'!$B$4:$D$27,2,0),"")
Cell A25 = IF(C25>"",VLOOKUP($A$21,'Employee Data'!$B$4:$D$27,2,0),"")
Cell A26 = IF(C26>"",VLOOKUP($A$21,'Employee Data'!$B$4:$D$27,2,0),"")
Cell A27 = IF(C27>"",VLOOKUP($A$21,'Employee Data'!$B$4:$D$27,2,0),"")
Cell A28 = IF(C28>"",VLOOKUP($A$21,'Employee Data'!$B$4:$D$27,2,0),"")
Cell A29 = IF(C29>"",VLOOKUP($A$21,'Employee Data'!$B$4:$D$27,2,0),"")

I'd like to be able to perform these lookups in VBA because the users start messing with and changing things. And these lookups would need to run when the user would click an OK command button on a userform.

Thanks in advance
Excel 2016

 

[Fluff]

How about

Sub Zimmerman()
   Dim Cl As Range
   Dim Dic As Object
   
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Employee Data")
      For Each Cl In .Range("B4:B27")
         Dic.Item(Cl.Value) = Cl.Offset(, 1).Value
      Next Cl
   End With
   With Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
      For Each Cl In .Range("C8:C15")
         If Cl.Value <> "" Then Cl.Offset(, -2).Value = Dic.Item(Range("A7").Value)
      Next Cl
      For Each Cl In .Range("C22:C29")
         If Cl.Value <> "" Then Cl.Offset(, -2).Value = Dic.Item(Range("A21").Value)
      Next Cl
   End With
End Sub

Change sheet name in red to suit

 

[Zimmerman]

That is brilliant and perfect. Thanks so much!

 

[Fluff]

You're welcome & thanks for the feedback

 

[Zimmerman]

Hate to ask another one of these but how would I incorporate the following formulas into VBA. Same concept as it has the same ranges.
Cell F8 =VLOOKUP(C8,'Pallet and Truss Data'!$A8:$F$400,5,0)
Cell F9 =VLOOKUP(C9,'Pallet and Truss Data'!$A8:$F$400,5,0)
Cell F10 =VLOOKUP(C10,'Pallet and Truss Data'!$A8:$F$400,5,0)
Cell F11 =VLOOKUP(C11,'Pallet and Truss Data'!$A8:$F$400,5,0)
Cell F12 =VLOOKUP(C12,'Pallet and Truss Data'!$A8:$F$400,5,0)
Cell F13 =VLOOKUP(C13,'Pallet and Truss Data'!$A8:$F$400,5,0)
Cell F14 =VLOOKUP(C14,'Pallet and Truss Data'!$A8:$F$400,5,0)
Cell F15 =VLOOKUP(C15,'Pallet and Truss Data'!$A8:$F$400,5,0)

Thanks again

 

[Fluff]

How about

Sub Zimmerman()
   Dim Cl As Range
   Dim Dic As Object
   
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Pallet and Truss Data")
      For Each Cl In .Range("A8:A400")
         Dic.Item(Cl.Value) = Cl.Offset(, 4).Value
      Next Cl
   End With
   With Sheets("Sheet1")
      For Each Cl In .Range("F8:F15")
         Cl.Value = Dic.Item(Cl.Offset(, -3).Value)
      Next Cl
   End With
End Sub

 

[Zimmerman]

Again you make things easy. Thanks again!

 

[Fluff]

My pleasure & thanks for the feedback