add missed headers for different location based on above rows

[abdo meghari]

Hi
I have sheet contains invoices details but some rows don't contain headers(TOTAL,DATE,INVOICE NO,PRICE,QTY,ID NO,DESCRIBE)
so I would macro to add the headers based on formatting row where precede it.
bfore rows 25,54

arranging data.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	BRIDD
2
3	Analytical detection of a client's movement
4
5
6
7	2024.05.08
8
9
10	NAME: ABSI
11
12
13	MOVEMENT TYPE :SELLING
14								PAGE NO (1 ) TO (2)
15	TOTAL					DATE		INVOICE NO	PRICE					QTY	ID NO					DESCRIBE
16	1,700.00					2024.01.09		507	425					4	1390					KM 185/70R14 TA21 KOR
17
18
19	TOTAL					DATE		INVOICE NO	PRICE					QTY	ID NO					DESCRIBE
20	3,965.00					2024.01.10		511	750					4	1394					DUNLOP 265/60R18 AT22 JAP
21									475					1	1392					CRESTAL 90A L KOR
22									490					1	1367					CRESTAL 100A KOR
23
24
25
26	2,550.00					2024.01.13		515	475					2	1392					CRESTAL 90A L KOR
27									500					2	1348					DONGA 100 A KOR
28									600					1	1395					BATTERY 88A KORE
29
30
31	TOTAL					DATE		INVOICE NO	PRICE					QTY	ID NO					DESCRIBE
32	3,400.00					2024.01.14		520	680					5	1371					DUNLOP 265/65R17 AT20 JAP
33
34	NAME: ABSI							PAGE NO (2 ) TO (2)
35
36	TOTAL			DATE		INVOICE NO	PRICE			QTY	ID NO		DESCRIBE
37	1,090.00			2024.01.23		558	545			2	1412		MARSHAL 245/70R17 KL61KOR
38
39
40	TOTAL			DATE		INVOICE NO	PRICE			QTY	ID NO		DESCRIBE
41	3,400.00			2024.01.23		559	850			4	1411		BS 275/55R20 ALENZA1 JAP
42
43
44	TOTAL			DATE		INVOICE NO	PRICE			QTY	ID NO		DESCRIBE
45	5,940.00			2024.01.25		562	425			4	1414		GOODRIDE 255/70R15 CHI
46							1,060.00			4	1417		CONTINENTAL 255/45R19 SC5GER
47
48
49	MOVEMENT TYPE :SELLING RETURNING
50
51	TOTAL				DATE		INVOICE NO	PRICE			QTY	ID NO		DESCRIBE
52	3,000.00				2024.01.28		111	750			4	1394		DUNLOP 265/60R18 AT22 JAP
53
54
55	425.00				2024.01.28		112	425			1	1414		GOODRIDE 255/70R15 CHI
KS

after for row 25,54

arranging data.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	BRIDD
2
3	Analytical detection of a client's movement
4
5
6
7	2024.05.08
8
9
10	NAME: ABSI
11
12
13	MOVEMENT TYPE :SELLING
14								PAGE NO (1 ) TO (2)
15	TOTAL					DATE		INVOICE NO	PRICE					QTY	ID NO					DESCRIBE
16	1,700.00					2024.01.09		507	425					4	1390					KM 185/70R14 TA21 KOR
17
18
19	TOTAL					DATE		INVOICE NO	PRICE					QTY	ID NO					DESCRIBE
20	3,965.00					2024.01.10		511	750					4	1394					DUNLOP 265/60R18 AT22 JAP
21									475					1	1392					CRESTAL 90A L KOR
22									490					1	1367					CRESTAL 100A KOR
23
24
25	TOTAL					DATE		INVOICE NO	PRICE					QTY	ID NO					DESCRIBE
26	2,550.00					2024.01.13		515	475					2	1392					CRESTAL 90A L KOR
27									500					2	1348					DONGA 100 A KOR
28									600					1	1395					BATTERY 88A KORE
29
30
31	TOTAL					DATE		INVOICE NO	PRICE					QTY	ID NO					DESCRIBE
32	3,400.00					2024.01.14		520	680					5	1371					DUNLOP 265/65R17 AT20 JAP
33
34	NAME: ABSI							PAGE NO (2 ) TO (2)
35
36	TOTAL			DATE		INVOICE NO	PRICE			QTY	ID NO		DESCRIBE
37	1,090.00			2024.01.23		558	545			2	1412		MARSHAL 245/70R17 KL61KOR
38
39
40	TOTAL			DATE		INVOICE NO	PRICE			QTY	ID NO		DESCRIBE
41	3,400.00			2024.01.23		559	850			4	1411		BS 275/55R20 ALENZA1 JAP
42
43
44	TOTAL			DATE		INVOICE NO	PRICE			QTY	ID NO		DESCRIBE
45	5,940.00			2024.01.25		562	425			4	1414		GOODRIDE 255/70R15 CHI
46							1,060.00			4	1417		CONTINENTAL 255/45R19 SC5GER
47
48
49	MOVEMENT TYPE :SELLING RETURNING
50
51	TOTAL				DATE		INVOICE NO	PRICE			QTY	ID NO		DESCRIBE
52	3,000.00				2024.01.28		111	750			4	1394		DUNLOP 265/60R18 AT22 JAP
53
54	TOTAL				DATE		INVOICE NO	PRICE			QTY	ID NO		DESCRIBE
55	425.00				2024.01.28		112	425			1	1414		GOODRIDE 255/70R15 CHI
KS

 

