I would like a vba script that will determine if an assembly is New or C/O based on 5 criteria.
The criteria:
1. Assembly p/n are structured like ####-AA-AA [where #### is numeric, and AA and AA could be any alpha character].
2. Block Code Assemblies p/n are structured like ####-AA-A-AA [where #### is numeric, and AA and A could be any alpha character].
3. Standard p/n are structured like 9####-##### [where # could be alphanumeric].
4. Service Assembly p/n are structured like ####-00-## and ####-01-##
5. Component p/n are structured like #$$$$-$$$$$ [where # is numeric, and $ could be alphanumeric], and have a level of 1-9 [1=sub-assembly or individual component, 2=child of subsequent 1, 3=child of subsequent 2, etc.].
6. Assemblies are made up of varying numbers of components and sub-assemblies.
7. Assemblies which contain a new component [not used in previous project] AND that component is not a standard part [standard part number begins with "9" , a nut, bolt,etc] is a New assembly.
8. Assemblies which contain no new components is a C/O Assembly.
9. Previous Project Code column lists previous project where component used [where applicable], and will be blank If no previous use = New component.
10. If an assembly contains ANY New components which are not a standard part, and the assembly is not a block code, or for service, then the assembly is New.

In column A please, I would like the conditions reported on the corresponding Assembly row: New, C/O, BC, Svc.

Thanks in advance, and if you have any questions, please let me know.

Dj