VBA search column for #N/A and allow text box replacement of each instance

    Howdy folks.


    Im working on some autocorrecting stuff, and would like to see if I can get input here. |I suppose my biggest issue is lack of familiarity with VBA syntax. Im able to reverse engineer most scripts as needed (i use you guys quite a bit, and just registered today).


    Im needing to be able to correct #N/A errors by scanning down a column, and with each instance reference the corresponding cell in a different column as a prompt for overwrite in that cell. For example, after clicking a button to activate the macro in one worksheet, in another it finds each #N/A in column B, it will show an error message like "Update code for (name in column A)" and allow a text box entry to update it:


    [TABLE="width: 157"]

    [tr]


    [td]

    Name

    [/td]


    [td]

    Code

    [/td]


    [/tr]


    [tr]


    [td]

    Smith, Sara

    [/td]


    [TD="align: right"]223[/TD]

    [/tr]


    [tr]


    [td]

    Collins, Jeff

    [/td]


    [TD="align: center"]#N/A[/TD]

    [/tr]


    [tr]


    [td]

    Blow, Joe

    [/td]


    [TD="align: right"]453[/TD]

    [/tr]


    [tr]


    [td]

    Howard, Duck

    [/td]


    [td]

    #N/A

    [/td]


    [/tr]


    [tr]


    [td]

    Little, Debbie

    [/td]


    [td]

    316

    [/td]


    [/tr]


    [/TABLE]


    The rest of the routing rewrites the formulas down that column, but would like to error proof the process afterwards.

    Re: VBA search column for #N/A and allow text box replacement of each instance


    These NA's are the result of vlookups? How many different columns are there that the data can be found?


    here is one way you could look for erros in column B


    Re: VBA search column for #N/A and allow text box replacement of each instance


    The errors are the result of index/match formulas, somewhere down the chain. Column A would be employees. Column B would GL codes. If they're termed, they won't be on the Roster that im using as reference, so i want to be able to hardcode the GL code into column B (in this example).


    There are multiple columns of data that links across and whatnot, and several other VB routines that do stuff like printed, formula writing, etc. So whatever we hardcode into the workbook will be overwritten using VBA scripts the next time the workbook is used. BUt i want people who don't know anything about excel to be able to process this. "Idiot proof" it, so to speak. Along with getting better at using Excel. Outside VBA there's nothing I can't do. Inside VBA, im stuck using my 1985 BASIC programming background to fumble through it.


    SHould add: id prefer the routine continue once the value is updated in the text input box, so it works through the list and removes any errors that are present by the end of the exercise

    Re: VBA search column for #N/A and allow text box replacement of each instance


    You might find it will be better/faster to simply provide the logic to replace any errors during run-time rather than pausing to get user input at each error. Looking at the title of your thread, it seems that that what you want to do. If you can provide more info...even a sample...I can help you with this.

    Re: VBA search column for #N/A and allow text box replacement of each instance


    Quote from bryce;789091

    You might find it will be better/faster to simply provide the logic to replace any errors during run-time rather than pausing to get user input at each error. Looking at the title of your thread, it seems that that what you want to do. If you can provide more info...even a sample...I can help you with this.


    I’ve scrubbed the attached of anything identifying. I’ve left a UDF that I used to help with the logic functions on matching up names and attached it here.

    What I would like:

    A routine that will check down column N, and when it finds an error pops up a box showing the contents of column A and a prompt to enter a correct GL code.

    The #N/A errors typically related to an employee who was termed prior to the parameters on the Roster, or has had their name changed. I pasted values over any formulas on the Roster tab to make sure it didn’t mess up calculations once extracted from the workbook.

    It can’t decide what to do on its own due to their being no point of reference. The AP clerk will need to spend time looking up the employee (unless they know them), and coding it onto the attached. The added benefit: any #n/a’s means insurance is being paid for unnecessarily.

    Does this make sense?

    Re: VBA search column for #N/A and allow text box replacement of each instance


    Hi bfft - See if this works for you. When you click the button Update Dpts & GL a userform appears and it shows you the name associated with the current error in column N. The user picks the department from the drop down and types the GL into the textbox. When the user clicks OK, all instances of that employee are updated with the department and GL that they typed.


    About the drop down for the department. You dont need this I guess but for the sake of completeness I added it. The departments are in a named range on the tab I added called Table. If you add the rest of your departments to that list the named range pick them up and appear whenever the form appears.


    If you would like to modify any of this could you please include the code for the UDF? It will be easier for testing purposes.


    Thanks
    Bryce

    Re: VBA search column for #N/A and allow text box replacement of each instance


    That nails it +10. Is there a feedback feature or something?


    I do a lot of free artwork for people, and understand the labor of love. But still...if there is a feedback feature or something I should use, id be happy. You delivered more than asked.

    Re: VBA search column for #N/A and allow text box replacement of each instance


    Nice, I am glad it works for you. There is a reputation system here. You would click the "GIVE Reputation" button right below this post. And thank you.

    Re: VBA search column for #N/A and allow text box replacement of each instance


    I do have a question....how would i integrate this so it works on other worksheets? My workbook uses 4 sheets for this, and i'd like to run the same routine from a 5th tab on each. I'd assume if i knew how to name the worksheet, i could just change the column variables and it would work.

    Re: VBA search column for #N/A and allow text box replacement of each instance


    There would be a few tweaks to the code to accommodate this. I know there are at least 5 worksheets in this workbook. Are there more? I would need to know the names of the tabs and the code names of the tabs so I could adjust the code. Would it be possible to add a scrubbed down version of the workbook you actually want this to work in? It would make it easier for me to adjust.


    Are all of the formulas always in Column N or does that change? Does each tab have a dpt & GL column like the Provident tab...is it in the same columns? Do all tabs have data that begin in row 5? Sorry...lots of Q's but necessary to get you where you want to be.

    Re: VBA search column for #N/A and allow text box replacement of each instance


    i feel fairly helpless asking for help like this. :) My hope is i can use the code to broaden general understanding, beyond the task you're helping with.


    I've scrubbed it all down to just made up names and whatnot. :) All prior macros and UDFs are in place.


    For what its worth....the GL code is really all the matters, everything links up by GL code. To make the workbook cleaner I can just index match the department description on whats in GL. Save a step on the input box. alternately, picking department can allow the GL to index match, making it easier for those who aren't speaking our GL code language.

    Re: VBA search column for #N/A and allow text box replacement of each instance


    Would you be interested in in skipping the formulas and directly assigning departments and GL to names? If we went this route I could just add data validation to the rows where the names were not in your current HR file and color them red, that way the user would know where they need to make adjustments and make selections directly in the worksheet. If the GL is driven from the dpt and vise versus then we could use a change event to fill in the GL after a dpt selection was made.


    You have an active employee list, can your HR get you a termed list in the same way? If we had a term list, there would be no need for the user to do anything. And if you wanted to go this way, how would you deal with duplicate names? Ideally, all tables in the workbook to include the insurance outputs would include employee ID's. That would eliminate the duplicate issue but absent that how would we deal with dupes?


    I will add comments to all the code so you can follow whats happening.

    Re: VBA search column for #N/A and allow text box replacement of each instance


    Employees who are termed should be a pain to deal with, as it means we are paying for employees who are not paying in themselves, and no longer work with us (someone in HR needs to cancel the policy)


    The names....i've had to do quite a bit of workaround to account for a lack of fuzzy logic capability in excel (short of using the add in, which won't work for everyone). One invoice has a space after the comma, one doesn't. One invoice includes middle initial for some, others don't. One invoice has an extra space at the end, and another (for just one employee only) duplicates the middle initial. None seem to match the Roster data.


    Im unsure how you would go about doing what you mention, so am unsure how interested i'd end up being. But given you have more experience with it, i would hate to not considering it.


    Something else to consider: the invoicing is done as they are delivered. So someone may prepare an invoice for UHC today, then update the book with Guardian in 4 days when its delivered. Each one needs to be input, coded, printed, and entered into our AP system (this last step is outside Excel, obviously), with the workbook having a complete record at month end to be saved for posterity. So each invoice and distribution should stand alone to accomdate this and save paper.


    ETA: ideally ill share my workbook with any other location so life gets easier for them. So if you happen to have a better way to look up name data im all about it. There is one kludge for any name that has a space within it (like a hyphenated last name missing the hyphen, as that invoice doesn't print hyphens). You can see it in a formula that denotes a statement for cells containing "STEED"

    Re: VBA search column for #N/A and allow text box replacement of each instance


    Hi bfft - Here is the updated file. I didnt do anything with the formulas. I kept the original find the error and replace it userform.


    Some important ideas.


    1) To find where data starts in any given worksheet, I look for the key words "Home Dept" These key words have to remain else the code will fail
    2) To locate the employee name column in each worksheet I look for the word "Name." Currently, the word "Name" is only found in the Employee Name column. This will have to stay that way.
    3) I added a small table to your GL Cod Map tab that lists the departments and their associated GL. The userform is using this table to both populate the Departments and retrieve the GL


    Besides that. When the error is found the form pops up. All the user has to do is select the department and the GL will auto fill. After OK is clicked all instances of that employee name are updated with the new department and the GL. Additionally, I color the changed cells light red so that it is clear those names were changed and should indicate to the user that these employees have been termed.


    I didn't turn off screen updating. Sometimes I feel like users like to see where they are in the sheet. Generally, it seems to provide comfort to them that what they think is happening is actually happening. Also, there aren't that many rows and or errors so this shouldn't be an issue. If you don't like it I will turn off screen updating.


    Thanks
    Bryce

    Re: VBA search column for #N/A and allow text box replacement of each instance


    Nailed it, sir (or ma'am). Fantastic. This allows me to clear 2 projects today that have taken a ton of my time.


    Thank you kindly. It works just like i need/want.

    Re: VBA search column for #N/A and allow text box replacement of each instance


    I forgot to mention. One of your worksheets has your index formulas in the Home Dpt column wrapped in a IFNA...take that out. We need the error.

    Re: VBA search column for #N/A and allow text box replacement of each instance


    Thanks for that....i found the issue and rewrote it to flow. Did some error testing, and assuming HR stops making changes to report formats, it should work like a charm. Thanks again.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login