Paul!!!
Absolutely!!
1z@compuserve.com
And thank you!!!
Hugs.....
Lisa
Sent: Mon, Jan 9, 2012 7:34 pm
Subject: [ExcelVBA] Re: Sorting in VBA
Lisa,
Sorry I've been delayed in responding to your sorting question.
see that others have stepped up to provide assistance.
may be able to help clear up some of the confusion.
There was a question with regard to preference for sorting from Least
ignificant to Most Significant.
Let's say you had records with two fields:
Z : 1
: 3
: 2
When sorting on the first (most significant) column:
is compared to A, and the records are reversed, resulting in:
: 3
: 1
: 2
Z is compared to C and the records are reversed, resulting in:
: 3
: 2
: 1
An additional iteration would show that the Most Significant field is now
orted.
orting the Next field:
is compared to 2, the records are reversed, resulting in:
: 2
: 3
: 1
You can immediately see that the sort order has been invalidated.
ut, if you were to have sorted the SECOND field (least significant)
hen, by the time you get to the MOST significant criteria, then changes made to
he lesser significant fields would be irrelevant.
Now,
I understand that you have data stored in an array.
he source of the array is irrelevant.
ou COULD extract this data and store it in an Excel worksheet, sort it, then
e-load the array.
ut you SHOULD be able to process the array without loading another application.
I have created two separate macro scenarios.
n one scenario, I sort the array elements themselves. (it requires nested If
tatements, since you will only sort secondary criteria if the previous criteria
s the same)
also created a single-dimension array in which I concatenated the array
ecords IN THE ORDER OF SORT CRITERIA. Then, I reloaded the array.
I was able to get both scenarios to work to get the sort order as described in
our file (I think)
he difference is that: sorting the original array (388 records) took 32
econds.
orting the Concatenated data took only 13 seconds.
his may not be significant NOW, but perhaps if the array gets much larger.
I also think that it may be possible to create a more "flexible" function with
he second option.
hat is, it might be able to pass an array to the function that will control the
rder in which the array elements are concatenated.
I'd like to send you the file so that you can look at it… if either approach
ooks like it might be interesting, you can post it as a solution.
Can I send you the file?
Paul
[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
----------------------------------
Tidak ada komentar:
Posting Komentar