AutoFilter Macro using Array Issues

[cusoman]

Hey all, having some trouble with some VBA wherein I'm attempting to feed data into an array via a range (as far as I know, you can do this, correct me if I'm wrong), and then apply a custom autofilter to a field on another sheet. The problem is I keep getting a "type mismatch" error when running it. The data in the column I'm trying to feed from is actually straight numbers, but I want to read them in as a string type because as you'll see in the filter, I'm applying some text based wildcards as the numbers appear in the middle of a string of data on the sheet I'm trying to apply the filter to.

I made sure the data in the A column in my "Cust ID" sheet is formatted as text, and I also tried declaring "cusIDarray" as a Variant, but then I get a type mismatch on my filter statement.

Help if you can!

Please Login or Register  to view this content.

[Norie]

You need a one-dimensional array, an array from a worksheet is two-dimensional.

Also, cusIDArray is an array not a string so you can't concatenate wildcards on it, even if you could I'm you would need to add them for each item.

It is possible to convert your array to one-dimensional, but I don't know how to deal with wildcard.

[cusoman]

Sounds like maybe an array of strings isn't the way to go here as I don't know how to use the autofilter on that even if it was a one-dimensional array. Maybe if I lay out what I'm trying to do here, another solution could be proposed? Here's what I'm trying to do:

- Using the list of IDs in Column A in sheet "ID List" (there could be 500+ IDs here) I want to filter out unique rows of data, based on data in Column K of the “Monitor” sheet that contain any one of those IDs. The “contain” logical function here is important, as the ID will be in the middle of the data in the cells that the filter will be applied to.
- This autofilter needs to reapply to the data every time the web query is refreshed (thinking this part requires VBA for sure).

Example:

Column A on “ID List" sheet
1234
2345
3456
4567

Column K on “Monitor” sheet from refreshable web query (other columns omitted, but the data from those columns needs to come out of the filter as well)
TRANSACTION.S2345.RECORD
TRANSACTION.S2345.RECORD
TRANSACTION.S1111.RECORD
TRANSACTION.S1234.RECORD
TRANSACTION.S3456.RECORD
TRANSACTION.S4567.RECORD
TRANSACTION.S2345.RECORD
TRANSACTION.S9999.RECORD

When the filter is applied on the rows of data that contain the above samples using the ID criteria from “ID List”, it should only show the rows that have this data in column K in "Monitor":
TRANSACTION.S2345.RECORD
TRANSACTION.S1234.RECORD
TRANSACTION.S3456.RECORD
TRANSACTION.S4567.RECORD

So the duplicates of "2345" got filtered out, along with the data that didn't have IDs that matched the other sheet, namely "1111" and "9999".