translate vlookup

[andysh]

Hi

I have 10 users updating a shared workbook with a worksheet each to enter data. Col F on each sheet has the below formula and I'm wondering how I can translate the formula to VBA to avoid users editing/deleting the formula (I can't protect as it affects a macro to copy the data to a master sheet).

=IF(ISNA(VLOOKUP(E2,Lists!$A$1:$B$18,2,FALSE)),"",VLOOKUP(E2,Lists!$A$1:$B$18,2,FALSE))

Each worksheet already has a private sub and I'd like to add the VLOOKUP into it if I can.

 

[steve the fish]

Which cell/ cells is this formula in?

 

[jasonb75]

I can't protect as it affects a macro to copy the data to a master sheet

That's not true, you could add a couple of lines to the macro to remove the protection, then re-apply it after it has finished running.

See post 2 here VBA code to unprotect a protected sheet for a simple example of how to do this.

 

[andysh]

The formula is in all cells in col F