PDA

View Full Version : Solved: Delete row (numbered) all other rows (numbers) change in sequence



Nurofen
10-20-2009, 01:07 PM
I have a speadsheet with numbers in column (1.01,1.02.1.03), If i delete 1.02 is there for all the other numbers to resequence themselves to read
(1.01,1.02).

Thank you for your help in advance.

Regards

Anit

MWE
10-20-2009, 02:44 PM
I have a speadsheet with numbers in column (1.01,1.02.1.03), If i delete 1.02 is there for all the other numbers to resequence themselves to read
(1.01,1.02).

Thank you for your help in advance.

Regards

Anitthere are ways to do this. A few questions:
I assume you mean that in a spreadsheet with multiple rows, you have a column used to "number" each row, for example, 1.01, 1.02, etc
I assume by "delete 1.02" you mean delete the row containing 1.02
You provided 1.01, 1.02, 1.03 as an example. What happens after 1.99?
Do you want to do this using VBA or without VBA?

mdmackillop
10-20-2009, 04:22 PM
In A1 1.01
In A2 =A1+0.01 and copy down

MWE
10-20-2009, 05:26 PM
In A1 1.01
In A2 =A1+0.01 and copy downI don't think it is quite that simple. You are assuming that the identify scheme is numerical and runs as a numerical sequence. I await confirmation from Nurofen that the assumption is correct. I suspect that row ID is really an indexing scheme and some "numerical values" are not there.

Nurofen
10-21-2009, 05:36 AM
I assume you mean that in a spreadsheet with multiple rows, you have a column used to "number" each row, for example, 1.01, 1.02, etc
I assume by "delete 1.02" you mean delete the row containing 1.02
You provided 1.01, 1.02, 1.03 as an example. What happens after 1.99?
Do you want to do this using VBA or without VBA?

1. Yes
2. Yes the whole row will be deleted
3. There are different areas of interest so one area may only reach 1.16, the next area would then start at 2.01 (sheet attached).
4. I would say vba, but would like to see what can be done without vba

Just a add-on to the last question:
5. The link refers to another sheet and area of where the data is stored can the the data be cut and placed into another sheet when the numbered link is deleted so the data and links are up-to-date


Regards
Nurofen

Thankyou for all your help inadvance

MWE
10-21-2009, 09:09 AM
1. Yes
2. Yes the whole row will be deleted
3. There are different areas of interest so one area may only reach 1.16, the next area would then start at 2.01 (sheet attached).
4. I would say vba, but would like to see what can be done without vba

Just a add-on to the last question:
5. The link refers to another sheet and area of where the data is stored can the the data be cut and placed into another sheet when the numbered link is deleted so the data and links are up-to-date


Regards
Nurofen

Thankyou for all your help inadvanceattached please find an updated spreadsheet with the functionality you requested. I added a few things:
it deletes one or more rows
it checks to make sure that no "title" row is in the set of rows to be deletedSimple select one or more rows and click on the command button.

I copied the data from sheet1 to sheet2 for testing purposes. I used sheet3 as the target sheet to which deleted rows are copied

The code is "brute force", nothing elegant. But it is easy to understand.

You will find a 2nd procedure, xlLastRow, that computes the last populated row of a target worksheet. That is used to figure out where copied rows belong.

Let me know if this works as you want.

Nurofen
10-21-2009, 04:28 PM
MWE Thank you for that mate.

1. The re-indexing is prefect, is there a way of deleting the number that appear out of boxes in column A.

2: Say I wanted to delete the row sheet1 and also delete a named range on sheet2. On sheet(1) I delete row(s) 2 with 1.02 which is linked(this refers to a range A4:L23 on sheet2)to the range named on sheet2, which will also be deleted
3): if a new number is added with a new name is possible to have user be asked to input the named range that it refers to on sheet(2), so when clicked or deleted, it either refers them to the range of data on sheet(2) or deletes it.

Thank you for help in advance

MWE
10-21-2009, 06:19 PM
MWE Thank you for that mate.

1. The re-indexing is prefect, is there a way of deleting the number that appear out of boxes in column A.

2: Say I wanted to delete the row sheet1 and also delete a named range on sheet2. On sheet(1) I delete row(s) 2 with 1.02 which is linked(this refers to a range A4:L23 on sheet2)to the range named on sheet2, which will also be deleted
3): if a new number is added with a new name is possible to have user be asked to input the named range that it refers to on sheet(2), so when clicked or deleted, it either refers them to the range of data on sheet(2) or deletes it.

