Row delete macro is effecting record count formula and vlookup code. #REF!

I have created some VB code for deleting a row (See attached Equipment Return Log, on ReturnData sheet. Clicking Delete Row will remove the row containing an active cell). However when a row gets deleted it effects my A column formula which list the record number with the following formula:
=IFERROR(IF($D7="", " ", $A6+1), "")

When the row is deleted, the A cell formula in the row below changes to:
=IFERROR(IF($D7="", " ",#REF!+ 1), "")
and no longer displays the record number for any rows below.

I tried changing this formula to the following code (attachment Equipment Return Log2):
=IFERROR(IF($D7="","",INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1),"")

While this fixes the issue of the record number not working when the records are deleted it prevents some vlookup code from working on the Search sheet. If you go down to row 334, (which contains the last record from the ReturnData sheet) it starts adding blank records. When using the delete record macro the A column on ReturnData sheet now appears to correctly list all the record numbers below, however on the Search sheet a blank row gets left behind where that record used to be, and the vlookup formula on that blank row gets changed to:
=IFERROR(VLOOKUP(ReturnData!#REF!,ReturnData!$B$6:$K$49972, 3, FALSE),"")

Im not sure if the issue is with the record number forumla on the A column, the vlookup formula on the Search page, or both. And I dont see how the Column A formula on ReturnData! is effecting the vlookup formula on Search! since the vlookup code only references columns D to K.

Thank you for your time,
James

Use index or address formula to over come your problem

Thank you for your responses.

nflsales, im not to familiar with index and address formulas. Im assuming that would help get around the #REF! issue. How could I change the vlookup formula to get around that?

sktneer, that record count formula seems to have the same effect as the one I have on Log2. It handles deleted rows fine, but it causes the vlookup on the Search page to created blank records with 0's in every cell (after the last record is displayed).

Im guessing both of these issues are related to the vlookup formula.
Any thoughts?
Thanks,
James

sktneer, I tried the new lookup formula (along with your row count formula), but every record in the search page just comes back as 0 for each cell.
See attached workbook.

For some reason the vlookup formula isnt copying down to the cells below correctly. The A6 isnt counting up, it just remains A6 (so each row comes back with the same data) I tried adding a $ infront of the Letter, but that didn't help. I then tried manually entering it for the first three rows, and then copying that down, but it just kept repeating A6, A7, A8 over and over again. Manually entering the entire page would take forever!

I deleted row 7 (since I have rows 6-8 with the correct data on the search) and it seemed to react correctly on the search page. But some of the issues I was having with the previous code was that it was adding blank records (with 0's) to the end of the search results. So without filling that code on the search page its hard to say for sure, but its looking promising.

Is this what C11 in Search should be? It came back blank:

=IFERROR(VLOOKUP(INDIRECT(INDIRECT("ReturnData!A"&ROW()-5)),ReturnData!$B$6:$K$49970, 3, FALSE),"")

Sorry, wasn't thinking straight, the following code returned the correct results:
=IFERROR(VLOOKUP(INDIRECT("ReturnData!A"&ROW()-5),ReturnData!$B$6:$K$49970, 3, FALSE),"")

It handles deleted records just fine now (thank you). The only issue as I suspected is it still creates blank records with 0's in each cell. See attached workbook. The Search sheet correctly displays all 317 records, then on line 328 it starts creating blank records.

I made some development. I went into the preferences and disabled show zeros, which removed all the blank records it was creating. So it now handles deleted records correctly, and does not display blank records (with zeros) anymore. However as soon as I enter a search criteria on the search page, it displays the first record on the Return Data page over and over again from row 328 (with no search criteria entered the last record, record 317 finishes at row 327).

I have attached the workbook. With no criteria you will see the last record on the search page on row 327. If you hit the TODAY macro at the top (which will return no results since there are no records entered today), the first record will be duplicated from row 328 on. How can I fix this?

Thanks,
James