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

[speedyacct]

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.