VBA/Excel regex with arrays or cells

[Carmelo Labadie]

I'm trying to pattern match according to a given set of cells. For example, I'd like find the locations(row/column) of the headers of a spreadsheet, but the headers may slightly vary in name from worksheet to worksheet.

I considered storing the range of cells in an array and using a regex from there but I'm not sure how to do this.

I tried using the match/find functions but I would like to search for something that matches an exact pattern, not necessarily an exact string or something close to it.

An example of what the headers could be is something like this:

Store Name | Store Number | Item Name | Item Number

or

Store # | Store | Item # | Item

Currently I just have it semi hard-coded to get it done, but I'm looking for a nice way to do this that is easily reusable.

Edit:

To clarify it isn't the regex itself I'm having difficulties with, it's searching a set of cells for a given regex. Currently, I convert a range of cells into a multidimensional array and loop through the array to find the regex. My issue with this is it can be difficult to capture the array of data in the first place (I don't always know where the headers start, the headers can be in a different order, and there may be white spaces anywhere in the spreadsheet).

[Grovkillen]

Will the headers always be on the same row and with not blank columns in-between? Will a header cell always be on top of the list? Etc. etc. These are the things I would look for instead of the actual cell's contents. You could also look into format, bold text in headers, or an borderline etc.