Hi,
I have a workbook which has worksheets labeled as follows: "1_1", "1_2", "1_3"......etc......."2_1", "2_2", "2_3", ........etc
In my program i have to assume that the worksheets are "in order" ie...."1_1" is before "1_2" and "1_2" is before "1_3" and so on.
I want to write some code to do a quick check on the worksheet names to make sure they are "in-order". Since the names are text not sure the best way to do this........
Any ideas?
If the sheets are named like that, probably the easiest thing to do would be to loop through all the worksheets converting the name to a decimal number by replacing the "_" with a "." and adding to an array. You can then pass the array to a function to check if it is ordered - Chip Pearson has 1 such function on his website, a simple search "pearson test array sorted" should find that page.
Excuse the brevity, using a tablet so what code is there was typed freehand...
Please Login or Register to view this content.
PS: I did try to update this in case you have names like 1_2_1 but the piggin' firewall objected and I lost the edited reply... Post back if that is the case and I'll try again.
Last edited by cytop; 04-27-2017 at 02:38 PM.
well, i tried converting "1_1" to a numerical value of 1.1
Problem is ...........when converting "1_10" to a number i get the following 1.1
Ahhh, yes...
No problem.
Split the sheet names using the "_" as the delimiter and then convert firstly the 'Major' number to a value and then add the 'Minor' number, converted to a value and divided by 100, or 1000 if your workbook is crazy enough to have that many sheets.
You still need Chip Pearsons' Array function but I have tested it and it works as long as:Please Login or Register to view this content.
- You are checking all the sheets.
- They are all numbered in the same manner
- There's only 1 level of 'minor' numbers
Multiple levels of minor numbers (1_2_1) can be accommodated with another little tweak of the code...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks