Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adapt an array routine or a better way?
I build an array of the instances of '£' when found in
col(6). An offset down of a couple of rows will mark the start of a data 'page', whilst an offset up from the next value held in the array, will mark the end of that data 'page'. Once the array is built, this code establishes the data 'page' to be transferred: Set rng1 = Range(varr(i).Offset(1, -5), varr(i + 1) _ .Offset(-6, -5)).Resize(, 6) This seems to work until I reach the last 'page' when suddenly there is no '£' in col(6) to mark the next 'page'......because there isn't a next page, and so no '£' to Offset back up from! What there is however, consistently throughout the data, is a '£' in col(5) which more accurately marks the end of the data 'page'. So might there be a more efficient way that does (perhaps) something like this: search down col(6) for a '£' & mark it as a 'page' start then switch to col(5) in that same row search down col(5) for a '£' & mark it as a 'page' end etc Would be grateful to know if arrays are the answer, and for any help, please. Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.500 / Virus Database: 298 - Release Date: 10/07/2003 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adapt an array routine or a better way?
"Stuart" wrote...
... This seems to work until I reach the last 'page' when suddenly there is no '£' in col(6) to mark the next 'page'......because there isn't a next page, and so no '£' to Offset back up from! ... If there's nothing in the cells below the last bit of data, you may want to move to the bottom of the worksheet, then use .End to move up to the last row containing anything, then offset down (and right?) from that cell to enter a '£' in col 6, then run your existing procedure, and once it's completed delete the added '£' in col 6. -- 1. Don't attach files to postings in this newsgroup. 2. Snip unnecessary text from quoted text. Indiscriminate quoting is wasteful. 3. Excel 97 & later provides 65,536 rows & 256 columns per worksheet. There are no add-ins or patches that increase them. Need more? Use something else. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adapt an array routine or a better way?
Understood, many thanks.
Regards. "Harlan Grove" wrote in message ... "Stuart" wrote... .. This seems to work until I reach the last 'page' when suddenly there is no '£' in col(6) to mark the next 'page'......because there isn't a next page, and so no '£' to Offset back up from! .. If there's nothing in the cells below the last bit of data, you may want to move to the bottom of the worksheet, then use .End to move up to the last row containing anything, then offset down (and right?) from that cell to enter a '£' in col 6, then run your existing procedure, and once it's completed delete the added '£' in col 6. -- 1. Don't attach files to postings in this newsgroup. 2. Snip unnecessary text from quoted text. Indiscriminate quoting is wasteful. 3. Excel 97 & later provides 65,536 rows & 256 columns per worksheet. There are no add-ins or patches that increase them. Need more? Use something else. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.500 / Virus Database: 298 - Release Date: 10/07/2003 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Have Linked Data Labels Adapt for an Expanded Range | Charts and Charting in Excel | |||
Several Pivot Tables: Change one filter and adapt it for the other | Excel Discussion (Misc queries) | |||
Help to adapt Formula syntax to work with Dynamic Named Ranges | Excel Worksheet Functions | |||
adapt macro | Excel Worksheet Functions | |||
How can I adapt the included filter example to search for more th. | Excel Discussion (Misc queries) |