I realized after deleting data that, on update, column 5 (Row) should update the formula with "=ROW()" not a number. Otherwise, when a row is deleted the row numbers are no longer correct.
Great and thanks so much. I am so glad you like it Frank. You can uncomment the line of code that ads thr row so that the row is always kept Row numbers should always use a formula =Row() I hope this helps and thanks so much.
I love this! I have futtered, but you show how to make the application do the work. This looks like an exceptional example to get someone who has made a start into VBA get a grip of the power and get a better undestanding of how to use it. Thanks
You are very welcome Donnie. I have tons on AI integration. from my AI Playlist I think you will like. If you do try this, it may return an error with a free open AI account but will work with a paid account. With that said I am considering releasing this as a product so you do not need to use your own account. I hope this helps and thanks so much.
Hi Randy, Thank you very much for this video. I built an address book based on your video. Everything went smoothly. However, I did notice an error. It concerns the row number. Suppose, as in your example, you have ten names. When entering the first name in the table (directly in the table and not via the Form), I enter "=ROW()" in the last column (called "row"). That works fine. The first name is in row number 3, so 3 is entered in column "row" in the table. Everything fine. If I add a new name, that new name will automatically be numbered "4" and so on... I fill in the remaining nine names via the form and each name gets its unique number. However, if I now delete the name in row 8 via the form, rows 9,10,11 and 12 are moved up. The row number 8 in Excel now contains in the "row" column of the table the number 9 and no longer the number 8. Now if I want to delete row number 9 (which after deleting row number 8 in the table now has the number 10) then of course the row number 10 is deleted in stead of row number 9. That is of course not the intention. How can the code be adjusted so that this does not happen again? Actually, when deleting a contact, the column number in the table (last column) should also be adjusted so that it always matches the row number in the table. Thanks in advance. Kind regards.
Hi and thanks for your comment and question. When entering the row # via VBA code, we always want to enter the formula using VBA such as .range("K" & ContRow).value = "=Row()" this way no matter what row is deleted, the actual row # will always be accurate. In the row column you would never want to enter the actual row number (manually or with VBA code) and would always want to use the formula. If you are using an advanced filter or separate list of record on this sheet, another sheet, or in a userform, this list must be refresh after every delete, save or update of the record. I hope this helps and thanks so much.
Thank you for your quick answer. I understand what you are saying. But in the video it is not explained like this. Could you please copy here the excact line of code I would have to replace with the code you suggest in your answer? Thank you so much. 👍
Hi and thanks, you would want to add the line Sheet1.range("E" & ContRow).value = "=Row()" After the For/Next loop. This is not needed if you are using a table, as the formula is automated, when using a table, just like in my sample Feel free to download this file using the links in the description under the word DOWNLOAD. I hope this helps and thanks.
@@ExcelForFreelancers Thank you for your answer. It works perfectly that way. I used all the instructions in your example and did insert a table and gave it a name. Without that additional line of code, the next number is simply entered in the "Row" column and not the formula "=Row()". But for me the problem is solved, with the additional line of code the formula "=Row()" is correctly filled in. I don't want to be difficult, but I just wanted to share my experiences with you and the members. Who knows, maybe it will help someone. In any case, thank you very much. By the way, I tried to download the file, entered my email address, but unfortunately I did not receive anything.
Yes great. If you don't have a table, it will not be automated, however when you create a table from your data, it will be automated. I hope this helps and thanks so much.
good evening mr randy, J want to download the resources and the updated workbook "Perishable Inventory menagement update", but, it say me "Page not found", and like this video also the others of 1 years ago. how can J do? thank for your reponse
