Re: [ExcelVBA] Problem: sort sheets according to two sets of criteria

Hi again, Paul
>
> Glad I could help.

You sure did! As I said, I think about you at least 2 times a day!
It saves me so much time that I sort more often.
>
> As for the "X" in:
>
> If (Sheets("Source").Cells(R, "B").Value & "X" <> "X") Then
>
> I manage nearly 100,000 lines of VBA code spread across several applications!

Hum, how do you count them?
I am not only joking: is there a way the see the line numbers in VBE?
There are still lots of features in the VBE that I do not use because I don't know what they are for or how they work.

I am sure I would be more efficient if I mastered this.

I write all my subs first in Text Pad, and I do appreciate the line numbers
(and also all the pieces of code that I can store in the libraries)
> As you can imagine, troubleshooting problems can be difficult.

This reminds me of an old question a few years ago, with subject : How do you debug?
I did not expect so many replies, but there were, and this helped me a lot!

>
> On occasion, I've encountered cases where the "value" in a cell is "null".
.............

> .. unless someone asks...

Well I asked and got a good trick that I will use, because as a matter of fact, this has happened to me, and I always found workarounds that were time consuming.

> --------------------------------------------------------------------------------------------
>
> The other "trick" to speed things up is that I turn off screen updating.
As for screen updating turned off, maybe it was not written in the sample book, but I use this all the time.
It does not only save time, even if it is the main reason, with screen updating off, you don't see things that I not necessarily pretty (on a hidden sheet for example). I am glad that I do not work for a client: my pages are not always good looking.

As well as calculation turned to manual.
When I need to refresh 10 queries per page on 40 pages, if it calculated each time, not only it would take so long, but some results would present a error if all the data are not yet in.

>
> --------------------
> Sorting
> --------------------
> What I did was perform what is called a "bubble sort".

I had found "bubble sort" in my long googling hours, but no example helped me understand how to do it, at least in the case I needed.
The examples were applied to too simplistic tasks.

> I loaded the array with sheet names and values.
> I then cycled through the array from first to last and compared the "value" from
> one sheet to the next.
> If the values need to be reversed, I load one set of array contents into the
> "bubble" and use this to swap values.
>
> I COULD have combined the array sort and sheet move into one loop.
> However, that means that whenever the "values" require the sheets to be
> "swapped", the actual sheets would be moved, which would take WAY more
> processing time.
>
> ----------------
> To be honest, we COULD have simply combined the data into one sheet, sorted
> it in the sheet, and then used VBA to move the sheets into the proper order.

This was what I was starting to do when I thought I could not do it, but I also prefer VBA solutions.

What you did - and it did not come to my mind - is that you did it in 2 steps:
First sort all according to criteria 2, and only after bring back the pages I needed first according to criteria 1.
This made every thing much more simple.
>
> But... I like VBA solutions best!
>
> let us know if you need anything else...

I sure will.
But I really try to find my solutions before I ask.
When I came here first, I knew so little that I did not even know which words to google for.
Now I am more efficient at finding answers on my own.
But if you saw all my code lines, you would probably want to re write a lot!


>
> Paul
> -----------------------------------------
> "Do all the good you can,
> By all the means you can,
> In all the ways you can,
> In all the places you can,
> At all the times you can,
> To all the people you can,
> As long as ever you can." - John Wesley
> -----------------------------------------
>

I can see that you act - and probably not only on this group - according to the text that you use as signature.
You are really generous, and a good teacher too.
This is beautiful: another gift from you

Thanks again

Louise

