Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grouping & Sorting question
Here is what I wish to eventually achieve, and would be
grateful for some help with the first part: User chooses a workbook, and in each sheet in col G they will 'mark' any row they choose with a value from A to Z (Later they will be able to do the same in cols H and I). I'm going to sort and group in sheets in a new workbook based on the values in col G only (for now), such that all the rows marked 'A' end up in sheet 'A' in the new book etc. So ideally the code would run, then pause to allow the user to enter their 'A', 'B's etc after which the code would present a form (ideally) containing the unique values in col G against which the user could input the 'true' name (say 'A' represented Timber), such that a new sheet could be named "Timber" where all 'A's could then be placed. If that makes sense, then how best to pause the code, and what is the best control for the form, please? Or is there a better way? 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
|
|||
|
|||
Grouping & Sorting question
The best way would to be able to identify those lines that pertain to Timber
without having the user designate which ones they are - how is the user making the determination? Could the code do the same thing? You really can't make the macro pause. You can end the macro and have the user kick off the next step. If it was a single cell, you could kludge something with onkey, but since the user basically needs to make multiple entries, the user would have to decide when they are done and perform some action that starts a new macro to complete the job. If you want to maintain control, then you would need to present the pertinent information in a userform and not give the user uncontrolled access to the sheet. -- Regards, Tom Ogilvy Stuart wrote in message ... Here is what I wish to eventually achieve, and would be grateful for some help with the first part: User chooses a workbook, and in each sheet in col G they will 'mark' any row they choose with a value from A to Z (Later they will be able to do the same in cols H and I). I'm going to sort and group in sheets in a new workbook based on the values in col G only (for now), such that all the rows marked 'A' end up in sheet 'A' in the new book etc. So ideally the code would run, then pause to allow the user to enter their 'A', 'B's etc after which the code would present a form (ideally) containing the unique values in col G against which the user could input the 'true' name (say 'A' represented Timber), such that a new sheet could be named "Timber" where all 'A's could then be placed. If that makes sense, then how best to pause the code, and what is the best control for the form, please? Or is there a better way? 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grouping & Sorting question
Thanks for the reply. I was unsure how to approach this
task. Here is a typical data row, where User's original data is in cols B, C and D. The additional columns/data etc have been created via addin routines: 'headers' Item Description Qty Unit Rate Total col 'G' 'data' A Carpets 10 m2 User would enter 'F' in col G (for Flooring), the idea being that it and all further 'F' items were saved to a common sheet. The same would apply for slate flooring, vinyl flooring, etc. However I think it has to be user-designated, rather than looking for 'keywords' in col B. The idea for cols H and I is as follows: col G: go to a carpet shop and get a price to buy the carpet col H: go to a carpet shop and get a price to supply and fit the carpet col I: go to the carpet shop and get a price to fit only the user's own carpet Banaal I know(g) and Saturday too! So after the save to a sheet, I'm hoping to offer the user the options to print/fax/email their col G, H ,I options to a shop or subcontractor, for pricing the various options. Only certain options are sent to specific shops/subcontractors. Is this feasible/sensible? I'm trying to keep this within VBA control, but at worst, obviously the 'sort' could be achieved in pure Excel. Users + me using Excel 2000. Regards. "Tom Ogilvy" wrote in message ... The best way would to be able to identify those lines that pertain to Timber without having the user designate which ones they are - how is the user making the determination? Could the code do the same thing? You really can't make the macro pause. You can end the macro and have the user kick off the next step. If it was a single cell, you could kludge something with onkey, but since the user basically needs to make multiple entries, the user would have to decide when they are done and perform some action that starts a new macro to complete the job. If you want to maintain control, then you would need to present the pertinent information in a userform and not give the user uncontrolled access to the sheet. -- Regards, Tom Ogilvy Stuart wrote in message ... Here is what I wish to eventually achieve, and would be grateful for some help with the first part: User chooses a workbook, and in each sheet in col G they will 'mark' any row they choose with a value from A to Z (Later they will be able to do the same in cols H and I). I'm going to sort and group in sheets in a new workbook based on the values in col G only (for now), such that all the rows marked 'A' end up in sheet 'A' in the new book etc. So ideally the code would run, then pause to allow the user to enter their 'A', 'B's etc after which the code would present a form (ideally) containing the unique values in col G against which the user could input the 'true' name (say 'A' represented Timber), such that a new sheet could be named "Timber" where all 'A's could then be placed. If that makes sense, then how best to pause the code, and what is the best control for the form, please? Or is there a better way? 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Grouping & Sorting question
As I see it:
macro ends User completes columns G, H, I User starts macro to process the data. Alternative is to loop through the items row at a time, load into a userform, user makes selection there, write results to row, do next entry. -- Regards, Tom Ogilvy Stuart wrote in message ... Thanks for the reply. I was unsure how to approach this task. Here is a typical data row, where User's original data is in cols B, C and D. The additional columns/data etc have been created via addin routines: 'headers' Item Description Qty Unit Rate Total col 'G' 'data' A Carpets 10 m2 User would enter 'F' in col G (for Flooring), the idea being that it and all further 'F' items were saved to a common sheet. The same would apply for slate flooring, vinyl flooring, etc. However I think it has to be user-designated, rather than looking for 'keywords' in col B. The idea for cols H and I is as follows: col G: go to a carpet shop and get a price to buy the carpet col H: go to a carpet shop and get a price to supply and fit the carpet col I: go to the carpet shop and get a price to fit only the user's own carpet Banaal I know(g) and Saturday too! So after the save to a sheet, I'm hoping to offer the user the options to print/fax/email their col G, H ,I options to a shop or subcontractor, for pricing the various options. Only certain options are sent to specific shops/subcontractors. Is this feasible/sensible? I'm trying to keep this within VBA control, but at worst, obviously the 'sort' could be achieved in pure Excel. Users + me using Excel 2000. Regards. "Tom Ogilvy" wrote in message ... The best way would to be able to identify those lines that pertain to Timber without having the user designate which ones they are - how is the user making the determination? Could the code do the same thing? You really can't make the macro pause. You can end the macro and have the user kick off the next step. If it was a single cell, you could kludge something with onkey, but since the user basically needs to make multiple entries, the user would have to decide when they are done and perform some action that starts a new macro to complete the job. If you want to maintain control, then you would need to present the pertinent information in a userform and not give the user uncontrolled access to the sheet. -- Regards, Tom Ogilvy Stuart wrote in message ... Here is what I wish to eventually achieve, and would be grateful for some help with the first part: User chooses a workbook, and in each sheet in col G they will 'mark' any row they choose with a value from A to Z (Later they will be able to do the same in cols H and I). I'm going to sort and group in sheets in a new workbook based on the values in col G only (for now), such that all the rows marked 'A' end up in sheet 'A' in the new book etc. So ideally the code would run, then pause to allow the user to enter their 'A', 'B's etc after which the code would present a form (ideally) containing the unique values in col G against which the user could input the 'true' name (say 'A' represented Timber), such that a new sheet could be named "Timber" where all 'A's could then be placed. If that makes sense, then how best to pause the code, and what is the best control for the form, please? Or is there a better way? 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
|
|||
|
|||
Grouping & Sorting question
Many thanks for the help.
Regards. "Tom Ogilvy" wrote in message ... As I see it: macro ends User completes columns G, H, I User starts macro to process the data. Alternative is to loop through the items row at a time, load into a userform, user makes selection there, write results to row, do next entry. -- Regards, Tom Ogilvy Stuart wrote in message ... Thanks for the reply. I was unsure how to approach this task. Here is a typical data row, where User's original data is in cols B, C and D. The additional columns/data etc have been created via addin routines: 'headers' Item Description Qty Unit Rate Total col 'G' 'data' A Carpets 10 m2 User would enter 'F' in col G (for Flooring), the idea being that it and all further 'F' items were saved to a common sheet. The same would apply for slate flooring, vinyl flooring, etc. However I think it has to be user-designated, rather than looking for 'keywords' in col B. The idea for cols H and I is as follows: col G: go to a carpet shop and get a price to buy the carpet col H: go to a carpet shop and get a price to supply and fit the carpet col I: go to the carpet shop and get a price to fit only the user's own carpet Banaal I know(g) and Saturday too! So after the save to a sheet, I'm hoping to offer the user the options to print/fax/email their col G, H ,I options to a shop or subcontractor, for pricing the various options. Only certain options are sent to specific shops/subcontractors. Is this feasible/sensible? I'm trying to keep this within VBA control, but at worst, obviously the 'sort' could be achieved in pure Excel. Users + me using Excel 2000. Regards. "Tom Ogilvy" wrote in message ... The best way would to be able to identify those lines that pertain to Timber without having the user designate which ones they are - how is the user making the determination? Could the code do the same thing? You really can't make the macro pause. You can end the macro and have the user kick off the next step. If it was a single cell, you could kludge something with onkey, but since the user basically needs to make multiple entries, the user would have to decide when they are done and perform some action that starts a new macro to complete the job. If you want to maintain control, then you would need to present the pertinent information in a userform and not give the user uncontrolled access to the sheet. -- Regards, Tom Ogilvy Stuart wrote in message ... Here is what I wish to eventually achieve, and would be grateful for some help with the first part: User chooses a workbook, and in each sheet in col G they will 'mark' any row they choose with a value from A to Z (Later they will be able to do the same in cols H and I). I'm going to sort and group in sheets in a new workbook based on the values in col G only (for now), such that all the rows marked 'A' end up in sheet 'A' in the new book etc. So ideally the code would run, then pause to allow the user to enter their 'A', 'B's etc after which the code would present a form (ideally) containing the unique values in col G against which the user could input the 'true' name (say 'A' represented Timber), such that a new sheet could be named "Timber" where all 'A's could then be placed. If that makes sense, then how best to pause the code, and what is the best control for the form, please? Or is there a better way? 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 | |||
Grouping and sorting details | Excel Worksheet Functions | |||
Grouping and sorting | Excel Discussion (Misc queries) | |||
Grouping Cells / Sorting | Excel Discussion (Misc queries) | |||
Tricky question with grouping and sorting | Excel Worksheet Functions | |||
grouping and sorting data | Excel Discussion (Misc queries) |