Insert Row

[vmjan02]

I have a sheet by name "Headers" and the range from AA2 to AA25 so need to check from this list if any of the missing records found in sheet "FinalReport" from B4 to B28 and if found the vba will insert a row

eg:- now missing record for 5 so now the script will search 4 in FinalReport and then will insert a row after it find the 4 and after insert it i will do the average of 3 & 4

any way to do this. it is taking around 2 hr to do the task manually now.

CST_NW_District_Automation_V1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
4	2024-10-08	7.00	99.35	99.74	100.00	99.21	100.00	100.00	100.00	99.40	100.00	94.98	N/A
5	2024-10-08	8.00	99.35	99.81	100.00	99.23	100.00	100.00	100.00	100.00	100.00	100.00	N/A
6	2024-10-08	9.00	99.26	99.71	100.00	99.23	100.00	100.00	100.00	100.00	100.00	100.00	N/A
7	2024-10-08	10.00	99.31	99.67	100.00	97.59	100.00	99.92	100.00	100.00	100.00	100.00	N/A
8	2024-10-08	11.00	99.35	99.76	100.00	98.56	100.00	100.00	100.00	100.00	100.00	100.00	N/A
9	2024-10-08	12.00	99.35	99.77	100.00	98.78	100.00	100.00	100.00	100.00	100.00	100.00	N/A
10	2024-10-08	13.00	99.35	99.82	100.00	98.79	100.00	100.00	100.00	100.00	100.00	100.00	N/A
11	2024-10-08	14.00	99.35	99.82	100.00	98.62	100.00	100.00	100.00	100.00	100.00	100.00	N/A
12	2024-10-08	15.00	99.35	99.83	100.00	99.20	100.00	100.00	100.00	100.00	100.00	100.00	N/A
13	2024-10-08	16.00	99.35	99.82	100.00	99.23	100.00	100.00	100.00	100.00	100.00	100.00	N/A
14	2024-10-08	17.00	99.45	99.83	100.00	99.23	100.00	100.00	100.00	100.00	100.00	100.00	N/A
15	2024-10-08	18.00	99.45	99.82	100.00	99.23	100.00	100.00	100.00	100.00	100.00	100.00	N/A
16	2024-10-08	19.00	99.45	99.81	100.00	99.23	100.00	100.00	100.00	100.00	100.00	100.00	N/A
17	2024-10-08	20.00	99.45	99.80	100.00	99.13	100.00	100.00	100.00	100.00	100.00	100.00	N/A
18	2024-10-08	21.00	99.45	99.73	100.00	98.20	100.00	100.00	100.00	100.00	100.00	100.00	N/A
19	2024-10-08	22.00	99.45	99.61	100.00	98.09	100.00	100.00	100.00	100.00	100.00	100.00	N/A
20	2024-10-08	23.00	99.45	98.76	100.00	92.42	100.00	100.00	100.00	100.00	100.00	100.00	N/A
21	2024-10-09	0.00	99.45	98.77	100.00	92.45	100.00	100.00	100.00	100.00	100.00	100.00	N/A
22	2024-10-09	1.00	99.45	98.77	100.00	92.45	100.00	100.00	100.00	100.00	100.00	100.00	N/A
23	2024-10-09	2.00	99.45	98.85	100.00	92.98	100.00	100.00	100.00	100.00	100.00	100.00	N/A
24	2024-10-09	3.00	99.36	99.83	100.00	99.23	100.00	100.00	100.00	100.00	100.00	100.00	N/A
25	2024-10-09	4.00	99.35	99.84	100.00	99.23	100.00	100.00	100.00	100.00	100.00	100.00	N/A
26	2024-10-09	6.00	99.35	99.71	100.00	99.23	100.00	100.00	100.00	100.00	100.00	100.00	N/A
FinalReport

CST_NW_District_Automation_V1.xlsm
	AA
2	0.00
3	1.00
4	2.00
5	3.00
6	4.00
7	5.00
8	6.00
9	7.00
10	8.00
11	9.00
12	10.00
13	11.00
14	12.00
15	13.00
16	14.00
17	15.00
18	16.00
19	17.00
20	18.00
21	19.00
22	20.00
23	21.00
24	22.00
25	23.00
Headers

 

[RobP]

thanks - but I still don't know how you have formatted your column B (FinalReport) and column AA (Headers) to know why my code breaks only on your machine. Please advise on that. and also how these numbers are generated ? did you type them in manually ?

also, when you say the formula averages the previous 2 rows - I think what you mean is it will average the previous 2 cells in the same column ? Is it correct please ?

Rob

 

[RobP]

In addition to my above request, I updated my code to insert the values (average of 2 cells above each column) or N/A if applicable.

So on my testing with your data (I cannot see the format as I said above), it generates this result for example when 5 was missing :
eg. So cell C26 is the average of cells C24 + C25, D26 is average of D24 + D25 etc.

