Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to construct for best speed?
I have data throughout cols A:G
User cycles down col H and enters an identifier in the cell in that column to indicate the adjacent data to be copied. They do the same in col I and col J, where relevent. All the values in cols H:J will be unique, and likely to be 'A', 'B', 'C' or 'A1', 'A2', etc. The 3 identifier cols represent 3 different types of Contractor that the user will want to send data, ie Subcontractor, Supplier and Labour-only. I have another workbook open, containing an empty sheet for each of the identifiers, and my thought was to copy 'tagged' data into the appropriate sheet. The 'tagged' records need to appear in the sequence found in the original worksheet. So this suggests that I've got to sample down cols H , I and J Should I sample down each of these cols in turn, or sample down col H and test, then using Offset test the adjacent cells in I and J, before continuing down H? Whichever, it seems there's going to be a lot of If Not IsEmpty(Cell) happening. Generally speaking, the entire record occupies a single row. So where an identifier is found, just copy that row. However, sometimes that is not the case and the record might take this form: A Data.............. 4 No 5.00 20.00 General information relating to next record B Different Data 6 No 7.00 42.00 K" So K* is the tag indicating the data is to be copied but this time the "General information relating to next record" data also has to be copied with the 'B' record. I'm reasonably happy that Offset. End(xlUp) & (Down) will get this record. I mention this type of data in case it precludes another solution. The records are not contiguous. Blank/empty rows exist. Any general thoughts before I get stuck in to this, would be much appreciated, thanks. Using Xl2000 (ditto Users). Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to construct for best speed?
Is that: test each cell in one column, then move on to
the next column and ditto, or test one column, and then test across the columns in that row, please? Never been good at cards, and would hate to louse up the deal. Regards and thanks. "Tom Ogilvy" wrote in message ... I think row at a time should be fast enough and would certainly be the easiest - like dealing cards. -- Regards, Tom Ogilvy "Stuart" wrote in message ... I have data throughout cols A:G User cycles down col H and enters an identifier in the cell in that column to indicate the adjacent data to be copied. They do the same in col I and col J, where relevent. All the values in cols H:J will be unique, and likely to be 'A', 'B', 'C' or 'A1', 'A2', etc. The 3 identifier cols represent 3 different types of Contractor that the user will want to send data, ie Subcontractor, Supplier and Labour-only. I have another workbook open, containing an empty sheet for each of the identifiers, and my thought was to copy 'tagged' data into the appropriate sheet. The 'tagged' records need to appear in the sequence found in the original worksheet. So this suggests that I've got to sample down cols H , I and J Should I sample down each of these cols in turn, or sample down col H and test, then using Offset test the adjacent cells in I and J, before continuing down H? Whichever, it seems there's going to be a lot of If Not IsEmpty(Cell) happening. Generally speaking, the entire record occupies a single row. So where an identifier is found, just copy that row. However, sometimes that is not the case and the record might take this form: A Data.............. 4 No 5.00 20.00 General information relating to next record B Different Data 6 No 7.00 42.00 K" So K* is the tag indicating the data is to be copied but this time the "General information relating to next record" data also has to be copied with the 'B' record. I'm reasonably happy that Offset. End(xlUp) & (Down) will get this record. I mention this type of data in case it precludes another solution. The records are not contiguous. Blank/empty rows exist. Any general thoughts before I get stuck in to this, would be much appreciated, thanks. Using Xl2000 (ditto Users). Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to construct for best speed?
If I understood him correctly he already said some single entries were two
rows and there are blank rows between entries. So I am not sure sorting would be advised if this is a form for interacting with the user. But I could be wrong. -- Regards, Tom Ogilvy Mark Bigelow wrote in message ... If sorting wouldn't mess up your data, I would sort all the columns, including H:J, by H, then by I, then by J. That way, you can start at Row 1 through the last row of H that has data and put that Range into the worksheet corresponding with H, then go from that last row + 1 to the last row in I, etc... Let me know if that doesn't work. Mark --- Mark Bigelow mjbigelow at hotmail dot com http://hm.imperialoiltx.com *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to construct for best speed?
Assume your range is H2:J50
For each cell in Range("H2:J50").SpecialCells(xlConstants) if cells(cell.row,1)="B" then ' copy two rows starting with row above cell.offset(-1,0).entireRow.Resize(2,7).Copy _ Destination:=worksheets(cell.Value). _ Cells(row.count,1).End(xlup)(2) else ' copy one row cell.EntireRow.Resize(1,7).Copy _ Destination:=worksheets(cell.Value). _ Cells(row.count,1).End(xlup)(2) End if Next If that doesn't give you the order you want, then you will have to loop through all the cells in the order you want. Name your sheets with the code in H2:J50, then after you have distributed the data, rename the sheets with functional names - then you don't have to translate on each copy. -- Regards, Tom Ogilvy Stuart wrote in message ... Is that: test each cell in one column, then move on to the next column and ditto, or test one column, and then test across the columns in that row, please? Never been good at cards, and would hate to louse up the deal. Regards and thanks. "Tom Ogilvy" wrote in message ... I think row at a time should be fast enough and would certainly be the easiest - like dealing cards. -- Regards, Tom Ogilvy "Stuart" wrote in message ... I have data throughout cols A:G User cycles down col H and enters an identifier in the cell in that column to indicate the adjacent data to be copied. They do the same in col I and col J, where relevent. All the values in cols H:J will be unique, and likely to be 'A', 'B', 'C' or 'A1', 'A2', etc. The 3 identifier cols represent 3 different types of Contractor that the user will want to send data, ie Subcontractor, Supplier and Labour-only. I have another workbook open, containing an empty sheet for each of the identifiers, and my thought was to copy 'tagged' data into the appropriate sheet. The 'tagged' records need to appear in the sequence found in the original worksheet. So this suggests that I've got to sample down cols H , I and J Should I sample down each of these cols in turn, or sample down col H and test, then using Offset test the adjacent cells in I and J, before continuing down H? Whichever, it seems there's going to be a lot of If Not IsEmpty(Cell) happening. Generally speaking, the entire record occupies a single row. So where an identifier is found, just copy that row. However, sometimes that is not the case and the record might take this form: A Data.............. 4 No 5.00 20.00 General information relating to next record B Different Data 6 No 7.00 42.00 K" So K* is the tag indicating the data is to be copied but this time the "General information relating to next record" data also has to be copied with the 'B' record. I'm reasonably happy that Offset. End(xlUp) & (Down) will get this record. I mention this type of data in case it precludes another solution. The records are not contiguous. Blank/empty rows exist. Any general thoughts before I get stuck in to this, would be much appreciated, thanks. Using Xl2000 (ditto Users). Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to construct for best speed?
Making progress with your help.
Regards and thanks. "Tom Ogilvy" wrote in message ... Assume your range is H2:J50 For each cell in Range("H2:J50").SpecialCells(xlConstants) if cells(cell.row,1)="B" then ' copy two rows starting with row above cell.offset(-1,0).entireRow.Resize(2,7).Copy _ Destination:=worksheets(cell.Value). _ Cells(row.count,1).End(xlup)(2) else ' copy one row cell.EntireRow.Resize(1,7).Copy _ Destination:=worksheets(cell.Value). _ Cells(row.count,1).End(xlup)(2) End if Next If that doesn't give you the order you want, then you will have to loop through all the cells in the order you want. Name your sheets with the code in H2:J50, then after you have distributed the data, rename the sheets with functional names - then you don't have to translate on each copy. -- Regards, Tom Ogilvy Stuart wrote in message ... Is that: test each cell in one column, then move on to the next column and ditto, or test one column, and then test across the columns in that row, please? Never been good at cards, and would hate to louse up the deal. Regards and thanks. "Tom Ogilvy" wrote in message ... I think row at a time should be fast enough and would certainly be the easiest - like dealing cards. -- Regards, Tom Ogilvy "Stuart" wrote in message ... I have data throughout cols A:G User cycles down col H and enters an identifier in the cell in that column to indicate the adjacent data to be copied. They do the same in col I and col J, where relevent. All the values in cols H:J will be unique, and likely to be 'A', 'B', 'C' or 'A1', 'A2', etc. The 3 identifier cols represent 3 different types of Contractor that the user will want to send data, ie Subcontractor, Supplier and Labour-only. I have another workbook open, containing an empty sheet for each of the identifiers, and my thought was to copy 'tagged' data into the appropriate sheet. The 'tagged' records need to appear in the sequence found in the original worksheet. So this suggests that I've got to sample down cols H , I and J Should I sample down each of these cols in turn, or sample down col H and test, then using Offset test the adjacent cells in I and J, before continuing down H? Whichever, it seems there's going to be a lot of If Not IsEmpty(Cell) happening. Generally speaking, the entire record occupies a single row. So where an identifier is found, just copy that row. However, sometimes that is not the case and the record might take this form: A Data.............. 4 No 5.00 20.00 General information relating to next record B Different Data 6 No 7.00 42.00 K" So K* is the tag indicating the data is to be copied but this time the "General information relating to next record" data also has to be copied with the 'B' record. I'm reasonably happy that Offset. End(xlUp) & (Down) will get this record. I mention this type of data in case it precludes another solution. The records are not contiguous. Blank/empty rows exist. Any general thoughts before I get stuck in to this, would be much appreciated, thanks. Using Xl2000 (ditto Users). Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Construct staff pay timesheet | Excel Discussion (Misc queries) | |||
I'm Trying to Construct a Smart Worksheet | Excel Worksheet Functions | |||
how do i construct a bar in microsoft word? | Excel Discussion (Misc queries) | |||
if else construct | Excel Worksheet Functions | |||
Construct a range in VB | Excel Discussion (Misc queries) |