VBA code to populate a formula based on a table ( ID // Name)

Hello everyone,

I've been trying to find a way to write a formula in vba but I only see stuff on how to insert a fixed formula,
I'm trying to have a dynamically written formula by VBA


The code would have to repeat a section of the formula [ ,IF(--NOT(ISERROR(SEARCH("for each project ID",H2))),"and each name", ]

one after the other untill all projects are accounted for


there may be up to a bunch of projects at a time but they will change often so that's why it needs to adjust to that.


the first two arguments of the formula are

=IF(A2=0,"",IF(--NOT(ISERROR(SEARCH("EXTRA",J2))),"EXTRA",

followed by
Something like

IF(--NOT(ISERROR(SEARCH("first in list",H2))),"name1",IF(--NOT(ISERROR(SEARCH("Second in list",H2))),"name2",IF(--NOT(ISERROR(SEARCH("third in list",H2))),"name3",


and so on until all projects are mentioned.


with the final comma "false answer" being [ ,"Regular" ] if no project ID was found


then to paste that in Column R of Sheet "LIVE ALL" (from R2 all the way down for each row of data on this sheet)


The projects are written on a sheet called "Projects"

Column A has an Sorting order number to help sort put the projects in order not to get false positives for incomplete matches.
Column B has the project IDs
Column C has the project names


How would you approach this with VBA ?


I have uploaded a sample Workbook you may download




Thank you for your help and any guidance.

the idea is to make it user friendly so the users can add and remove projects from the project table and the formula would adjust to it every time a macro is run to identify projects


as far as DATA growing I am not concerned as i know it is working correctly with barely any delay to calculate I am already using this formula on my master sheet (20K+ Rows) but I write it myself and I am trying to have VBA do it so Other users can manipulate the projects and I wont have to write the formula for it each time projects change

Let me know if you have any questions.

WOW that's a beautiful approach and i wont even need VBA

Althought, that has a nested search for the word "project"

Would it be possible to have this formula work without looking for a project name containing "project"

all projects are differently named and some even have commas in them

can this formula be adapted to pickup any strings written in column B of projects WITHOUT HAVING AN EMBEDDED search for the word "PROJECT" in the projects ?