>
>
>
> ________________________________
> From: Louise Gariépy <garilou@cgocable.ca>
> To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
> Sent: Wed, May 1, 2013 10:07:21 PM
> Subject: Fwd: [ExcelVBA] Problem: sort sheets according to two sets of criteria
>
> Hi again Paul!
>
> Fantastic.
> As expected, it worked perfectly at the first run on the sample book.
>
> Then it took me about 30 minutes to adapt it to the real book.
> I had to change the first sorting, because I needed the sheets to be sorted from
> the largest number to the smallest.
> Then I understood why you were using
>>> If (Sheets("Source").Cells(R, "B").Value & "X" <> "X") Then.
>
> And I tried:
> If (Sheets("Source").cells(R."B").value > "") then
> ...
> and it worked well
> (I do not have 1000 sheets, and I hope I will never have so many, so the sub
> runs in about 1/2 sec.
> To do this - without mistakes - it took me sometimes up to 10 minutes (and the
> sheets that were not on "Source" were not sorted).
>
> I thank you so much, I wish so much I could do something to thank you.
>
> The only thing that I could offer to you would be to trade stocks for you.
>
> The "A,B,C..." from the sample book are in fact stock tickers.
> Since I came to this group for the first time, I have not stopped refining my
> indicators, and this new book helps me to size the positions so that my
> portfolio is always fully invested with the best performing stocks, and most of
> all to quickly catch the day winners, bull or bear market!
> With this new book, I am real good, and beat the markets every day by 0.5% to
> 1.5%.
> I am only joking, but my son has invested a few thousand dollars in my "Fund"
> and he is really pleased.
>
> So every night, when I will reallocate my portfolio, I'll have a good thought
> for you.
>
> Till the next time,
>
> Louise
>
>
>
>
>
>
>
>
> Début du message réexpédié :
>
>> De : Louise Gariépy <garilou@cgocable.ca>
>> Objet : Rép : [ExcelVBA] Problem: sort sheets according to two sets of
> criteria
>> Date : 1 mai 2013 20:19:45 HAE
>> À : ExcelVBA@yahoogroups.com
>> Répondre à : ExcelVBA@yahoogroups.com
>>
>> Hi Paul,
>>
>> First, sorry for the empty message.
>> I am not familiar with this mail program, and I do stupid mistakes.
>>
>> Wow!
>>
>> You did much more then I was asking, (I did not ask for a complete code).
>> But the main reason I asked to the group was to come out of my habits and try
>> to learn something new, here it is!
>> I knew I should sort the array, and I did not know how.
>> I am so glad.
>> Now I have concrete to work with.
>>
>> I am not sure if I understand the "X" in:
>>> If (Sheets("Source").Cells(R, "B").Value & "X" <> "X") Then
>>
>>
>> but let me think a little bit.
>>
>> I will study and try the code and give you feedback.
>>
>> Yes I will remove the sheet "Problem Description", but in the real book, I have
>> 4 sheets before the "A,B,C..." sheets to sort.
>>
>> Thank you so much! Once it is done, it will save me so much time and
> mistakes!
>>
>> Louise
>>
>> Le --01052013 à 11:47, Paul Schreiner <schreiner_paul@att.net> a écrit :
>>
>>> Louise,
>>>
>>> Here's what I came up with.
>>> it's not really "efficient", but it gets the job done.
>>> If I knew that the values in A1 of each sheet were NUMERIC,
>>> and the range of values, then I might be able to make it run more
> efficiently.
>>>
>>> Even so, I created some test data (1000 sheets) and it ran in about 5
> seconds.
>>>
>>> Here's what I came up with:
>>> The premise is to load the sheet names and values from (A1) into an array and
>
>>> sort the array based on the A1 values.
>>> Skipping sheets with the name "Source" and "Problem Description".
>>>
>>> Then, move the sheets to the "end" of the workbook based on this array.
>>> Next, move the sheets in your "special" list to the front. (in reverse order)
>>>
>>> Note: I used .Move After:=Sheets(2)
>>> which would put them after the 2nd sheet, which is "Problem Description"
>>> If you remove that sheet, and want the sheets to appear after the first sheet,
>>
>>> then change Sheets(2) to Sheets(1).
>>> -----------------------------------------------------------------------------
>>> Option Explicit
>>> Sub SortSheets()
>>> Dim R, nRows, sht, inx
>>> Dim ShtArray(1500, 1)
>>> Dim SortFlag, LoopCnt
>>> Dim tmpval0, tmpval1
>>> On Error GoTo 0
>>> '------------------------------------------------------
>>> ' Load Sheet Names and Values (from Cell A1) into array
>>> '------------------------------------------------------
>>> inx = -1
>>> For Each sht In Sheets
>>> If ((UCase(sht.Name) <> UCase("Source")) _
>>> And (UCase(sht.Name) <> UCase("Problem description"))) Then
>>> inx = inx + 1
>>> ShtArray(inx, 0) = sht.Name
>>> ShtArray(inx, 1) = sht.Cells(1, 1).Value
>>> End If
>>> Next
>>> '---------------------------------------
>>> ' Sort Array by Values
>>> '---------------------------------------
>>> SortFlag = True
>>> LoopCnt = 0
>>> While SortFlag And LoopCnt < 1000000
>>> LoopCnt = LoopCnt + 1
>>> SortFlag = False
>>> For inx = 0 To UBound(ShtArray) - 1
>>> If ((ShtArray(inx, 0) <> "") And (ShtArray(inx + 1, 0) <> ""))
> Then
>>> If (ShtArray(inx, 1) > ShtArray(inx + 1, 1)) Then
>>> tmpval0 = ShtArray(inx, 0)
>>> tmpval1 = ShtArray(inx, 1)
>>> ShtArray(inx, 0) = ShtArray(inx + 1, 0)
>>> ShtArray(inx, 1) = ShtArray(inx + 1, 1)
>>> ShtArray(inx + 1, 0) = tmpval0
>>> ShtArray(inx + 1, 1) = tmpval1
>>> SortFlag = True
>>> Exit For
>>> End If
>>> End If
>>> Next inx
>>> Wend
>>> '----------------------
>>> ' Move Sheets
>>> '----------------------
>>> Application.ScreenUpdating = False
>>> For inx = 0 To UBound(ShtArray)
>>> If (ShtArray(inx, 0) <> "") Then
>>> Sheets(ShtArray(inx, 0)).Move After:=Sheets(Sheets.Count)
>>> Else
>>> Exit For
>>> End If
>>> Next inx
>>> '-----------------------------------------------
>>> ' Move first sheets from "First" list to front
>>> '-----------------------------------------------
>>> nRows = Sheets("Source").Cells(1, 1).SpecialCells(xlLastCell).Row
>>> For R = nRows To 1 Step -1
>>> If (Sheets("Source").Cells(R, "B").Value & "X" <> "X") Then
>>> ' Sheets(Sheets("Source").Cells(R, "B").Value).Move
>>> Before:=Sheets(1)
>>> Sheets(Sheets("Source").Cells(R, "B").Value).Move After:=Sheets(2)
>>> End If
>>> Next R
>>> Application.ScreenUpdating = True
>>> Sheets("Source").Select
>>> MsgBox "Finished"
>>> End Sub
>>>
>>> -----------------------------------------------------------------------------
>>> Paul
>>> -----------------------------------------
>>> "Do all the good you can,
>>> By all the means you can,
>>> In all the ways you can,
>>> In all the places you can,
>>> At all the times you can,
>>> To all the people you can,
>>> As long as ever you can." - John Wesley
>>> -----------------------------------------
>>>
>>>
>>>
>>>
>>> ________________________________
>>> From: Louise Gariépy <garilou@cgocable.ca>
>>> To: ExcelVBA@yahoogroups.com
>>> Sent: Wed, May 1, 2013 7:05:35 AM
>>> Subject: Re: [ExcelVBA] Problem: sort sheets according to two sets of
> criteria
>>>
>>>
>>> Hi, David,
>>> Thank you for trying.
>>> But this does not solve my problem.
>>> Or in other words, yes this list that you suggest was generated in the sheet
>>> "Problem description". (Columns M and N)
>>>
>>> I was hoping to find a method to go directly from the columns B and C,
>>> (Criteria
>>>
>>> 1) and E and F (criteria 2) to the sheet sorting process.
>>> I guess if the whole cannot be done in one step, I will keep on with my not too
>>>
>>> elegant program that I have started, generating all columns that were
>>> illustrated in that sheet.
>>>
>>> From there on, it will not be too difficult with the:
>>> sheets(s).move after.
>>> to move the sheets with the largest numbers to the end.
>>>
>>> I guess that if I could not find the solution while googling for so long, it
>>> was
>>>
>>> because there was no way to do it.
>>> Thanks anyway.
>>> This gave me the opportunity to say hello to everyone ;-)
>>>
>>> Louise
>>>
>>> Le --27042013 à 20:29, David Grugeon <yahoo@grugeon.com.au> a écrit :
>>>
>>>> Hi Louise
>>>>
>>>> I think the easiest way to do this is to create a list of the sheet names
>>>> in order, then step through this list in order moving each sheet to the
>>>> end. I think you would use something like
>>>>
>>>> Sheets(s).move after:=Sheets(Sheets.Count)
>>>>
>>>> Put this in a loop which picks up the next sheetname as s.
>>>>
>>>>
>>>> On 28 April 2013 09:27, garilou <garilou@cgocable.ca> wrote:
>>>>
>>>>> Hi group, and all the genius programmers!
>>>>>
>>>>> I have not been asking much for quite a long time, but I keep programming
>>>>> almost every day.
>>>>>
>>>>> David has put on the group page a sample sheet that I have prepared to
>>>>> better explain what I am trying to do.
>>>>> As I told David, I have Googled for days, and all what I found were
>>>>> programs to sort alphabetically, which is not what I need.
>>>>> ==============================
>>>>>
>>>>> I have a workbook with more almost 40 data sheets.
>>>>>
>>>>> I must rearrange those sheets at least once a day: when there were only 5
>>>>> to 10, I made it manually, but it took a long time, and I made so many
>>>>> mistakes that the rest of the tasks did not work.
>>>>>
>>>>> The sheets must be ordered according to 2 sets of criteria.
>>>>>
>>>>> Between 1 and maximum 10 sheets must be ordered first, in an order
>>>>> determined on another sheet (« source »). (Criteria 1)
>>>>>
>>>>> The other ones must be ordered according to a number (between 20 and 100)
>>>>> that I have set in cell A1 from every sheet of the sample book. (Criteria
> 2)
>>>>>
>>>>> For the sake of this sample, I have created those values with a random
>>>>> formula.
>>>>>
>>>>> "In real life", this could be more complicated, because some sheets could
>>>>> have the same value in cell A1, but those could be placed side by side,
>>>>> with no special order.
>>>>>
>>>>> The names on sheet «Source » are different every day, as well as the value
>>>>> in Cell A1 of the other sheets.
>>>>>
>>>>> I do not expect any one to write the whole program for me, but if I could
>>>>> get some methods to follow, steps to go through, some keywords that I could
>>>>> Google that would help me find a solution.
>>>>>
>>>>> Then I might try and come back with more specific questions.
>>>>>
>>>>> Thank you to all who will be so kind to look at this.
>>>>>
>>>>> Louise
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> ------------------------------------
>>>>>
>>>>> ----------------------------------
>>>>> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
>>>>> here: http://www.mousetrax.com/techtrax to enter the ezine, then search
>>>>> the ARCHIVES for EXCEL VBA.
>>>>>
>>>>> ----------------------------------
>>>>> Visit our ExcelVBA group home page for more info and support files:
>>>>> http://groups.yahoo.com/group/ExcelVBA
>>>>>
>>>>> ----------------------------------
>>>>> More free tutorials and resources available at:
>>>>> http://www.mousetrax.com
>>>>>
>>>>> ----------------------------------Yahoo! Groups Links
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>> [Non-text portions of this message have been removed]
>>>>
>>>>
>>>>
>>>> ------------------------------------
>>>>
>>>> ----------------------------------
>>>> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here:
>>>>
>>>> http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES
>>>
>>>> for EXCEL VBA.
>>>>
>>>>
>>>> ----------------------------------
>>>> Visit our ExcelVBA group home page for more info and support files:
>>>> http://groups.yahoo.com/group/ExcelVBA
>>>>
>>>> ----------------------------------
>>>> More free tutorials and resources available at:
>>>> http://www.mousetrax.com
>>>>
>>>> ----------------------------------Yahoo! Groups Links
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>> [Non-text portions of this message have been removed]
>>>
>>>
>>>
>>> ------------------------------------
>>>
>>> ----------------------------------
>>> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here:
>>> http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES
>>> for EXCEL VBA.
>>>
>>>
>>> ----------------------------------
>>> Visit our ExcelVBA group home page for more info and support files:
>>> http://groups.yahoo.com/group/ExcelVBA
>>>
>>> ----------------------------------
>>> More free tutorials and resources available at:
>>> http://www.mousetrax.com
>>>
>>> ----------------------------------Yahoo! Groups Links
>>>
>>>
>>>
>>
>>
>>
>> ------------------------------------
>>
>> ----------------------------------
>> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here:
>> http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES
>> for EXCEL VBA.
>>
>>
>> ----------------------------------
>> Visit our ExcelVBA group home page for more info and support files:
>> http://groups.yahoo.com/group/ExcelVBA
>>
>> ----------------------------------
>> More free tutorials and resources available at:
>> http://www.mousetrax.com
>>
>> ----------------------------------Yahoo! Groups Links
>>
>>
>>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> ----------------------------------
> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here:
> http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES
> for EXCEL VBA.
>
>
> ----------------------------------
> Visit our ExcelVBA group home page for more info and support files:
> http://groups.yahoo.com/group/ExcelVBA
>
> ----------------------------------
> More free tutorials and resources available at:
> http://www.mousetrax.com
>
> ----------------------------------Yahoo! Groups Links
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> ----------------------------------
> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
>
> ----------------------------------
> Visit our ExcelVBA group home page for more info and support files:
> http://groups.yahoo.com/group/ExcelVBA
>
> ----------------------------------
> More free tutorials and resources available at:
> http://www.mousetrax.com
>
> ----------------------------------Yahoo! Groups Links
>
>
>



------------------------------------

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ExcelVBA/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/ExcelVBA/join
(Yahoo! ID required)

<*> To change settings via email:
ExcelVBA-digest@yahoogroups.com
ExcelVBA-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
ExcelVBA-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/