>>The use of multiple sort keys in a sort will depend entirely on the code you
> are using.
> Wel.... er ... yes... it would wouldn't it... :-)
Yes it does and your message read as though you already had sort code but needed to make it work with multiple keys.
> No got anything working at all yet Dave.
Don't care whether it's working or not, but need to know what code you're looking at.
> I've spent quite some days scouring the internet for examples and not found anything suitable.
I don't know what you've been searching. I just did a search on: vba sort algorithm
The first hit www.cpearson.com/excel/qsort.htm is a comprehensive sort algorithm that is intended to be general-purpose and contains lots of comments to describe what it's doing. It currently only works off a single key, but that could be a concatenated key. Alternatively, it could easily be modified to provide the keys separately to its QSortCompare function.
There were many other useful hits too.
No matter what, you are going to need to tailor the code to your specific array. I very much doubt that you will be able to find general-purpose code that will handle the dimensions of your array, plus your requirement for many keys. A Q(uick)Sort is quite small, though, and can be adapted easily enough.
=======================
There is the question "why bother", of course. Excel has strong sorting capabilities for data in worksheets, and the later Excel versions allow many more than the 3 key limit in 2003. Access also has stong(er) sorting capabilities for rows out of tables.
Don't know about Word, but I've never wanted to sort things there anyway. :-)
Regards, Dave S
----- Original Message -----
From: <1z@compuserve.com>
To: <ExcelVBA@yahoogroups.com>
Sent: Saturday, January 07, 2012 12:11 AM
Subject: Re: [ExcelVBA] Sorting in VBA
>
>
>>The use of multiple sort keys in a sort will depend entirely on the code you
> are using.
> Wel.... er ... yes... it would wouldn't it... :-)
>
> No got anything working at all yet Dave.
> I've spent quite some days scouring the internet for examples and not found anything suitable.
>
> I'll post the code I have to concatonate key fields below.. but the question was... will concatonating fields and sorting give me the same results as say... a multi key sort on an Excel sheet where items are sorted within items within items etc.
>
> Lisa
>
>
> PS... It looks like the copy/paste has destroyed the code formatting!!!! Bum!
> subSelectionSortStrings is a proc to just sort a one dimension string array.
>
> Sub subSortAcrossArrayInSelectedOrder( _
> spArray() As String, _
> spOrder As String _
> )
> ' Sort over all elements in a specific order.
> ' 1) Put all elements in a line together
> ' orderd as specified..
> ' 2) Sort it.
> ' 3) Put elements back in original format.
> '
> ' Ex spOrder = "1 4 0 3 2".
> ' Not all elements need be specified.
> ' The rest are tacked on the end in order.
> '
> Dim ilN As Integer
> Dim lnglCompareType As Long
> Dim lnglM As Long
> Dim lnglMax As Long
> Dim lnglMin As Long
> Dim lnglN As Long
> Dim lnglNumberOfElements As Long
> Dim lnglOrder As Long
> Dim lnglRow As Long
> Dim lnglRows As Long
> Dim lnglUBA As Long
> Dim lnglUBB As Long
> Dim slLine As String
> Dim slLineArray() As String
> Dim slLineLineArray() As String
> Dim slNumbers As String
> Dim slOrderArray() As String
> Dim slOrderArrayA() As String
> Dim slOrderArrayB() As String
>
> lnglRows = UBound(spArray, 1)
> lnglMin = LBound(spArray, 2)
> lnglMax = UBound(spArray, 2)
> lnglNumberOfElements = lnglMax
> slOrderArrayA = Split(spOrder, " ")
> lnglUBA = UBound(slOrderArrayA)
> slNumbers = ""
>
> For lnglN = 0 To lnglMax
> slNumbers = slNumbers & CStr(lnglN) & " "
> Next lnglN
> slNumbers = Trim(slNumbers)
> For lnglN = 0 To UBound(slOrderArrayA)
> slNumbers = Replace(slNumbers, slOrderArrayA(lnglN), "")
> Next lnglN
> slNumbers = Trim(fncStripDoubleSpaces(slNumbers))
> slOrderArrayB = Split(slNumbers, " ")
> lnglUBB = UBound(slOrderArrayB)
> ReDim slOrderArray(UBound(slOrderArrayA) + UBound(slOrderArrayB) + 1)
> For lnglN = 0 To lnglUBA
> slOrderArray(lnglN) = slOrderArrayA(lnglN)
> Next lnglN
> For lnglN = 0 To lnglUBB
> slOrderArray(lnglN + lnglUBA + 1) = slOrderArrayB(lnglN)
> Next lnglN
> ' Anything in the array?
> If lnglMin = lnglMax Then
> Exit Sub
> End If
> ' Order Ascending.
> lnglOrder = -1
> ' Not Case sensitive.
> lnglCompareType = vbTextCompare
> ReDim slLineArray(lnglRows)
> For lnglRow = 0 To lnglRows
> slLine = ""
> For lnglM = 0 To lnglMax
> slLine = slLine & spArray(lnglRow, CInt(slOrderArray(lnglM))) & ";"
> Next lnglM
> slLine = Left(slLine, Len(slLine) - 1)
> slLineArray(lnglRow) = slLine
> Next lnglRow
> ' Sort it.
> subSelectionSortStrings slLineArray()
> ' Recreate the original array.
> ReDim spArray(lnglRows, lnglMax)
> For lnglRow = 0 To lnglRows
> slLineLineArray = Split(slLineArray(lnglRow), ";")
> For lnglM = 0 To UBound(slLineLineArray)
> ' slLine = slLine & spArray(lnglRow, CInt(slOrderArray(lnglM))) & ";"
>
> spArray(lnglRow, CInt(slOrderArray(lnglM))) = slLineLineArray(lnglM)
> Next lnglM
> Next lnglRow
> ' ***********************************************************************
> End Sub
>
>
> Subject: Re: [ExcelVBA] Sorting in VBA
>
>
>
> The use of multiple sort keys in a sort will depend entirely on the code you
> re using. Could you post it please?
> Regards, Dave S
>
>
>
>
> [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
>
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 10.0.1416 / Virus Database: 2109/4125 - Release Date: 01/05/12
>
[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