Get Earliest Full-Kit Date from Supply Plan+ BOM with VBA

[dungnv]

I need help...

With VBA, how to get the earliest full kit date for 10pcs SKU from the supply plan and BOM?

** B, C, E are semi-FGs and their inventories need to be used up first. If there is a short, will start fresh build from components. ** D, F are components.

Thanks alot.

fullkit.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Supply								BOM						Check Week 1
2	Week	1	2	3	4	5	6		Child	Qty	Parent
3	A	4	4	3	3	7	9		A	1	SKU				Demand	Request	Inventory	Short
4	B	8	10	7	2	4	4		B	1	A				SKU	10	0	-10
5	C	8	6	3	10	4	8		C	2	A			1	A	10	4	-6
6	D	6	6	4	10	5	6		D	2	B			1	B	6	8	2
7	E	2	6	8	6	5	2		E	2	C			2	C	12	8	-4
8	F	7	3	9	9	8	3		F	2	E			2	D	0	6	0	>=0, OK
9														2	E	8	2	-6
10	A		Make											2	F	12	7	-5	<0, can't build
11	B, C, E		Make/Buy
12	D, F		Buy
13
14
15	BOM
16						Usage
17	SKU
18		A				1
19			B			1
20				D		2
21			C			2
22				E		2
23					F	2
24
25
26	Order
27	SKU	10
Sheet4