I have a two column array. What is the function to sort it.
Also... what is the lookup function for a two column array?
Thanks
John
There's no built-in sort function. One method is to create a form with a
listbox control with .Sorted set to TRUE. Load the form without displaying
it. Fill the list box using the sort element as the .Item and the
corresponding array index as the .ItemData. Then iterate the .ItemData
collection to retrieve the array indices in sorted order.
Don't understand the second question.
>I have a two column array. What is the function to sort it.
>
[quoted text clipped - 3 lines]
>
> John
Helmut Weber - 01 Jul 2005 03:07 GMT
Hi Jezebel, hi John,
>There's no built-in sort function. One method is to create a form with a
>listbox control with .Sorted set to TRUE.
@Jezebel: Wasn't it,
that there is no "sorted" property of listboxes in VBA, unlike in VB?
@John: Google for "sorting". It's a science of its own.
In this group, to me, Howard Kaikow is the sorting master. ;-)
Maybe, Wordbasic.sortarray is still there and might be sufficient.
BTW, Howard reveals at least one drawback of it.
>Don't understand the second question.
Neither do I.
Greetings from Bavaria, Germany
Helmut Weber, MVP, WordVBA
Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"
John - 01 Jul 2005 03:07 GMT
Thanks Jezebel. Amazing there is no sort function. I wrote a short
bubble sort and discovered there is also no SWAP function lol. Who wrote
this language anyway?
If I wasnt to sawp array(a,1) with Array(B,1) how do you do it?
John
> There's no built-in sort function. One method is to create a form with a
> listbox control with .Sorted set to TRUE. Load the form without displaying
[quoted text clipped - 11 lines]
>>
>>John
Helmut Weber - 01 Jul 2005 03:15 GMT
Hi John,
maybe the creators of this language have left out,
what could be done without much effort by almost everybody.
Though I'd say, I'd welcome such a function, too,
and I think, they just overlooked it.
Myvalue = array(a,1)
array(a,1) = array(B,1)
array(B,1) = Myvalue
Greetings from Bavaria, Germany
Helmut Weber, MVP, WordVBA
Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"
John - 01 Jul 2005 04:01 GMT
Yes, this is what I did to0 instead of swap. Because this is such a
commonly needed function (swap) it is a function in every other language
I've used including 3 or 4 other basics. I usually use a language called
Power Basic which is much easier, faster, compiles smaller etc. than
Visual Basic but, of course, doesn't have the name recognition and
doesn't work with excel handily.
JOhn
> Hi John,
>
[quoted text clipped - 13 lines]
> Win XP, Office 2003
> "red.sys" & Chr$(64) & "t-online.de"
Howard Kaikow - 01 Jul 2005 21:52 GMT
Don't use a bubble sort.
See http://www.standards.com/index.html?Sorting

Signature
http://www.standards.com/; See Howard Kaikow's web site.
> Thanks Jezebel. Amazing there is no sort function. I wrote a short
> bubble sort and discovered there is also no SWAP function lol. Who wrote
[quoted text clipped - 19 lines]
> >>
> >>John
John - 01 Jul 2005 03:11 GMT
Second question: I noticed there IS various Lookup functions but don't
understand how they work. It looked like Lookup("John", Array(),1) found
John in the first column and returnd whatever was in the second column.
Thus x = Lookup("John",Array(),1)
x would = 2 if there was an array value Array(John,2)
Obviously this isn't right.
JOhn
> There's no built-in sort function. One method is to create a form with a
> listbox control with .Sorted set to TRUE. Load the form without displaying
[quoted text clipped - 11 lines]
>>
>>John
Howard Kaikow - 01 Jul 2005 21:51 GMT
There's no Sorted property for a VBA Listbox.

Signature
http://www.standards.com/; See Howard Kaikow's web site.
> There's no built-in sort function. One method is to create a form with a
> listbox control with .Sorted set to TRUE. Load the form without displaying
[quoted text clipped - 11 lines]
> >
> > John
Here's one way:
Procedure to sort a two dimension array:
Dim MyArray() As Variant, i As Integer, j As Integer, m As Integer, n As
Integer, target As Document, newtable As Table, myitem As Range
'Load client data into MyArray
MyArray = ListBox1.List()
' Create a new document containing a table
Application.ScreenUpdating = False
Set target = Documents.Add
Set newtable = target.Tables.Add(Range:=target.Range(0, 0),
numrows:=ListBox1.ListCount, NumColumns:=2)
' Populate the cells of the table with the contents of the array
For i = 1 To ListBox1.ListCount
For j = 1 To 2
newtable.Cell(i, j).Range.InsertBefore MyArray(i - 1, j - 1)
Next j
Next i
' sort the table
newtable.Sort ExcludeHeader:=False, FieldNumber:="Column 2",
SortFieldType:=wdSortFieldDate, SortOrder:=wdSortOrderDescending
i = newtable.Rows.Count
' Get the number of columns in the table of client details
j = 2
' Set the number of columns in the Listbox to match
' the number of columns in the table of client details
ListBox1.ColumnCount = 2
' Define an array to be loaded with the client data
Dim NewArray() As Variant
'Load client data into MyArray
ReDim NewArray(i, j)
For n = 0 To j - 1
For m = 0 To i - 1
Set myitem = newtable.Cell(m + 1, n + 1).Range
myitem.End = myitem.End - 1
NewArray(m, n) = myitem.Text
Next m
Next n
' Load data into ListBox1
ListBox1.List() = NewArray
target.Close wdDoNotSaveChanges
Application.ScreenUpdating = True

Signature
Hope this helps.
Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.
Doug Robbins - Word MVP
>I have a two column array. What is the function to sort it.
>
[quoted text clipped - 3 lines]
>
> John
John - 02 Jul 2005 02:44 GMT
Cool, thanks
John
> Here's one way:
>
[quoted text clipped - 4 lines]
>
> 'Load client data into MyArray
SNIP