Using IF function with VLOOKUP for VBA

[stemhel]

I currently have an excel field using a formula and data validation. The formula is an IF statement that looks for any text from one cell(ie equipment part number) and then applies a VLOOKUP to find the name of the equipment. Based on the output of the VLOOKUP there is a data validation for a list to select either New or Used + Equipment type. All this is happening inside a table and I am having issues with the IF/VLOOKUP formula not auto-copying to a new row when new information is added. The data validation copies down to the new table row but not the formula. I am wanting to create a macro that applies to the entire Column that executes the formula function.

example of the formula
=IF(J2="","",VLOOKUP(J2,Parts!$A:$B,2,FALSE))

 

[Yongle]

The formula does copy down inside a table

Use this notation (PartNo is the header in column J)

=IF([@PartNo]="","",VLOOKUP([@PartNo],Parts!A:B,2,0))

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][th]

L​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td=bgcolor:#4472C4]A[/td][td=bgcolor:#4472C4]B[/td][td=bgcolor:#4472C4]C[/td][td=bgcolor:#4472C4]D[/td][td=bgcolor:#4472C4]E[/td][td=bgcolor:#4472C4]F[/td][td=bgcolor:#4472C4]G[/td][td=bgcolor:#4472C4]H[/td][td=bgcolor:#4472C4]I[/td][td=bgcolor:#4472C4]PartNo[/td][td=bgcolor:#4472C4]Type[/td][td=bgcolor:#4472C4]formula in column K[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td=bgcolor:#D9E1F2]

20/07/2019​

[/td][td=bgcolor:#D9E1F2]

54​

[/td][td=bgcolor:#D9E1F2]

70​

[/td][td=bgcolor:#D9E1F2]

30​

[/td][td=bgcolor:#D9E1F2]

81​

[/td][td=bgcolor:#D9E1F2]

17​

[/td][td=bgcolor:#D9E1F2]

51​

[/td][td=bgcolor:#D9E1F2]

31​

[/td][td=bgcolor:#D9E1F2]

15​

[/td][td=bgcolor:#D9E1F2]Part001[/td][td=bgcolor:#D9E1F2]a[/td][td=bgcolor:#D9E1F2]=IF([@PartNo]="","",VLOOKUP([@PartNo],Parts!A:B,2,0))[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

20/07/2019​

[/td][td]

10​

[/td][td]

42​

[/td][td]

16​

[/td][td]

71​

[/td][td]

92​

[/td][td]

27​

[/td][td]

88​

[/td][td]

72​

[/td][td]Part002[/td][td]b[/td][td]=IF([@PartNo]="","",VLOOKUP([@PartNo],Parts!A:B,2,0))[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]