VBA code for VLOOKUP - automated for an entire column

[newbie111]

Hi, I wonder if anyone can help. Apologies I'm very new to this but here is what I'm trying to do...

I have a database (in sheet 1, columns A and B covering Name and Department).

Ideally, when a user adds a name into column A on sheet2 I would like it to search my database and return the correct department in column B. Sheet 2 is updated/completed on a daily basis so there is no defined range. The database will also be added to.

I have managed to get exactly what I need when I run it so far with this solely for one cell:

set Datatable = sheet1.range("A:B")
set Name = sheet2.range("A2")
Set Mark = sheet2.range("B2")

Mark.value = application.worksheetfunction.vlookup(Name, Datatable, 2, False)

I'm struggling with how to translate this into an automatic response when the user adds information into any row in column A....Any help is greatly appreciated!

 

[Peter_SSs]

Welcome to the MrExcel board!

Do you really need vba? What if you make a formal table on each worksheet & use a formula as I have done below. The formula will automatically extend to new rows in the sheet2 table and will reference all old and new rows in the sheet1 table.

Here is my Sheet1. I have named this table 'DBtable'. Once the table has been created (Insert ribbon tab -> Table -> my table has headers) its name can be edited in the Name Manager on the Formulas ribbon tab.

newbie111 2020-06-02 1.xlsm
	A	B
1	Name	Department
2	Name 1	Dept 1
3	Name 2	Dept 2
4	Name 3	Dept 3
5	Name 4	Dept 1
6	Name 5	Dept 2
7	Name 6	Dept 3
8	Name 7	Dept 1
9
Sheet1

Then my Sheet2 table with formula

newbie111 2020-06-02 1.xlsm
	A	B
1	Name	Department
2	Name 4	Dept 1
3
4	Name 10	Not found
5	Name 6	Dept 3
6
Sheet2

Cell Formulas
Range	Formula
B2:B5	B2	=IF([@Name]="","",IFERROR(VLOOKUP([@Name],DBtable,2,0),"Not found"))