Tracing sum cells up to match with cell in last row for another sheet

[abdo meghari]

Hello

I have INVOICE sheet contains TOTAL amount in last row for column E should match with column C in ACCOUNT sheet after summing .

when sum the amounts for column C should stop from summing at lastrow match with same amount in INVOICE sheet .when reach for the same amount in last row for column E in INVOICE sheet after that copy summing amount from column C and put in in column F for last cell ACCOUNT sheet.

ورقة عمل Microsoft Excel جديد.xlsx
	A	B	C	D	E
21	ITEM	BRAND	QTY	PRICE	BALANCE
22	1	VEGA 70A R KOR	4.00	500.00	2,000.00
23	2	VEGA 55A R KOR	2.00	425.00	850.00
24	3	LARGEST 60A L HIGH JAP	2.00	440.00	880.00
25	4	KM 215/65R16 TA31 KOR	4.00	460.00	1,840.00
26	5	265/70R16 ALGERIA	4.00	630.00	2,520.00
27	6	KM 205/65R16 HS63 KOR	4.00	445.00	1,780.00
28	7	GC 385/65R22.5 AT131 CHI	2.00	1,650.00	3,300.00
29	8	KM 265/70R16 KOR	4.00	880.00	3,520.00
30	9	DONGA 66A L KOR	2.00	460.00	920.00
31	10	KM 235/65R17 HP71 KOR	4.00	625.00	2,500.00
32	11	KM 235/55R19 PS71 KOR	4.00	675.00	2,700.00
33	12	DROUB 90A L KOR	1.00	575.00	575.00
34	13	KM 13R22.5 MA11 KOR	2.00	2,550.00	5,100.00
35	TOTAL		39.00		28,485.00
INVOICE

Cell Formulas
Range	Formula
C35,E35	C35	=SUM(C22:C34)
E22:E34	E22	=D22*C22

ورقة عمل Microsoft Excel جديد.xlsx
	A	B	C	D	E	F
1	DATE	INFO	DEBIT	CREDIT	BALANCE	NOTE
2	01/11/2024	SALES INVOICE NO BSJ2000	2,000.00		2,000.00
3	02/11/2024	SALES INVOICE NO BSJ2001	3,000.00		5,000.00
4	03/11/2024	SALES RETURNS INVOICE NO BSJ789		3,000.00	2,000.00
5	03/11/2024	VOUCHER NO VBGH6788		1,500.00	500.00
6	04/11/2024	SALES INVOICE NO BSJ2003	15,000.00		15,500.00
7	04/11/2024	SALES INVOICE NO BSJ2004	8,485.00		23,985.00
8	05/11/2024	SALES INVOICE NO BSJ2005	12,000.00		35,985.00
9	06/11/2024	SALES INVOICE NO BSJ2006	12,000.00		47,985.00
ACCOUNT

Cell Formulas
Range	Formula
E2	E2	=C2-D2
E3,E5:E9	E3	=E2+C3-D3
E4	E4	=E3+CC104-D4

result

ورقة عمل Microsoft Excel جديد.xlsx
	A	B	C	D	E	F
1	DATE	INFO	DEBIT	CREDIT	BALANCE	NOTE
2	01/11/2024	SALES INVOICE NO BSJ2000	2,000.00		2,000.00
3	02/11/2024	SALES INVOICE NO BSJ2001	3,000.00		5,000.00
4	03/11/2024	SALES RETURNS INVOICE NO BSJ789		3,000.00	2,000.00
5	03/11/2024	VOUCHER NO VBGH6788		1,500.00	500.00
6	04/11/2024	SALES INVOICE NO BSJ2003	15,000.00		15,500.00
7	04/11/2024	SALES INVOICE NO BSJ2004	8,485.00		23,985.00	28,485.00
8	05/11/2024	SALES INVOICE NO BSJ2005	12,000.00		35,985.00
9	06/11/2024	SALES INVOICE NO BSJ2006	12,000.00		47,985.00
ACCOUNT

Cell Formulas
Range	Formula
E2	E2	=C2-D2
E3,E5:E9	E3	=E2+C3-D3
E4	E4	=E3+CC104-D4

if write another process

ورقة عمل Microsoft Excel جديد.xlsx
	A	B	C	D	E
21	ITEM	BRAND	QTY	PRICE	BALANCE
22	1	VEGA 70A R KOR	4.00	500.00	2,000.00
23	4	KM 215/65R16 TA31 KOR	4.00	460.00	1,840.00
24	5	265/70R16 ALGERIA	4.00	630.00	2,520.00
25	7	GC 385/65R22.5 AT131 CHI	2.00	1,650.00	3,300.00
26	8	KM 265/70R16 KOR	4.00	1,010.00	4,040.00
27	10	KM 235/65R17 HP71 KOR	4.00	625.00	2,500.00
28	11	KM 235/55R19 PS71 KOR	4.00	675.00	2,700.00
29	13	KM 13R22.5 MA11 KOR	2.00	2,550.00	5,100.00
30	TOTAL		28.00		24,000.00
INVOICE

Cell Formulas
Range	Formula
C30,E30	C30	=SUM(C22:C29)
E22:E29	E22	=D22*C22

result

ورقة عمل Microsoft Excel جديد.xlsx
	A	B	C	D	E	F
1	DATE	INFO	DEBIT	CREDIT	BALANCE	NOTE
2	01/11/2024	SALES INVOICE NO BSJ2000	2,000.00		2,000.00
3	02/11/2024	SALES INVOICE NO BSJ2001	3,000.00		5,000.00
4	03/11/2024	SALES RETURNS INVOICE NO BSJ789		3,000.00	2,000.00
5	03/11/2024	VOUCHER NO VBGH6788		1,500.00	500.00
6	04/11/2024	SALES INVOICE NO BSJ2003	15,000.00		15,500.00
7	04/11/2024	SALES INVOICE NO BSJ2004	8,485.00		23,985.00	28,485.00
8	05/11/2024	SALES INVOICE NO BSJ2005	12,000.00		35,985.00
9	06/11/2024	SALES INVOICE NO BSJ2006	12,000.00		47,985.00	24,000.00
ACCOUNT

Cell Formulas
Range	Formula
E2	E2	=C2-D2
E3,E5:E9	E3	=E2+C3-D3
E4	E4	=E3+CC104-D4

always when sum for column C will match lastrow in column E for INVOICE sheet. for some reason if it's not matched for the same amount then shouldn't show any thing in column F for ACCOUNT sheet.
every time when try sum amount in column C will start from after amount is existed in column F , but from first time will start from row2.
thanks

 

[DanteAmor]

Try this

Sub Tracing_sum()
  Dim tot As Double, dbt As Double
  Dim i As Long
  
  tot = Sheets("INVOICE").Range("E" & Rows.Count).End(3).Value
  With Sheets("ACCOUNT")
    For i = .Range("F" & Rows.Count).End(3).Row + 1 To .Range("E" & Rows.Count).End(3).Row
      dbt = dbt + .Range("C" & i).Value
      If dbt = tot Then
        .Range("F" & i).Value = tot
        Exit For
      End If
    Next
  End With
End Sub

 

[abdo meghari]

perfect !
many thanks Dante.