Solved

Posted on 2012-08-17

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.

Thanks,

John

GetSeatsFunction-v2.xlsm

GetSeats-Function-Error.xlsm

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

Thanks,

John

GetSeatsFunction-v2.xlsm

GetSeats-Function-Error.xlsm

7 Comments

```
(\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]+)
```

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.

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! :- )

Thanks,

John

GetSeatsFunction-v3.xlsm

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

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

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

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

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.

John

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Why am I getting "No Duplicate Values Found" on this spreadsheet when there very clearly ARE duplicates? | 12 | 46 | |

copy and paste multiple rows on autofiltered data not working | 4 | 24 | |

Excel 2013 Drop down help with data | 3 | 29 | |

rank minimum order | 9 | 0 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**14** Experts available now in Live!