VBA Help - Find Text Value, Insert Rows, Multiply Values based on %

[roseinsydney]

Hoping for an Excel VBA genius to help me with a macro project (fingers crossed)!!

Dataset: column C contains cost centres. If the cost centre strings = WH or TSG, break down into further cost centres based on percentage.

Taking "WH" as example, usually Ctrl+F to find "WH", then insert 13 lines, manually copy the new cost centres into column C, and multiply the amount by % using a ROUND formula for 2 decimal places.

To avoid any $0.01 rounding issues for the last multiplication (in this case QSDW) subtract the calculations so far from original amount.

Ctrl+D to copy down the value in Column A and B without changing it

Then ensure "WH" is deleted from the list once it has been updated with the broken down cost centres.

Repeat!

Complexities:

• WH (or TSG, etc) can be located at any row in Column C, for an undefined amount of times (say maximum 10) so code would need to loop, then move on once all replaced
• WH will always require 13 sub-levels, other codes (eg. TSG) require 8.

Book1
	A	B	C	D	E	F	G	H	I	J
1	50400	AP	CITG	73,977.00		WH	%		TSG	%
2	50400	AS	BRNI	52,480.00		ACDW	0.007		BVDC	0.024
3	50400	AS	CDGM	64,595.00		BVDW	0.031		CIDC	0.057
4	50400	AU	BRNA	41,984.00		CIDW	0.044		CSDC	0.236
5	50400	AU	BRNE	16,177.00		CSDW	0.06		DIHC	0.506
6	50400	AU	BRNF	19,572.00		DIHW	0.233		GXDC	0.049
7	50400	AU	BRNH	21,896.00		FSDW	0.011		PFDC	0.032
8	50400	AU	BRNI	23,660.00		GXDW	0.093		PSDC	0.03
9	50400	AU	BRNO	35,459.00		PFDW	0.012		QLBC	0.066
10	50400	AU	BRNQ	65,892.00		PFEW	0.006
11	50400	AU	BRNR	23,207.00		PSDW	0.094
12	50400	AU	CDGD	33,223.00		PSPW	0.012
13	50400	AU	CDGM	47,245.00		QLBW	0.1
14	50400	AU	CDGP	11,852.00		QSDW	0.297
15	50400	AU	CDGT	34,609.00
16	50400	AU	LSGD	30,120.00
17	50400	AU	LSGM	2,057.00
18	50400	AU	LSGP	78,303.00
19	50400	AU	LSGT	50,900.00
20	50400	CRP	BRNZ	52,739.00
21	50400	AU	WH	40,265.00
22	50415	AU	BRNO	72,934.00
23	50415	AU	WH	30,344.00
24	50420	AU	BRNF	2,195.00
25	50420	AU	CDGM	21,241.00
26	50420	AU	TSG	59,620.00
27	50440	AU	WH	77,334.00
28	50444	AS	CDGM	2,151.00
29	50444	AU	BRNE	33,290.00
30	50444	AU	BRNF	44,023.00
31	50444	AU	BRNH	14,261.00
32	50444	AU	BRNQ	32,593.00
33	50444	AU	BRNR	47,410.00
34	50444	AU	CDGD	65,344.00
35	50444	AU	CDGM	24,516.00
36	50444	AU	CDGP	52,749.00
37	50444	AU	LSGD	30,818.00
38	50444	AU	LSGM	28,777.00
39	50444	AU	LSGP	35,531.00
40	50444	AU	TSG	33,328.00
41	50444	AU	WH	28,998.00
42	50448	AU	TSG	28,981.00
43	50600	AP	CITG	49,471.00
44	50600	AS	BRNI	16,860.00
45	50600	AS	CDGM	39,369.00
46	50600	AU	BRNA	69,235.00
47	50600	AU	BRNE	50,355.00
48	50600	AU	BRNF	57,247.00
49	50600	AU	BRNH	19,228.00
50	50600	AU	BRNI	23,121.00
51	50600	AU	BRNO	22,752.00
52	50600	AU	BRNQ	79,603.00
53	50600	AU	BRNR	16,169.00
54	50600	AU	CDGD	54,267.00
55	50600	AU	CDGM	65,565.00
56	50600	AU	CDGP	25,930.00
57	50600	AU	CDGT	8,641.00
58	50600	AU	LSGD	51,692.00
59	50600	AU	LSGM	5,671.00
60	50600	AU	LSGP	37,474.00
61	50600	AU	LSGT	14,607.00
62	50600	CRP	BRNZ	70,420.00
63	50600	AU	TSG	55,966.00
64	50600	AU	WH	35,974.00
65
66	Example: Row above broken down
67	50600	AU	ACDW	251.82
68	50600	AU	BVDW	1,115.19
69	50600	AU	CIDW	1,582.86
70	50600	AU	CSDW	2,158.44
71	50600	AU	DIHW	8,381.94
72	50600	AU	FSDW	395.71
73	50600	AU	GXDW	3,345.58
74	50600	AU	PFDW	431.69
75	50600	AU	PFEW	215.84
76	50600	AU	PSDW	3,381.56
77	50600	AU	PSPW	431.69
78	50600	AU	QLBW	3,597.40
79	50600	AU	QSDW	10,684.28
Macro