Louise,
Glad I could help.
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!
As you can imagine, troubleshooting problems can be difficult.
On occasion, I've encountered cases where the "value" in a cell is "null".
For instance:
you have a function on your spreadsheet that says:
=if(A1="TEST","yes","")
you copy this down the page.
Every place where the value in column "A" is NOT "TEST", "" is placed in the
cell (which is "blank", or "null")
You then copy->paste special->Values.
If you were to select the first row of the column that had this formula, and hit
End-DownArrow,
It SHOULD take you to the first non-blank row.
However, you'll find that Excel thinks that all the cells in that column have a
value!
The fact is, there IS a value. It's the "null" character (CHAR(0)).
So... if a value in cell B1 is "null", it's actually not blank!
So, if I were to use:
if (sheets(1).cells(1,"B").Value <> "") then
it actually tests to see if B1 is "empty", but it's NOT, it has a Null character
in it!
So, instead of my macro "skipping" the cell, it processes it.
The other problem that I've run into is when a variable is a string variable or
a numeric variable.
"comparison" operators sometimes do funny things with the two variable types.
So, in my IF() test, I convert the value to a string by "concatenating" a string
to it ("X")
then, compare it to a string containing just the concatenated string.
if (SomeVariable & "X" = "X") then
converting a "null" character to a "string" effectively converts to a "blank".
So, if "SomeVariable" is a blank, or null, then the IF() statement becomes:
IF ("X" = "X") then
and it "recognizes" that the variable (or cell) is blank!
I've come across this problem often enough, that whenever I wish to test for a
blank cell, I always handle it this way without even thinking about it.. unless
someone asks...
----------------------------------------------------------
The other "trick" to speed things up is that I turn off screen updating.
Basically, when you move a sheet from one position to the end of the workbook,
normally you'd want to "see" it happen.
Refreshing the screen takes TIME.
Do it once or twice and it's no big deal (you'll see the display refresh as it
switches back-and-forth between sheets)
But you're not really interested in seeing the program work.
You're just interested in the end result.
So I turn off screen updating, run the macro, then refresh the screen to see the
end result.
--------------------
Sorting
--------------------
What I did was perform what is called a "bubble sort".
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.
But... I like VBA solutions best!
let us know if you need anything else...
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" <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]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (2) |
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
----------------------------------
Tidak ada komentar:
Posting Komentar