PDA

View Full Version : Sorting arrays



Chunk
06-04-2019, 07:08 AM
Good morning VBA masters,

I have a userform that 大灰狼1976 (http://www.vbaexpress.com/forum/member.php?70849-%26%2322823%3B%26%2328784%3B%26%2329436%3B1976) so graciously redesigned for me. I am new to arrays and would like the arrays being presented in the combo boxes to be sorted in ascending order.

I have scoured the internets with no luck. Attached is the file I am working with. Any help is greatly appreciated.

If you have time or links, I would like to learn "how" to do this as well.

Again thank you.

24352Chunk

Paul_Hossler
06-04-2019, 07:42 AM
'http://www.cpearson.com/excel/SortingArrays.aspx

QSortInPlace







Option Explicit
Public arrMst As Variant, dMst As Object, dTmp As Object
Private Sub UserForm_Activate()
Dim i As Long, s As String
Dim v() As Variant

arrMst = Sheets("data").[a1].CurrentRegion

Set dMst = CreateObject("scripting.dictionary")
Set dTmp = CreateObject("scripting.dictionary")

For i = 2 To UBound(arrMst)
s = arrMst(i, 4)
dMst(s) = dMst(s) & "," & arrMst(i, 2)
dTmp(s) = dTmp(s) & "," & i
Next i

v = dMst.keys ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Call QSortInPlace(v)
cbo_proj.List = v

End Sub

大灰狼1976
06-04-2019, 07:09 PM
Hi Chunk!
If the actual data of "PROJECT" is large and complex, In addition to sorting,
there is another way, you can enter part of "PROJECT" in cbo_proj and try it out.
Please refer to the attachment.