I have created a simple row delete macro to delete the selected row with the following code:
How can i make it so that before deleting the code it comes up with a message box saying 'Are you sure you want to delete record number (then display the data in cell A on the same row as the active cell)'.
Please Login or Register to view this content.
Hi, James,
Ciao,Please Login or Register to view this content.
Holger
Use Code-Tags for showing your code: [code] Your Code here [/code]
Please mark your question Solved if there has been offered a solution that works fine for you
Thanks Holger, that did the trick.
However when I delete rows in the middle of my records it messes up my record count number in the A column. This is the formula its using:
=IFERROR(IF($D7="", " ", $A6+1), "")
However when I delete a record all the A cells below stop displaying the record number, because the immediate A cell below changes its formula to:
=IFERROR(IF($D7="", " ",#REF!+ 1), "")
Hi, James,
do you remember that I asked about pasting the values to the rows when the process is done? Maybe this is the reason why I asked.
You would either need to rewrite the formulas, alter them to use Indirect or use a UserDefinedFunction to relate to the cell above no matter if any row is deleted.
If you can work around by hiding the row instead of deleting it you would not need the other actions but just use
Ciao,Please Login or Register to view this content.
Holger
I need to delete them, just hiding it doesn't do the trick. How would I rewrite the formula so it doesn't have this issue? This is the current formula im using to work out the record number:
=IFERROR(IF($D7="", " ", $A6+1), "")
Hi, James,
for A7 please try
Formula:
Please Login or Register to view this content.
and drag that formula fdown which would match up the numbers without errors if that is what you are after.
Ciao,
Holger
That fixes the issue when deleting rows, however it causes another issue on the Search page. If you go down to row 317 on Search it starts introducing rows with 0's in each cell. I have had this problem a few times in the past, its seems the VLOOKUP formulas on that page are very picky and I dont know why. The only record number generator I could find on the DataReturn page for Column A that didnt have that knock on effect on the Search page was the previous one I was using.
Workbook Attached.
Hi, James,
the question you asked has been answered, hasn´t it? You should make up your mind and rework the design of the whole workbook - not me as a follow up to a VBA question I answered.
Formula for Sheet Search in C293:
Formula:Please Login or Register to view this content.
Ciao,
Holger
Updated spreadsheet. See PM. Thanks.
Hi, james,
first of all I would ask you to keep the question open to everybody and not limit it to a PM.
Any change with a VBA code will have an influence on the formulas you have in the worksheet, and I remember to have mentioned to cut the number of the formulas down to a small number like ten in advance (not more than a thousand as are in Sheet Search as well as more than 600 in sheet ReurnData - I´m talking about those not having any content but being kept for the future). Each change with a code may lead to altering the formulas which may fit for that purpose but may get messed up with the next question/problem again.
Formula for Sheet Search range C311:
Formula:Please Login or Register to view this content.
Ciao,
Holger
That new formula seems to be doing some strange things. I have attached a worksheet with some search criteria already entered. The correct results appear in rows 11 to 22, but then rows 23 to 320 show records that all say n/a. then rows 321 and on appear to show the same record duplicated over and over again.
This is the formula I entered:
C11:
=IF(VLOOKUP(INDIRECT("ReturnData!A" & ROW()-5),ReturnData!$B$6:$K$49940, 3, FALSE)=0,"",VLOOKUP(INDIRECT("ReturnData!A" & ROW()-5),ReturnData!$B$6:$K$49940, 3, FALSE))
D11:
=IF(VLOOKUP(INDIRECT("ReturnData!A" & ROW()-5),ReturnData!$B$6:$K$49940, 4, FALSE)=0,"",VLOOKUP(INDIRECT("ReturnData!A" & ROW()-5),ReturnData!$B$6:$K$49940, 4, FALSE))
E11:
=IF(VLOOKUP(INDIRECT("ReturnData!A" & ROW()-5),ReturnData!$B$6:$K$49940, 5, FALSE)=0,"",VLOOKUP(INDIRECT("ReturnData!A" & ROW()-5),ReturnData!$B$6:$K$49940, 5, FALSE))
etc
(which I then coped down)
Any thoughts on this Holger? I dont understand why the vlookups on the Search sheet get screwed up due to the record number fromula in A.
Hi, James,
ReturnData!$B$6:$K$49940 could be one key to your question.
Ciao,
Holger
Thats why I dont understand how the formula in column A affects it, since the vlookup just references column B to K?
Holger,
I just realised that when the rows get deleted on the ReturnData sheet the vlookup formula on the Search sheet changes to REF:
=IFERROR(VLOOKUP(ReturnData!#REF!,ReturnData!$B$6:$K$49972, 3, FALSE),"")
This is whats causing the blank records where the deleted records used to be. How can I stop this from happening? (I think this will probably fix all my issues).
Thanks,
James
Thank you for all your help and time, I appreciate you trying to find a solution for me.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks