Need help to find the reason for error

[RAJESH1960]

Hello everyone,
I have created this application with the help of formulas and VBA code. There are 6 code buttons to get the result which have to be pressed in the same order mentioned in column A. When I press all the buttons one by one, I am getting the result. I am facing 2 problems here and both are connected to the 2A Extract sheet.
In the column D of the 2A Extract sheet, this formula has to get the invoice numbers from B2B sheet of rows containing "-Total" after removing "-"Total" from the invoice number. But I am getting a few invoice number twice. That is one of the problems.

=IFERROR(IFERROR(VALUE(SUBSTITUTE(INDEX('2A'!$C$7:$C$10000,AGGREGATE(15,6,ROW($A$1:$A$9935)/(RIGHT('2A'!$C$7:$C$10000,5)="Total"),ROWS($1:1))),"-Total","")),SUBSTITUTE(INDEX(B2B!$C$7:$C$5963,AGGREGATE(15,6,ROW($A$1:$A$5927)/(RIGHT(B2B!$C$7:$C$5963,5)="Total"),ROWS($1:1))),"-Total","")),"")

Again, in the 2A Extract sheet invoice value column the problem is it is not getting the invoice value in some rows. Rows 73 & 74.
I need your help to solve these 2 issues.
PS: Run the code buttons one by one to get the data displayed.

Errors pending to correct.xlsm

 

[Anthony47]

Could you explain how we may recreate the error?

 

[RAJESH1960]

Could you explain how we may recreate the error?

I have written in cell D1. Delete the 2 sheets GSTIN Verified and Portal and rest of the sheets to delete as mentioned in the button in each sheet. I haven't yet written the code to clear old data. Once this gets working then I will try to write the clear data code.

 

[RAJESH1960]

You can insert this in a new module to clear old data to re check the code. Assign macro to the button in the Code Buttons sheet first button with name Clear Old Data.

Option Explicit

Sub ClearOldData()
'
    Application.DisplayAlerts = False
    On Error Resume Next
'
     Sheets("GSTIN Verified").Delete
    Sheets("Portal").Delete
 
    With Sheets("2A")
        .Range("A7:X" & .Range("A" & .Rows.Count).End(xlUp).Row).ClearContents                     '   Erase A7:X range of data
    End With
    With Sheets("GSTIN_to_Verify")
        .Range("A2:B" & .Range("A" & .Rows.Count).End(xlUp).Row).ClearContents                     '   Erase A2:B range of data
    End With
    With Sheets("2A Extract")
        .Range("A3:AI" & .Range("A" & .Rows.Count).End(xlUp).Row).ClearContents                     '   Erase A3:AI range of data
    End With
    With Sheets("Not imported")
        .Range("A7:V" & .Range("A" & .Rows.Count).End(xlUp).Row).ClearContents                     '   Erase A7:V range of data
    End With
    Application.DisplayAlerts = True
End Sub

 

[Anthony47]

You didn't get any suggestions in "hours", so I guess the information is unclear; at least for me...
Anyway...

Delete GSTIN Verified sheet, Portal sheet +
clear as per instructions on button in the headings of each sheet

These 2 sheets are already missing, so I go on

Insert B2B sheet manually

This sheet is already in, so I go on

I understand that now I have to run Sub Create_2A, using the button
I did that. Now what is the error? Is the double "DSL/20200775" in C71 and C72? (for example)

 

[RAJESH1960]

You didn't get any suggestions in "hours", so I guess the information is unclear; at least for me...
Anyway...

These 2 sheets are already missing, so I go on


This sheet is already in, so I go on

I understand that now I have to run Sub Create_2A, using the button
I did that. Now what is the error? Is the double "DSL/20200775" in C71 and C72? (for example)

Only if you run the code you will get the whole data on display. In the 2A extract sheet you can find some of rows with the errors that is when you have run all the macro buttons.

 

[RAJESH1960]

If you want I can send a video recording of the steps

 

[Anthony47]

In the 2A extract sheet you can find some of rows with the errors

WHICH IS the error?
No need for video, for now

 

[RAJESH1960]

You didn't get any suggestions in "hours",

I wonder too why nobody is answering. I have tried to explain it as simple as possible. At least you replied. Thanks for that. This is the image where the errors are showing.

WHICH IS the error?
No need for video, for now

 

[RAJESH1960]

Check the Invoice No. & Voucher No. columns. They are supposed to show one Invoice number only and they are displaying the same invoice number twice. Now don't ask me why. I am baffled and trying to find out since the last 36 hours and I couldn't find the mistake. So I posted this in the forum.