VBA/Excel regex with arrays or cells

Technical support and scripting issues

Moderators: Dorian (MJT support), JRL

Post Reply
Carmelo Labadie
Newbie
Posts: 2
Joined: Fri Jul 02, 2021 7:30 am

VBA/Excel regex with arrays or cells

Post by Carmelo Labadie » Sat Aug 14, 2021 7:09 am

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).

User avatar
Grovkillen
Automation Wizard
Posts: 1009
Joined: Fri Aug 10, 2012 2:38 pm
Location: Bräcke, Sweden
Contact:

Re: VBA/Excel regex with arrays or cells

Post by Grovkillen » Sat Aug 14, 2021 10:23 am

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.
Let>ME=%Script%

Running: 15.0.24
version history

Post Reply
cron
Sign up to our newsletter for free automation tips, tricks & discounts