Loop value in column to find accumulate balance movement

[SamKhem]

Dear Senior member
Good morning. I would like request you to guide run vba excel with data by loop in code 100,000 rows then make add balance to column F from amount in column D when column E change accumulate balance as show below.

Book1
	B	C	D	E	F	G
1	CODE	REFERENCE	AMOUNT	ACCUMULATE BALANCE
2	66333			356,574.00		Balance start
3	66333	0MDHGZ18P	(77,000.00)	279,574.00
4	66333	0G67GJ0WJ	(20,000.00)	259,574.00
5	66333	08BJ9LHGB	(200,000.00)	59,574.00
6	66333	0B35FF34Y	412,600.00	472,174.00
7	66333	0G6KK61RJ	(200,000.00)	272,174.00
8	66333	0LPRG1RBZ	(16,500.00)	255,674.00	(16,500.00)
9	66333	0VSTQ8XDT	4,000,000.00	(3,744,326.00)
10	66333	14QC339LY	(4,127,000.00)	382,674.00	382,674.00
11	36058			(47,543.20)		Balance start
12	36058	04XMQGP6J	(1,500.00)	(49,043.20)
13	36058	05GL47DZB	450.05	(48,593.18)
14	36058	05C1T189N	2,397.48	(46,195.70)
15	36058	159MDR8PQ	50,630.20	4,434.50	4,434.50
16	36058	159M88ZPY	50.00	4,484.50	4,484.50
17	36058	0RQC3CZF7	(5,000.00)	(515.50)
18	36058	0PMZY7QYF	(484.50)	(1,000.00)
19	36058			1,000.00	1,000.00	Balance start
20	36058	04XMQGP6J	(1,500.00)	(500.00)
21	36058	159M88ZPY	(100.00)	(600.00)
Sheet1

Cell Formulas
Range	Formula
F8	F8	=D8
F10,F15:F16	F10	=E10

 

[bebo021999]

What columns are D,E,F?
Which cells are your desired results? (yellow cells?)
Try to post a minisheet with XL2BB

 

[SamKhem]

What columns are D,E,F?
Which cells are your desired results? (yellow cells?)
Try to post a minisheet with XL2BB

Yes please recheck. Please change amount F8=-16500 to 255,674 (F8 to E8). Thanks

 

[bebo021999]

What determines the display results in F8, F10, F15, F16, and F19, but not in other cells?
Once again, please upload minisheet (via XL2BB) then we have not to re-input again manually

 

[SamKhem]

Result in F column refer to change accumulate balance in column E from positive to negative or negative to positive.
Sorry please recheck.

Book1
	B	C	D	E	F	G
1	CODE	REFERENCE	AMOUNT	ACCUMULATE BALANCE
2	66333			356,574.00		Balance start
3	66333	0MDHGZ18P	(77,000.00)	279,574.00
4	66333	0G67GJ0WJ	(20,000.00)	259,574.00
5	66333	08BJ9LHGB	(200,000.00)	59,574.00
6	66333	0B35FF34Y	412,600.00	472,174.00
7	66333	0G6KK61RJ	(200,000.00)	272,174.00
8	66333	0LPRG1RBZ	(16,500.00)	255,674.00	255,674.00
9	66333	0VSTQ8XDT	4,000,000.00	(3,744,326.00)
10	66333	14QC339LY	(4,127,000.00)	382,674.00	382,674.00
11	36058			(47,543.20)		Balance start
12	36058	04XMQGP6J	(1,500.00)	(49,043.20)
13	36058	05GL47DZB	450.05	(48,593.18)
14	36058	05C1T189N	2,397.48	(46,195.70)
15	36058	159MDR8PQ	50,630.20	4,434.50	4,434.50
16	36058	159M88ZPY	50.00	4,484.50	4,484.50
17	36058	0RQC3CZF7	(5,000.00)	(515.50)
18	36058	0PMZY7QYF	(484.50)	(1,000.00)
19	36058			1,000.00	1,000.00	Balance start
20	36058	04XMQGP6J	(1,500.00)	(500.00)
21	36058	159M88ZPY	(100.00)	(600.00)
Sheet1

Cell Formulas
Range	Formula
F8,F19,F15:F16,F10	F8	=E8

 

[bebo021999]

Why F16, while F15 and F16 both are positive?

 

[SamKhem]

Why F16, while F15 and F16 both are positive?

refer E14 positive move from E15 negative and E16 positive move to E17 negative.

 

[bebo021999]

OK, try

Book1
	B	C	D	E	F
1	CODE	REFERENCE	AMOUNT	ACCUMULATE BALANCE
2	66333			356574
3	66333	0MDHGZ18P	-77000	279574
4	66333	0G67GJ0WJ	-20000	259574
5	66333	08BJ9LHGB	-200000	59574
6	66333	0B35FF34Y	412600	472174
7	66333	0G6KK61RJ	-200000	272174
8	66333	0LPRG1RBZ	-16500	255674	255674
9	66333	0VSTQ8XDT	4000000	-3744326
10	66333	14QC339LY	-4127000	382674	382674
11	36058			-47543.2
12	36058	04XMQGP6J	-1500	-49043.2
13	36058	05GL47DZB	450.05	-48593.18
14	36058	05C1T189N	2397.48	-46195.7
15	36058	159MDR8PQ	50630.2	4434.5	4434.5
16	36058	159M88ZPY	50	4484.5	4484.5
17	36058	0RQC3CZF7	-5000	-515.5
18	36058	0PMZY7QYF	-484.5	-1000
19	36058			1000	1000
20	36058	04XMQGP6J	-1500	-500
21	36058	159M88ZPY	-100	-600
Sheet2

Cell Formulas
Range	Formula
F2:F21	F2	=IFERROR(IF(E2<0,"",IF(OR(AND(B2=B3,SIGN(E2)+SIGN(E3)=0),AND(B2=B1,SIGN(E2)+SIGN(E1)=0)),E2,"")),"")

 

[SamKhem]

OK, try

Book1
	B	C	D	E	F
1	CODE	REFERENCE	AMOUNT	ACCUMULATE BALANCE
2	66333			356574
3	66333	0MDHGZ18P	-77000	279574
4	66333	0G67GJ0WJ	-20000	259574
5	66333	08BJ9LHGB	-200000	59574
6	66333	0B35FF34Y	412600	472174
7	66333	0G6KK61RJ	-200000	272174
8	66333	0LPRG1RBZ	-16500	255674	255674
9	66333	0VSTQ8XDT	4000000	-3744326
10	66333	14QC339LY	-4127000	382674	382674
11	36058			-47543.2
12	36058	04XMQGP6J	-1500	-49043.2
13	36058	05GL47DZB	450.05	-48593.18
14	36058	05C1T189N	2397.48	-46195.7
15	36058	159MDR8PQ	50630.2	4434.5	4434.5
16	36058	159M88ZPY	50	4484.5	4484.5
17	36058	0RQC3CZF7	-5000	-515.5
18	36058	0PMZY7QYF	-484.5	-1000
19	36058			1000	1000
20	36058	04XMQGP6J	-1500	-500
21	36058	159M88ZPY	-100	-600
Sheet2

Cell Formulas
Range	Formula
F2:F21	F2	=IFERROR(IF(E2<0,"",IF(OR(AND(B2=B3,SIGN(E2)+SIGN(E3)=0),AND(B2=B1,SIGN(E2)+SIGN(E1)=0)),E2,"")),"")

Perfect Worked. Thank for support.