Skip to main content

One Range within Another


Excel has sophisticated methods available for users but it is easy to misunderstand what the program designer really meant. The Intersect method could be one of these.

I first used the method a little over 20 years ago. It allowed me to post a value in named column, in respect of specific transaction (formed by a row). At that time, the real advantage of this approach the location of the column could change without my having to rewrite the code. It would continue to be posted to the correct cell for the purposes of the overall program.

My current task – one range within another - sounds very similar. Indeed, when searching the web, the Intersect method was offered as a potential solution. However, there are some crucial / fundamental differences.

I was not looking for overlapping ranges. Rather, the whole of the smaller range had to be contained within the boundaries of the larger one. It became clear that I had examine each of the boundaries of the larger range individually and compare them with the equivalent boundaries of the smaller one. Only if all four comparisons were valid could I be sure that no part smaller range stepped outside the boundary of the larger one. This led to some rather tediously long-winded code shown below.
Image of VB code

Mistakes in code development – or are they?

The expectation of the code above is that the larger and smaller ranges will be on the same worksheet, but the requirement is not actually specified. So, I considered what the implications might be if the two ranges were on different worksheets.
  • The code relies exclusively on the numerical values of the boundary positions of the larger and smaller ranges. If those boundary positions were satisfied then the function would still deliver a TRUE result. At first, this seemed a perfectly worthless truism.
  • It then occurred to me that the code was written, assuming that the smaller range was indeed smaller. However, it was also written such that the individual boundaries could be the same. Taken to its logical conclusion, all four boundaries of the so called larger and smaller ranges could be the same.

Taking these two conclusions together, we have a methodology for proving that two ranges on different worksheets are of identical size and in identical locations.


Sheet1



Sheet2

This sounds more like a silly panel game from the BBC’s ‘I’m Sorry I haven’t aClue’, but, if anyone can identify a genuine use for the function, I'd be delighted.





Comments

Post a Comment

Popular posts from this blog

ACBA Mapping: Employing ‘UsedRange’ in Excel

When reviewing a worksheet the ACBA Mapping software uses VBA’s ‘UsedRange’ function to identify the full scope of the sheet. This function is excellent for identifying separate ranges employed in a worksheet, but has some drawbacks. One of these drawbacks has been identified while reviewing the Enron corpus of files ( https://figshare.com/articles/dataset/Enron_Spreadsheets_and_Emails/1221767 ) The Enron corpus of spreadsheets and other correspondence is huge. Felienne Hermans however has whittled the number of workbooks down to 16,189 unique items.   https://www.felienne.com/archives/3634   The workbook that clearly displayed the UsedRange issue is named ‘benjamin_rogers__938__historical outages.xlsx’. Clearly the files have been renamed to prevent duplicates. In this blog though I will refer to it as ‘Historical Outages’. In the original Mapping software, users were advised when the ‘UsedRange’ exceeded half a million cells. This allows the user to escape from the proc...
Post a Comment
Read more

Cell Maps - Critical Comments

Cell maps are intended as tools for reviewing spreadsheets. If you spot an error or an inconsistency in the cell map this should be recorded and, if practicable, corrected. The cell mapping software provides a method for recording a reviewer's comments. All comments are linked to a specific map (or data table), The comments for a workbook under review are collated in a single worksheet. In this case, there's just one comment. The comment above refers to the area highlighted towards the bottom of the map below and are hyperlinked.to the specific area of the map through the column headed 'Map/Source Link'. The top left cell the source range provides a link back to the 'Comments. worksheet'. The yellow cells suggest that users should be entering information for collection by a formula, but which one.  At the this point I went back to the orginal worksheet and traced the dependents for some of these 'so called' user entry cells - also coloured yellow as it ...
Post a Comment
Read more

A Revised Cell Map!

  In the Blog ' What is a Cell Map? ', I included an image of a Cell Map. On reflection the presentation was muddled. A simpler and clearer design seemed worthwhile. The result below is more clearly rectangular, with the headings for the map, the formula table and the names/validation table matched along the same row. The validation table is new and highlights where user inputs are limited by validation and the nature of the validation criteria. The revised software is available at ACBA-Mapping . In addition, although software is intended to be intuitive and self explanatory, it rarely is. I have created a user group at    MSExcelCellMapping@groups.io   . You are welcome to join and ask questions about the software. https://drive.google.com/file/d/1w3tHk1l-G9LjmCsOqMBkJpE9kaSj0iX_/view?usp=sharing Spreadsheet errors and inconsistensies is a subject that is of concern by professional developers as well as amateurs. The European Spreadsheet Risk Interest Group (EuSpRIG...
Post a Comment
Read more