Posts by portengine
-
-
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.
-
Thank you for assisting skywriter. When executing the code this error appears
Run-time error '9':
Subscript out of rangeDebug has stopped at:
CodeFor Each r In Intersect(ActiveSheet.ListObjects("Table_Custom_Queries.accdb3").DataBodyRange.SpecialCells(12), _ 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.
-
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:
Code
Display MoreSub Honolulu() ' ' Honolulu Macro ' ' Sheets("Location").Select ActiveSheet.ListObjects("Table_Custom_Queries.accdb3").Range.AutoFilter Field _ :=5, Criteria1:="HI, HONOLULU CITY" ActiveWindow.SmallScroll Down:=-6 End Sub
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.