insert_row.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4	08/10/2024	7	99.35	99.74	100	99.21	100	100	100	99.4	100	94.98	N/A
5	08/10/2024	8	99.35	99.81	100	99.23	100	100	100	100	100	100	N/A
6	08/10/2024	9	99.26	99.71	100	99.23	100	100	100	100	100	100	N/A
7	08/10/2024	10	99.31	99.67	100	97.59	100	99.92	100	100	100	100	N/A
8	08/10/2024	11	99.35	99.76	100	98.56	100	100	100	100	100	100	N/A
9	08/10/2024	12	99.35	99.77	100	98.78	100	100	100	100	100	100	N/A
10	08/10/2024	13	99.35	99.82	100	98.79	100	100	100	100	100	100	N/A
11	08/10/2024	14	99.35	99.82	100	98.62	100	100	100	100	100	100	N/A
12	08/10/2024	15	99.35	99.83	100	99.2	100	100	100	100	100	100	N/A
13	08/10/2024	16	99.35	99.82	100	99.23	100	100	100	100	100	100	N/A
14	08/10/2024	17	99.45	99.83	100	99.23	100	100	100	100	100	100	N/A
15	08/10/2024	18	99.45	99.82	100	99.23	100	100	100	100	100	100	N/A
16	08/10/2024	19	99.45	99.81	100	99.23	100	100	100	100	100	100	N/A
17	08/10/2024	20	99.45	99.8	100	99.13	100	100	100	100	100	100	N/A
18	08/10/2024	21	99.45	99.73	100	98.2	100	100	100	100	100	100	N/A
19	08/10/2024	22	99.45	99.61	100	98.09	100	100	100	100	100	100	N/A
20	08/10/2024	23	99.45	98.76	100	92.42	100	100	100	100	100	100	N/A
21	09/10/2024	0	99.45	98.77	100	92.45	100	100	100	100	100	100	N/A
22	09/10/2024	1	99.45	98.77	100	92.45	100	100	100	100	100	100	N/A
23	09/10/2024	2	99.45	98.85	100	92.98	100	100	100	100	100	100	N/A
24	09/10/2024	3	99.36	99.83	100	99.23	100	100	100	100	100	100	N/A
25	09/10/2024	4	99.35	99.84	100	99.23	100	100	100	100	100	100	N/A
26	09/10/2024	5	99.355	99.835	100	99.23	100	100	100	100	100	100	N/A
27	09/10/2024	6	99.35	99.71	100	99.23	100	100	100	100	100	100	N/A
28
29
FinalReport

The data in Column B (and Column AA of Headers) for me are just numbers formatted as General.

Public chdr, cdata, x, y, lr, hlr As Long
Public Adata, Aheaders As Variant

Sub missing_headers()

lr = Sheets("FinalReport").Cells(Rows.Count, "A").End(xlUp).Row
hlr = Sheets("Headers").Cells(Rows.Count, "AA").End(xlUp).Row

ReDim Adata(4 To lr) 'array now mirrors row numbers starting row4

Sheets("Headers").Select 'load the array with Header numbers  NB. Header No's must be in sequence on Headers sheet..
Aheaders = Sheets("Headers").Range(Cells(2, 27), Cells(hlr, 27)).Value '27 = Col "AA"

Sheets("FinalReport").Select 'load the array with FinalReport data
For x = 4 To lr
    Adata(x) = Sheets("FinalReport").Range("B" & x).Value
Next x

For chdr = UBound(Aheaders) To 1 Step -1  'count through header numbers backwards
    found = 0
    For cdata = UBound(Adata) To 4 Step -1  'check through data values backwards
        If Aheaders(chdr, 1) = Adata(cdata) Then
            found = 1
            Exit For
        End If
    Next cdata
    
    ' Header is missing, act on it ..
    If found = 0 Then
        For cdata = lr To 4 Step -1
            If Aheaders(chdr - 1, 1) = Adata(cdata) Then
                Sheets("FinalReport").Cells(cdata + 1, 1).EntireRow.Insert
                Sheets("FinalReport").Cells(cdata + 1, 1) = Sheets("FinalReport").Cells(cdata, 1) 'add date
                Sheets("FinalReport").Cells(cdata + 1, 2) = Aheaders(chdr, 1) 'add header no.
                On Error Resume Next
                For y = 3 To 13
                    If IsError((Sheets("FinalReport").Cells(cdata - 1, y) + Sheets("FinalReport").Cells(cdata, y)) / 2) Then
                        Sheets("FinalReport").Cells(cdata + 1, y) = "N/A"
                        On Error GoTo 0
                    Else
                        Sheets("FinalReport").Cells(cdata + 1, y) = (Sheets("FinalReport").Cells(cdata - 1, y) + Sheets("FinalReport").Cells(cdata, y)) / 2
                    End If
                Next y
            End If
        Next cdata
    End If
Next chdr

End Sub

 

[vmjan02]

@RobP

this is perfect you nail it. thank you for saving hours of task. This is perfect

 

[RobP]

great .. thanks for the feedback

Glad we could help.

Rob