[JGordon11]

try this on a copy of your worksheet

Sub AddHeaders()
    Dim rw As Long, col As Long, a, ckrow As Long, found As Boolean
    a = ActiveSheet.UsedRange
    For rw = 15 To UBound(a, 1)
        For col = 1 To UBound(a, 2)
            If (IsNumeric(a(rw, col)) Or IsNumeric(Left(a(rw, col), 6))) And Len(a(rw, col)) > 0 And Len(a(rw - 1, col)) = 0 Then
                ckrow = rw - 2
                found = False
                Do While ckrow > 14 And Not found
                    If Len(a(ckrow, col)) > 0 And Not (IsNumeric(a(ckrow, col)) Or IsNumeric(Left(a(ckrow, col), 6))) Then
                        found = True
                        Cells(rw - 1, col) = a(ckrow, col)
                    End If
                    ckrow = ckrow - 1
                Loop
            End If
        Next
    Next
    For rw = 15 To UBound(a, 1)
        For col = UBound(a, 2) To 6 Step -1
            If Len(a(rw, col)) > 0 And Len(a(rw - 1, col)) = 0 Then
                If a(rw, col) <> "DESCRIBE" And Left(a(rw, col), 4) <> "PAGE" Then Cells(rw - 1, col) = "DESCRIBE"
                Exit For
            End If
        Next
    Next
End Sub

 

[abdo meghari]

thanks
just I note if I have PAGE NO.... and NAME:... before detail doesn't contain header will fill NAME and PAGE NO !!
like this

arranging data.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
36	TOTAL			DATE		INVOICE NO	PRICE			QTY	ID NO		DESCRIBE
37	1,090.00			2024.01.23		558	545			2	1412		MARSHAL 245/70R17 KL61KOR
38
39	NAME: ABSI						PAGE NO (2 ) TO (2)
40
41	3,400.00			2024.01.23		559	850			4	1411		BS 275/55R20 ALENZA1 JAP
KS

error result as highlighted(this case is rare for my real project)

arranging data.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
36	TOTAL			DATE		INVOICE NO	PRICE			QTY	ID NO		DESCRIBE
37	1,090.00			2024.01.23		558	545			2	1412		MARSHAL 245/70R17 KL61KOR
38
39	NAME: ABSI						PAGE NO (2 ) TO (2)
40	NAME: ABSI			DATE		INVOICE NO	PAGE NO (2 ) TO (2)			QTY	ID NO		DESCRIBE
41	3,400.00			2024.01.23		559	850			4	1411		BS 275/55R20 ALENZA1 JAP
KS

I hope there is way to fix it by exclude (NAME & PAGE NO).

 

[JGordon11]

Sub AddHeaders()
    Dim rw As Long, col As Long, a, ckrow As Long, found As Boolean
    a = ActiveSheet.UsedRange
    For rw = 15 To UBound(a, 1)
        For col = 1 To UBound(a, 2)
            If (IsNumeric(a(rw, col)) Or IsNumeric(Left(a(rw, col), 6))) And Len(a(rw, col)) > 0 And Len(a(rw - 1, col)) = 0 Then
                ckrow = rw - 2
                found = False
                Do While ckrow > 14 And Not found
                    If Len(a(ckrow, col)) > 0 And Not (IsNumeric(a(ckrow, col)) Or IsNumeric(Left(a(ckrow, col), 6))) Then
                        If Left(a(ckrow, col), 4) <> "NAME" And Left(a(ckrow, col), 4) <> "PAGE" Then
                            found = True
                            Cells(rw - 1, col) = a(ckrow, col)
                        End If
                    End If
                    ckrow = ckrow - 1
                Loop
            End If
        Next
    Next
    For rw = 15 To UBound(a, 1)
        For col = UBound(a, 2) To 6 Step -1
            If Len(a(rw, col)) > 0 And Len(a(rw - 1, col)) = 0 Then
                If a(rw, col) <> "DESCRIBE" And Left(a(rw, col), 4) <> "PAGE" Then Cells(rw - 1, col) = "DESCRIBE"
                Exit For
            End If
        Next
    Next
End Sub

 

[abdo meghari]

Great !
thank you so much.