Thank you for help in advanceLet's address your new needs one at a time:

1. I do not understand what you want. Assuming that "in boxes" means cells with borders: in the sample spreadsheet you provided, all numbers in ColA are "in boxes". What numbers do you mean?

2. Your linking (or hyperlinking) from Sheet1 is to individual cells in Sheet2. For example, the link from cell A5 (with text 1.02) is to cell A2 on sheet2. That said, if you wish to delete the linked range (a single cell or a multi-cell range) on some other sheet associated with (linked from) a particular row on Sheet1 when the particular row is deleted, yes that can be done. Do you wish to delete the range or "clear the contents" of the range. If you delete the range, adjacent cells move around.

3. The easiest way to do this would be to add a button (Add Row) that would add a row above the currently selected row and prompt the user for the text for colB in the new row, and the sheet and range that is to be linked to the new row. I assume you would want to reindex after any add.

As this gets more complicated, it would be wise to protect relevant sheets so operations are only done via command buttons

Nurofen
10-22-2009, 05:17 AM
1. I do not understand what you want. Assuming that "in boxes" means cells with borders: in the sample spreadsheet you provided, all numbers in ColA are "in boxes". What numbers do you mean? ignore me on this one I worked out the problem, if you delete e.g. 6.04 the re-indexing numbers appear out of the boxes. I will use a title colored white to stop this from happening.

2. Your linking (or hyperlinking) from Sheet1 is to individual cells in Sheet2. For example, the link from cell A5 (with text 1.02) is to cell A2 on sheet2. That said, if you wish to delete the linked range (a single cell or a multi-cell range) on some other sheet associated with (linked from) a particular row on Sheet1 when the particular row is deleted, yes that can be done. Do you wish to delete the range or "clear the contents" of the range. If you delete the range, adjacent cells move around. I would be hyperlinking to a multi-cell range on sheet2, the data will always be the same width(A:K) but the length will be different in all cases. link 1.01 = (A13:K25), link 1.02 = (A27:K50). When 1.01 is deleted can (A13:K25) also be deleted and all other cells move up and the re-indexed on sheet1 reflects sheet 2.

3. The easiest way to do this would be to add a button (Add Row) that would add a row above the currently selected row and prompt the user for the text for colB in the new row, and the sheet and range that is to be linked to the new row. I assume you would want to reindex after any add. Yes

As this gets more complicated, it would be wise to protect relevant sheets so operations are only done via command buttons Ok

MWE thank you for taking the time to help.

MWE
10-22-2009, 07:49 AM
re your upgrades:
1) attached please find new version of spreadsheet with the bug you discovered fixed. I did not consider the problem of "no next group". The revised spreadsheet now does what it should when you delete a row in the last group. I also froze rows 1:2 so the DeleteRow button is always visible.

2) yes, we can delete ranges as you wish

3) I will update the attached a little later today with requests #2 and #3 (and the auto protect feature) and should post back before the end of the day.

Nurofen
10-22-2009, 10:32 AM
MWE,
Thank you so much.

Nurofen

MWE
10-22-2009, 12:20 PM
attached please find v004 of the work in progress. Updates include:
Deleted rows are posted to "DeletedRows" sheet
if deleted row has a hyperlink, the target is deleted
Add Rows button to add one or more rows
AddRows procedure adds rows just like the manual process, i.e., rows are added above the selection and the # of rows added = the # of rows selected
after adding rows, relevant section is reindexed
then user is asked (row by row) for "Name" and hyperlink range. The appl assumes that all links will be to the same sheet ("LinkSheet")I changed the links for the 3 cells on Sheet1 to link to ranges in the LinkSheet sheet. I think everything you wanted is included. I did some testing and everything seems to work OK. I have not added the automated sheet protecting and unprotecting as I wanted you to play with it a bit and see if it is what you want.

Get back to me with anything that is not working as you wish or if you have additional requests (within reason:devil2:) or when you are ready for the sheet autoprotect.

