VBA macro to check if one cell has text and paste a formula in a different cell

Basically I need a code to look in B8 for text and if there is text I need the code to place a formula in the corresponding "D" column.

so for instance if B8 says adam then i need a code to paste =VLOOKUP(B8,Sheet1!A:B,2,FALSE) and so on for the entire column B and D??

Thank you so much worked perfectly

That worked perfectly however I tried to use the same code with this formula and i get a error

Please Login or Register  to view this content.