I have a table of data in a workbook. Each row has a formula that points to that rows row number. Currently the formula is: =--ISNUMBER(IFERROR(SEARCH($L$3,A28,1),""))
After inserting a new record using a UserForm, the record is inserted OK but the row number is now off by one on all the following records. In the formula above, the offending reference s the ,A28,
I would like to have, in this example, reference the row the formula is actually in.
Thank you.
Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Here is a copy of the file.
So, you're looking for the contents of cell $L$3 in the current row of column A. So, on row 3, you are looking for $L$3 in cell A3.
Where do you insert the row? And what do you want to happen? A28 should stay the same?
If you click the button at the bottom of sheet one, use the form to add a new record using data that does not match any of the dummy data. After the record is inserted, check the formula in column H.
The row reference for that record and the one below it should not match the row the data is in.
Cell L3 in Sheet 2 contains the record text of what was selected in the combo box on Sheet 1. Then, the formula being references in column gets that value and finds all matches of that selection in column A in the database.
Hope that makes sense. It also happens if you insert a row directly in the database by inserting a new row.
Last edited by TeacherWart; 01-31-2023 at 03:58 PM.
I don't think I was able to reproduce the problem.After inserting a new record using a UserForm, the record is inserted OK but the row number is now off by one on all the following records.
That said, if it DOES happen, I suggest you re-establish the formulae in columns I:K. Do that by calling the subroutine below.
Please Login or Register to view this content.
Please note that this has been tested independently. It has NOT been tested when called from elsewhere. As stated, it should be called AFTER a row has been inserted to maintain the integrity of the formulae. Unfortunately, because the application changes the Excel environment, I am not prepared to test it IRL. If the (original) code fails/crashes for any reason, it could leave a lot of sorting out to do ... and I'm not going to take that risk.
Thank you. I will be trying your example tomorrow and will get back to you.
Your code works perfectly. Thank you so much.
You're welcome. Thanks for the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks