Sort an array to use in a listbox on a userform in Excel 2007
first, make the following change in my code to get it to run in less steps.
from
For j = 1 To UBound(sfiles)
to
For j = (i + 1) To UBound(sfiles)
I don't understand you last question. Why don't you always sort on the last
column?
Do you want to extract the number from the full string?
MyString = "c:\AB 123.xls"
'remove folders
Do While InStr(MyString, "\") 0
MyString = Mid(MyString, InStr(MyString, " ") + 1)
Loop
'check for blanks'
Do While InStr(MyString, " ") 0
MyString = Mid(MyString, InStr(MyString, " ") + 1)
Loop
'remove extension
If InStr(MyString, ".") 0 Then
MyString = Left(MyString, InStr(MyString, ".") - 1)
End If
"Renate" wrote:
Hi Joel,
Thanks for your post. I've used your code inside mine and the result
when looping lbound(sfiles,2) to ubound(sfiles,2) is
123 : c:\AB 123.xls
234 : c:\AB 234.xls
c:\999.doc : 999
What I would like to have as a result is:
when sorting on the second column:
c:\AB 123.xls 123
c:\AB 234.xls 234
c:\999.doc 999
when sorting on the first column:
c:\999.doc 999
c:\AB 123.xls 123
c:\AB 234.xls 234
Any thoughts on how to change your code to get this result?
On 22 jun, 05:51, Joel wrote:
Sub sortarray()
Dim sfiles(8, 2) '<= this line is optional depending how your array is
defined
SortRow = 0 'could be 0 or 1
For i = 0 To (UBound(sfiles) - 1)
For j = 1 To UBound(sfiles)
If sfiles(i, SortRow) sfiles(j, SortRow) Then
'swap data items
temp = sfiles(i, 0)
sfiles(i, 0) = sfiles(j, 0)
sfiles(j, 0) = temp
temp = sfiles(i, 1)
sfiles(i, 1) = sfiles(j, 1)
sfiles(j, 1) = temp
End If
Next j
Next i
End Sub
"Renate" wrote:
Hi guys,
Please bear with me, I can't get this sorting the way I need it to
work. I've serached on the archives, but somehow I couldn't find an
example that works for my array.
The array contains filenames including the path etc and some sort of
description which I create from leaving parts out of the filename,
for example:
sFiles(0,0) = "c:\AB 123.xls"
sFiles(1,0) = "123"
sFiles(0,1) = "c:\AB 234.xls"
sFiles(1,1) = "234"
sFiles(0,2) = "c:\AB 898.xlam"
sFiles(1,2) = "898"
sFiles(0,2) = "c:\999.doc"
sFiles(1,2) = "999"
I use this data in a listbox with two columns. Only one of the columns
is visible to the user. I want to create an option on the userform to
sort the listbox on either the fullname or on the description.
How can I archieve this?
|