How to use VBA to filter, then insert specific text in offset cell

  • Using MS Office Professional 2010.

    Question: How to use VBA to autofilter for a specified criteria, then insert text into a cell located further down the row

    By recording a macro I was able to establish a filter:

    Attached is a stripped down copy of the file, normally there is a graphic interface so the macros are assigned to certain buttons (which is why we're not using the already built-in filter capability). When executed, the macro above works as desired, finding all instances in a hundred item list. There is potential for data on the "Location" tab to change slightly each month, so I test the data periodically by executing each macro, then putting a "found" into column Z. When all macros have been excecuted I look for any row without a "found", then I know to create a new filter. As you can imagine the process is laborious and consumes a significant period of time.

    The attached file has the VBA modules already and the tab has been mocked up to show the desired result for column Z. My knowledge of Excel forumulas is scant and VBA less so, but I've attempted variations with ActiveCell and .Offset among others.

    My hope is there's some code I can copy & paste by hand into the each macro that will add the "found" text.

    Thank you in advance for any insight and assistance you can share.

  • Maybe something like this.

    Bruce :cool:

  • Thank you for assisting skywriter. When executing the code this error appears

    Run-time error '9':
    Subscript out of range

    Debug has stopped at:

    1. For Each r In Intersect(ActiveSheet.ListObjects("Table_Custom_Queries.accdb3").DataBodyRange.SpecialCells(12), _
    2. ActiveSheet.ListObjects("Table_Custom_Queries.accdb3").ListColumns(26).DataBodyRange)

    Using F8 to step through the code the tool tip from r in the code above always reads as r = Nothing

    If I follow, with Dim, we're setting the variable name as "r" and then using that name later. I need to read up on what the rest of the code is doing and how it's performing the action.

  • I've been reading up a bit this morning on this, while I can't verbalize yet what the code would say, I believe I follow what .SpecialCells(12) and ListColumns(26) are doing.

    SpecialCells with (12) means to look across all visible cells, and ListColumns, well thats for 26 columns to get into column Z.

    So, just because, I changed it to .ListColumns(25) and as someone would imagine, the "found" entry went into column Y. Partial success!

    Are there any recommendations on what/where I can look (another module, or a set-up somewhere) to see what's limiting it from going outside column Y?

    Tested skywriter's submission and the modified .ListColumns(25) on the sample and the production spreadsheet, errors and partial success results were same on both

    Edit: downloaded the sample spreadsheet attached to this forum post and skywriter's submission worked as desired. Something I did locally changed the production spreadsheet as well as my local copy of the sample after posting (wish I knew what I did). I'm still back to my last question on recommendations where I may have limited the code from going outside column Y.

  • Thanks for the pointing me towards the article and your work on the code! Since the original question was answered I'll close this as a success. If there are other questions I'll start a new thread. I don't see anyway to leave a positive feedback/ranking, if it's there someone please let me know.