VBA - AutoFilter an array containing wildcards?

Hi,

First post, longtime lurker.

I'm building a report and as part of that I need to collect certain Invoice IDs based on what type of Product Offers IDs they contain. The crux is that the Invoice IDs can contain multiple Product Offer IDs (separated by semicolons) in no particular order in one cell and I only want to collect the invoices containing the specific products I'm after. From what I can tell an Autofilter array doesn't seem to work with wildcards so I'm looking for help.

I've attached an example .csv containing InvoiceID and OfferList columns. There are 12 invoices with a set of product offers (Item.OFD.50.00001 through Item.OFD.50.00008).

For the sake of the example I'm looking to collect Invoice IDs 1-7 and 10-12 which contain offers 1, 2, 4, 5, 6, 7 anywhere in them but not invoices 8-9 that only contain offers 3 and 8. In my real report there are hundreds of offers of which I'm looking to collect around 50. The offers I want to collect are always known but the actual total offer IDs in the source files are unknown.

This is the code part I've made, which only works for Invoices where there is only one product offer per cell, I'm looking to expand this so it suits my outlined example with multiple products per invoice.

Please Login or Register  to view this content.

Many thanks for any help.

Two options are basically either to loop through all the data checking against your desired list, then add those items as literal strings to an array that you can use in the autofilter, or to use an advanced filter instead. The only downside to the advanced filter is that you do need to enter the criteria into cells somewhere. Where are you running the code from, and how do you select the items you are interested in?

Yes, that would work - you just need to make sure that the column header in your add-in matches the column header in the data sheet, and that the offer IDs in your add-in are enclosed in * wildcards, then you can use simply:

Please Login or Register  to view this content.

for example.

You would refer to:

Please Login or Register  to view this content.

For multiple criteria it can get tricky. If it's just one other criterion as in the example you describe, you could just add another column to your table with the relevant column headers and enter "B" into each row.

Glad to help.