Nurofen
10-22-2009, 02:15 PM
Deleted rows are posted to "DeletedRows" sheet (thanks)
if deleted row has a hyperlink, the target is deleted (thanks)
Add Rows button to add one or more rows (thanks)
AddRows procedure adds rows just like the manual process, i.e., rows are added above the selection and the # of rows added = the # of rows selected (thanks)
after adding rows, relevant section is reindexed (thanks)
then user is asked (row by row) for "Name" and hyperlink range. The appl assumes that all links will be to the same sheet ("LinkSheet") (thanks) After the name has been added I would the user to enter a further 5 cells across Name, House address, class, goal, color, date.Once the row has been added and I click the hyperlink I recieve a error, I have attached print page of this, it maybe me doing something wrong.

Again thank you for all your help mate.(I'm unable to attach my file, will ASAP)

MWE
10-22-2009, 04:48 PM
then ... (thanks) After the name has been added I would the user to enter a further 5 cells across Name, House address, class, goal, color, date.Once the row has been added and I click the hyperlink I recieve a error, I have attached print page of this, it maybe me doing something wrong.

Again thank you for all your help mate.(I'm unable to attach my file, will ASAP)
The bug in the hyperlink has been fixed (I had attached the wrong spreadsheet).

I updated the spreadsheet to provide the additional 5 cells for each added row and tweaked the sheet1 format to accommodate things. Is the Date the user enters today's date? If so, we can automate that.

Nurofen
10-22-2009, 11:51 PM
MWE,
That is prefect mate, the date is random so can not automate.
is it possible to have the number on the link appear on the data top left cell

one last request:

if i wanted to print the data that is linked to say steve can that be done from a search feature or a drop down list.

Once again Thank you so much for all your help.

Nurofen

MWE
10-23-2009, 06:49 AM
MWE,
That is prefect mate, the date is random so can not automate.
is it possible to have the number on the link appear on the data top left cell

one last request:

if i wanted to print the data that is linked to say steve can that be done from a search feature or a drop down list.

Once again Thank you so much for all your help.

NurofenI am not sure what you mean when you state, "is it possible to have the number on the link appear on the data top left cell". I assume that you are asking for the following:
for any row on sheet1 that has a hyperlink
add the row "index", e.g., "1.04" to the top left cell of the hyperlink target (range)That can easily be done as long as the target cell is not being used for something else already.

Next, when you ask about "... print the data ...":
Printing the range associated with a particular hyperlink is fairly easy
One method would be to add a button called, say, "Print Data" that would print the link target data associated with the currently selected line. By selecting the row and then clicking the button, the row information (Index, Name, Address, etc) could also be provided on the printout.

Nurofen
10-23-2009, 09:26 AM
for any row on sheet1 that has a hyperlink
add the row "index", e.g., "1.04" to the top left cell of the hyperlink target (range) yes ( Max - 1.04) attached sheetThat can easily be done as long as the target cell is not being used for something else already. (thanks)

Next, when you ask about "... print the data ...":
Printing the range associated with a particular hyperlink is fairly easy
One method would be to add a button called, say, "Print Data" that would print the link target data associated with the currently selected line. By selecting the row and then clicking the button, the row information (Index, Name, Address, etc) could also be provided on the printout. That would only print the data connected to row?, (if steve appears more than once I would like to be able to print each data range connected to steve, steve appears say 9 or more times i would to be able to print them all.

again thank you.

MWE
10-23-2009, 10:38 AM
Per the example, you want both the Name and the Index in the top left cell of the hyperlink target. That is included in the attached version 6

Re the printing request. We can still use the selected row and Print Data button approach and add a search for all other identical names and print out those hyperlink targets as well. A few questions:
if the row selected has Name = "Steve", we will print out all targets associated with Name = "Steve" in other rows. Do you want to also print out targets associated with alternate spellings, e.g., "steve", "STEVE", etc? (I assumed Yes)
how do you want the printout grouped? The simplest way would be one target printout per page (I assumed one per page)
how do you want to handle the situation where the entire range does not fit on a single page? Do you wish to have the output scaled to fit a single page? (I have done nothing about this yet)
I have not done anything special should the selected row not have a hyperlink. Do you want to warn the user?
Given the assumptions above, the attached has this printing capability. When you click on Print Data, there will be a prompt asking if you wish to print out all instances of Name = ????. If you will always want to do that, we can remove that logic.

Nurofen
10-23-2009, 04:40 PM
if the row selected has Name = "Steve", we will print out all targets associated with Name = "Steve" in other rows. Do you want to also print out targets associated with alternate spellings, e.g., "steve", "STEVE", etc? (I assumed Yes) Yes

how do you want the printout grouped? The simplest way would be one target printout per page (I assumed one per page) Yes

how do you want to handle the situation where the entire range does not fit on a single page? Do you wish to have the output scaled to fit a single page? (I have done nothing about this yet) fit single page

I have not done anything special should the selected row not have a hyperlink. Do you want to warn the user? Yes
Given the assumptions above, the attached has this printing capability. When you click on Print Data, there will be a prompt asking if you wish to print out all instances of Name = ????. If you will always want to do that, we can remove that logic. yes we can remove that logic, the case will always be to print all instances of steve. can we add a preview function where all the data for steve is just copied to a new sheet.


Thank you again

Nurofen

MWE
10-23-2009, 06:36 PM
attached please find version 7. It contains the additional functionality requested. A new button, Preview Data, provides the previewing requested.

You owe me a beer the next time I am in the UK

Nurofen
10-24-2009, 06:32 AM
You owe me a beer the next time I am in the UK
MWE no problem at all let me know i buy a case mate.

can the preview and print function be transfered to word (so they preview is in word and the print function places the data in word than prints- reason: due to merged cell when preview the data loses all it formatting.

I've just had a add on : on the LinkSheet if the data is 10 or more I would like the index number, name, number and colour carried over to the Number10andAbove sheet, if the number is 9 than carried over to the Number9 sheet. (can they made to be auto-updated when numbers are changed on the data sheet)

This is truley the last request MWE

Thanks again mate

MWE
10-24-2009, 08:55 AM
MWE no problem at all let me know i buy a case mate.

can the preview and print function be transfered to word (so they preview is in word and the print function places the data in word than prints- reason: due to merged cell when preview the data loses all it formatting.
My experience with moving data between Excel and Word is that the formatting is worse than before. Initiating a Word process from Excel and dumping the data into a word doc is easy, but you end up with word tables (which are messy) and synchronizing control between two programs as you have requested is tricky. I do not understand the formatting problem you mentioned; the print preview in Excel preserves all formatting. Can you add some real data to the spreadsheet and attach it to your reply so I can see what the problem is. Perhaps there is an easy solution without involving Word.

I've just had a add on : on the LinkSheet if the data is 10 or more I would like the index number, name, number and colour carried over to the Number10 and Above sheet, if the number is 9 than carried over to the Number9 sheet. (can they made to be auto-updated when numbers are changed on the data sheet) Sorry, I have no idea what you are asking for here. What data -- the hyperlink target? 10 or more what? 9 what? Are these number some reference to the index numbers? the number of hyperlink targets? the size (either rows or cols) of the target(s)? What are the other sheets you are referencing,. e.g., Number10 sheet, Number9 sheet?

This is truley the last request MWE

Thanks again mate

Nurofen
10-25-2009, 06:23 AM
can the preview and print function be transfered to word (so they preview is in word and the print function places the data in word than prints- reason: due to merged cell when preview the data loses all it formatting.
My experience with moving data between Excel and Word is that the formatting is worse than before. Initiating a Word process from Excel and dumping the data into a word doc is easy, but you end up with word tables (which are messy) and synchronizing control between two programs as you have requested is tricky. I do not understand the formatting problem you mentioned; the print preview in Excel preserves all formatting. Can you add some real data to the spreadsheet and attach it to your reply so I can see what the problem is. Perhaps there is an easy solution without involving Word.
I will trust your experience mate, (attached data, it seems all the formatting is kept, it's a resizing problem)

I've just had a add on : on the LinkSheet if the data is 10 or more I would like the index number, name, number and colour carried over to the Number10 and Above sheet, if the number is 9 than carried over to the Number9 sheet. (can they made to be auto-updated when numbers are changed on the data sheet) Sorry, I have no idea what you are asking for here. What data -- the hyperlink target? 10 or more what? 9 what? Are these number some reference to the index numbers? the number of hyperlink targets? the size (either rows or cols) of the target(s)? What are the other sheets you are referencing,. e.g., Number10 sheet, Number9 sheet? (sorry about the bad explanation, if the score is 9 than display on "Number9" sheet, if the score is 10 or more display on "Number10Above" sheet)

MWE
10-25-2009, 05:29 PM
can the preview and print function be transfered to word (so they preview is in word and the print function places the data in word than prints- reason: due to merged cell when preview the data loses all it formatting.
My experience with moving data between Excel and Word is that the formatting is worse than before. Initiating a Word process from Excel and dumping the data into a word doc is easy, but you end up with word tables (which are messy) and synchronizing control between two programs as you have requested is tricky. I do not understand the formatting problem you mentioned; the print preview in Excel preserves all formatting. Can you add some real data to the spreadsheet and attach it to your reply so I can see what the problem is. Perhaps there is an easy solution without involving Word.

I will trust your experience mate, (attached data, it seems all the formatting is kept, it's a resizing problem)

I have looked at the real data you provided. It appears that every hyperlink target is the same size: 19 rows by 10 cols. If that is true, we can easily solve the size problem. The print preview and ultimate print scale factor is being set in the current spreadsheet such that the output is 1 page wide and 1 page tall -- as I thought you wanted. As the number of data blocks increases, the whole thing gets narrower because it has to fit on a single page. Moving this data to Word will not help the situation. You previously mentioned that you wanted all data blocks associated with a particular name to be on a single sheet/page. We can put everything on a single sheet, but if there are more than 4 blocks, any subsequent printout will be need more than a single page. Before I revise the Print Data function, I want to ensure that my assumptions are correct, in particular that all target blocks are 19 rows by 10 cols.



I've just had a add on : on the LinkSheet if the data is 10 or more I would like the index number, name, number and colour carried over to the Number10 and Above sheet, if the number is 9 than carried over to the Number9 sheet. (can they made to be auto-updated when numbers are changed on the data sheet) Sorry, I have no idea what you are asking for here. What data -- the hyperlink target? 10 or more what? 9 what? Are these number some reference to the index numbers? the number of hyperlink targets? the size (either rows or cols) of the target(s)? What are the other sheets you are referencing,. e.g., Number10 sheet, Number9 sheet? (sorry about the bad explanation, if the score is 9 than display on "Number9" sheet, if the score is 10 or more display on "Number10Above" sheet)

You realize that until you provided real data, the “score” had never been mentioned. In looking at what you entered on Number9 sheet and Number10AndAbove sheet, this is a new request. My understanding of what you want:
For any row in sheet1 that has a hyperlink and, thus, a target data block and “score”, you want selected information to be copied to either the Number9 sheet or the Number10AndAbove sheet depending on the particular score.
It appears that the data to be copied is Index#, Name, House Address, Number and Colour. There are two blank fields between Number and Colour. I assume that Index#, Name, House Address and Colour come from Sheet1. Where do I find “Number” or is this the same as “Score”The easiest way to implment this requrest is via another command button

Nurofen
10-26-2009, 04:28 AM
can the preview and print function be transfered to word (so they preview is in word and the print function places the data in word than prints- reason: due to merged cell when preview the data loses all it formatting.
My experience with moving data between Excel and Word is that the formatting is worse than before. Initiating a Word process from Excel and dumping the data into a word doc is easy, but you end up with word tables (which are messy) and synchronizing control between two programs as you have requested is tricky. I do not understand the formatting problem you mentioned; the print preview in Excel preserves all formatting. Can you add some real data to the spreadsheet and attach it to your reply so I can see what the problem is. Perhaps there is an easy solution without involving Word.

I will trust your experience mate, (attached data, it seems all the formatting is kept, it's a resizing problem)

I have looked at the real data you provided. It appears that every hyperlink target is the same size: 19 rows by 10 cols. If that is true, we can easily solve the size problem. The print preview and ultimate print scale factor is being set in the current spreadsheet such that the output is 1 page wide and 1 page tall -- as I thought you wanted. As the number of data blocks increases, the whole thing gets narrower because it has to fit on a single page. Moving this data to Word will not help the situation. You previously mentioned that you wanted all data blocks associated with a particular name to be on a single sheet/page. (Sorry misunderstanding from my end: What I understood was one name and data range pre printed sheet - sorry about that) We can put everything on a single sheet, but if there are more than 4 blocks, any subsequent printout will be need more than a single page. Before I revise the Print Data function, I want to ensure that my assumptions are correct, in particular that all target blocks are 19 rows by 10 cols. (The target blocks are always 10 cols, but the 19 rows can change depending on the amount of data to be inserted)



I've just had a add on : on the LinkSheet if the data is 10 or more I would like the index number, name, number and colour carried over to the Number10 and Above sheet, if the number is 9 than carried over to the Number9 sheet. (can they made to be auto-updated when numbers are changed on the data sheet) Sorry, I have no idea what you are asking for here. What data -- the hyperlink target? 10 or more what? 9 what? Are these number some reference to the index numbers? the number of hyperlink targets? the size (either rows or cols) of the target(s)? What are the other sheets you are referencing,. e.g., Number10 sheet, Number9 sheet? (sorry about the badexplanation, if the score is 9 than display on "Number9" sheet, if the score is 10 or more display on "Number10Above" sheet)

You realize that until you provided real data, the “score” had never been mentioned. In looking at what you entered on Number9 sheet and Number10AndAbove sheet, this is a new request. My understanding of what you want:
· For any row in sheet1 that has a hyperlink and, thus, a target data block and “score”, you want selected information to be copied to either the Number9 sheet or the Number10AndAbove sheet depending on the particular score. (Yes)
· It appears that the data to be copied is Index#, Name, House Address, Number and Colour. There are two (There are five now (for later data additions)) blank fields between Number and Colour. I assume that Index#, Name, House Address and Colour come from Sheet1. Where do I find “Number” or is this the same as “Score” Index#, House Address come from sheet1, Colour and Score come from LinkSheet
The easiest way to implment this requrest is via another command button (that will be fine)



Once again thank you for all your help on this.

MWE
10-26-2009, 09:57 AM
Attached is a revised spreadsheet with a "Scores" button. When you click on it, the following happens:
old data in the Number9 and Number10 worksheets is cleared out
each row in Sheet 1 is examined for hyperlinks
if a link is found, the target is examined for "Score" in the appropriate cell. (At present, the code assumes that "Score" will always be in the same cell, but now that I know that the # of rows in a block can change, the next version will not assume a particular row. Will "Score" always be in the same col?)
if "Score" is found, the appropriate numerical value is read (currently assumed to be in the same row and 3 cols over. Will that be consistent?)
based on the score, the relevant information from sheet1 and the score are posted to the appropriate sheet.Regarding the print preview and printout. If you want one name and block per page, the sizing problem is easily solved. We can still place all the blocks associated with a name on the Preview sheet and then add a pagebreak after each block. I will have that done in version 9

Nurofen
10-26-2009, 03:37 PM
NOTE: the size has of the data table had changed from A:K to A:O, If you indicate where I make the changes in VBA I will do that, I assume there is no change required as the VBA reads the range from the link on sheet1?


Attached is a revised spreadsheet with a "Scores" button. When you click on it, the following happens: Values 10 or higher works right, but the 9 should not bring values less than 9 only 9.- if indicate where I change this I will try to do it myself
old data in the Number9 and Number10 worksheets is cleared out
each row in Sheet 1 is examined for hyperlinks
if a link is found, the target is examined for "Score" in the appropriate cell. (At present, the code assumes that "Score" will always be in the same cell, but now that I know that the # of rows in a block can change, the next version will not assume a particular row. Will "Score" always be in the same col?) Can this be left open to change "Score" will be in either col C,H or M,
if "Score" is found, the appropriate numerical value is read (currently assumed to be in the same row and 3 cols over. Will that be consistent?) No, the value will be in either E,J or O the row may change too, Could you place a note: in the VBA as to where I can change cols & rows, I will try doing myself.
There is also the color to be carried which is in col D but can we agian leave this open for change to col F, if you point this out in VBA I will try change it.
based on the score, the relevant information from sheet1 and the score are posted to the appropriate sheet. If I need to add a new sheet and add a new search - rather than an addon could you in the next version add extra comments in the VBA and I will try do it myself and will ask you guidence if I get stuck Regarding the print preview and printout. If you want one name and block per page, the sizing problem is easily solved.(prefect) We can still place all the blocks associated with a name on the Preview sheet and then add a pagebreak after each block. I will have that done in version 9 (thanks)

mdmackillop
10-26-2009, 05:02 PM
I don't think it is quite that simple. (Post #4)
You never said a truer word!!!:whistle:

MWE
10-26-2009, 08:26 PM
NOTE: the size has of the data table had changed from A:K to A:O, If you indicate where I make the changes in VBA I will do that, I assume there is no change required as the VBA reads the range from the link on sheet1?
Data table width is variable, # cols is defined by variable NumCols in Sub PreviewData. For testing purposes, I expanded the size of the data blocks in LinkSheet and changed the hyperlinks in Sheet1 to reflect the new widths.


Attached is a revised spreadsheet with a "Scores" button. When you click on it, the following happens: Values 10 or higher works right, but the 9 should not bring values less than 9 only 9.- if indicate where I change this I will try to do it myself
Fixed in the attached

old data in the Number9 and Number10 worksheets is cleared out
each row in Sheet 1 is examined for hyperlinks
if a link is found, the target is examined for "Score" in the appropriate cell. (At present, the code assumes that "Score" will always be in the same cell, but now that I know that the # of rows in a block can change, the next version will not assume a particular row. Will "Score" always be in the same col?) Can this be left open to change "Score" will be in either col C,H or M, If col is consistently C, H or M, one need only change the value of variable NColScoreT in he Scores sub. If col can vary depending on the particular data block, then it makes more sense to interrogate the entire block for the cell with "Score" in it.

if "Score" is found, the appropriate numerical value is read (currently assumed to be in the same row and 3 cols over. Will that be consistent?) No, the value will be in either E,J or O the row may change too, Could you place a note: in the VBA as to where I can change cols & rows, I will try doing myself. Same concern as above, i.e., consistency. The location of the numericial value for score must be consistently in a particular col (same row as "Score" text) or consistently the same offset from the "Score" text. If numerical value is consistently in a particular col, the variable NColScoreN is relevant.

There is also the color to be carried which is in col D but can we agian leave this open for change to col F, if you point this out in VBA I will try change it. Was part of the previous version but not mentioned in the thread.

based on the score, the relevant information from sheet1 and the score are posted to the appropriate sheet. If I need to add a new sheet and add a new search - rather than an addon could you in the next version add extra comments in the VBA and I will try do it myself and will ask you guidence if I get stuck. There are basic comments in the code already. Take a look and see if you need more info
Regarding the print preview and printout. If you want one name and block per page, the sizing problem is easily solved.(prefect) We can still place all the blocks associated with a name on the Preview sheet and then add a pagebreak after each block. I will have that done in version 9 (thanks)

the Preview in version 9 works as it should based on your most recent needs and the new size of the text blocks. Not knowing how wide the new cols will be, the scale is correct for the col widths I selected (for the new cols). Once you have decided on what they actually will be, manually adjust the % Nominal Size in PageSetUp until you have what you want and then change the "zoom" factor in the PreviewPrep sub. The preview function seems to take longer than I would expect (~ 5 seconds for the rather limited test data).

I do not think we really need a separate Print Data function any more. You can simply print the Preview sheet

Finally, a fair fraction of the VBA code is brute force and was done without much attempt to be elegant or compact while you figured out what you really wanted. Thus there is some cleanup and streamlining that might be appropriate.

Nurofen
11-01-2009, 02:26 PM
The Thread has been solved, Thank for all your help MWE.

MWE, sorry the delayed reply mate,

Note: the resizing works prefectly mate (Thank you)

1) prefect
2) Great
3) There is now a fixed Col for "Score" (sorted thanks)
4) (sorted thanks)
5) no problem, they no longer require Colour. (but I will still try to get it to work for my own understanding, will ask for help if unable to work this out)
6) Thanks will defo ask, if you don't mind.


Finally, a fair fraction of the VBA code is brute force and was done without much attempt to be elegant or compact while you figured out what you really wanted. Thus there is some cleanup and streamlining that might be appropriate. seems to working prefectly the way it is mate brute force style, not sure what you mean by streamling, I see cleanup in the code is it ok or does it need to be added to? (not sure what it for).

Thanks for all your help mate

Nurofen

MWE
11-02-2009, 10:39 AM
Sounds like you have things under control. The "streamlining" or "clean up" would be to optimize the code now that we know what is required. There are a few places where the code could be a little better. But as long as it is working for you, we can leave it alone (for now?)

Please recontact me if you need additional help.

Nurofen
11-02-2009, 03:27 PM
MWE,

No problem we can leave it alone as it does what it says on the box/tin.

will be working on color this weekend will get back to if any questions.

should I continue in this tread regards to the questions about color or should I start a new tread ? Lucas...if your out there mate.


thank for your time and effect mate trust very greatful.


nurofen