Modifying a RegEx function to cover more situations

Please take a look at the attached workbook ( "GetSeatsFunction_v2.xlsm") and let me know how I might modify one (or more) of the three functions to avert false positives such as the words like "are" or "is" or anything beginning with a letter from A to M?

The other workbook ("GetSeats Function Error.xlsm") has a less complicated single function that could be adapted as well.

John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?
andrewssd3Connect With a Mentor Commented:
I think your immediate problem with 'are' can be fixed very simply - it is not in the negative lookahead list, so just add it:
(\d{1,3})[ /-]?(?!jan|feb|apr|ma[ry]|ju[ln]|aug|sep|oct|nov|dec|is|in|by|mi|hrs|are|AVOD|check|GMT|jump|and|dtd|fail|area|headset)([A-M]+)

Open in new window

The problem is you might find other words that you need to exclude also, and if they consist only of the letters A-M then it could be impossible to tell them from seat letters.

Another approach might be to be very specific about the order of letters in the seat letters part.  For example 'had' would match your [A-M]+ but I guess it's unlikely anyone would specify a block of seats in that way.  So if there are only a limited number of combinations likely to be specified, you could list them as alternatives, e.g. (AB|ABC|BC....  etc.  But of course there might be so many possibilities it would not be practical to state  them.
käµfm³d 👽Commented:
Can you post the code of the functions? I do not have Excel installed to view the documents.
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Hi Andrew, thanks for your post.I tried added  'are'  and 'is' to both workbooks and it doesn't help. However  There is a table of allowed combinations and if we can modify the code to limit our pattern to those combinations only, then I'd think i'd be in pretty good shape.

Please take a look at the attached revised workbook.

I spent the entire day today trying to figure it out myself, to no avail so any help would be greatly appreciated! :- )

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

John CarneyReliability Business Tools Analyst IIAuthor Commented:
Hi Stuart, I finally figured it out. I just needed to add the negative lookahead list to "Const cstrPattSeatRange" as well!

I think the reason this took me so long is that while I understand the syntax of patterns pretty well, but i don't really understand how all the ancillary stuff works, and I spent most of the time trying to figure that out.

What I'd like to do is ask a series of questions (which I'm hoping you might be available to answer) on manageable chunks of your function until I actually understand it all to the point where I could write one like it myself.

I know there are a lot of online resources about this stuff, but whether it's laziness or ADD, I have a hard time learning anything except via the apprentice system. :- )

For example:
intMaxRows = Application.WorksheetFunction.max(rowAssignments)
    ReDim aRows(1 To intMaxRows)

Open in new window

For starters, why is "rowAssignments" identified as an Excel.Range, instead of just a Range??
How many cells are in a given instance of that range? Two? the Lo and the Hi of each class? Or something else entirely?
If it does in fact refer to the 2-cell ranges of each class, what part of the function tells me whether it's [CE7:CF7] or [CG7:CH7]? in the function.
With this code:
ReDim aRows(1 To intMaxRows)

Open in new window

What string might aRows(1) be? Or aRows(2)? or aRows(intMaxRows)?
What might the value of intMaxRows be? Is it a defined as an Integer rather than Long  because - in the case of  [CE7:CF7] for example (assuming that that's an aRow range) -  it refers to the number value in the second cell of the range, as opposed to the placement of [CF7]in the 2-cell range of B-Class?  
If you can help me with these questions , then I think I can get this stuff down if I post enough similar questions.

I hope this isn't too confusing. In any event, thank you again for your solution the previous question, and for leading me to (what I hope is my correct) assumptive solution to this one.

- John
Thanks for the points.  I will answer these questions here, but you'd better ask any more as a separate question to give other people a chance to weigh on.

OK here goes:
 - Excel.Range is exactly equivalent to Range in Excel VBA.  I have a habit of qualifying it with Excel because if you are coding cross product VBA, e.g. interaction between Word and Excel, Range could be ambiguous, becausse Word also has a (quite different) Range object.
- In any range object there can be any number of cells - think of it like a selection you can make in Excel; it could be one contiguous rectangle of cells, but it can be more complex and be multiple ranges, as if you selected a rectangle, thne added another using ctrl-Click
- I can't remember how the GetSeatsNew function is called, but the rowAssignments will be whatever you pass to it.
- the aRows array has one entry per row - aRows(1) contains the seats (all or a subset) that have been specified for Row 1, aRows(2) is the same for Row 2, and aRows(intMaxRows) is the same for the last row.
- intMaxRows is the number of the last used Row.  int is fine for this because it has a maximum value of  32,767 - which should be more than enough for number of rows of seats on a plane
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Thanks, Stuart. Yes I will definitely be posting more separate questions. If I understand you correctly, in the case of [AD6] whose formula is "=GetSeatsNew(T6,CC6:CL6,CU6:CY6)", rowAssignments is the range "CC6:CL6", and rowSeats is "CU6:CY6". Is that right?

And now that I understand "intMaxRows" I can use my named range [RowCount] which returns the number of the last Row used. "ReDim aRows(1 To [RowCount] )"

Thanks for this help. Hope I encounter you in my further questions.

Yes that's right
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.