Array Sort

[John]

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

 

[Jezebel]

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.

 

[Helmut Weber]

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]

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

 

[John]

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

 

[Helmut Weber]

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]

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

 

[Doug Robbins]

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


--
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

 

[Howard Kaikow]

There's no Sorted property for a VBA Listbox.

 

[Howard Kaikow]

Don't use a bubble sort.

See http://www.standards.com/index.html?Sorting

 

[John]

Cool, thanks

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

SNIP