Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autosort on an Array
Hi there,
I've got a pretty simple array that is populating on a form. I need some way of sorting the data by 'radius' before it hits the form, can anyone help? My code is: Private Sub CommandButton1_Click() Dim i, j As Integer Dim MyArray As Variant Sheets("InsertCustomerPostcode").Range("B3") = TextBox1 Sheets("InsertCustomerPostcode").Range("B8") = Val(TextBox2) ListBox1.ColumnWidths = "100; 125; 125; 125; 100; 80; 50; 40; 50; 50" j = 2 Count = 0 k = Cells(j, 2) Do Until k = "" Count = Count + 1 j = j + 1 k = Cells(j, 2) Loop ReDim MyArray(Count, 8) i = 0 j = 2 k = Cells(j, 2) Do Until k = "" MyArray(i, 0) = Cells(j, 3) ' Supplier MyArray(i, 1) = Cells(j, 4) ' Supplier MyArray(i, 2) = Cells(j, 5) ' Supplier MyArray(i, 3) = Cells(j, 6) ' Supplier MyArray(i, 4) = Cells(j, 8) ' Supplier MyArray(i, 5) = Cells(j, 9) ' Supplier MyArray(i, 6) = Cells(j, 2) ' Postcode MyArray(i, 7) = Round(Cells(j, 10), 2) ' Distance MyArray(i, 8) = Cells(j, 11) ' Within Radius? i = i + 1 j = j + 1 k = Cells(j, 2) Loop ListBox1.List = MyArray End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autosort on an Array
Private Sub CommandButton1_Click()
Dim i, j As Integer Dim MyArray As Variant Sheets("InsertCustomerPostcode").Range("B3") = TextBox1 Sheets("InsertCustomerPostcode").Range("B8") = Val(TextBox2) ListBox1.ColumnWidths = _ "100; 125; 125; 125; 100; 80; 50; 40; 50;50" j = 2 Count = 0 k = Cells(j, 2) Do Until k = "" Count = Count + 1 j = j + 1 k = Cells(j, 2) Loop ReDim MyArray(Count, 8) i = 0 j = 2 k = Cells(j, 2) Do Until k = "" MyArray(i, 0) = Cells(j, 3) ' Supplier MyArray(i, 1) = Cells(j, 4) ' Supplier MyArray(i, 2) = Cells(j, 5) ' Supplier MyArray(i, 3) = Cells(j, 6) ' Supplier MyArray(i, 4) = Cells(j, 8) ' Supplier MyArray(i, 5) = Cells(j, 9) ' Supplier MyArray(i, 6) = Cells(j, 2) ' Postcode MyArray(i, 7) = Round(Cells(j, 10), 2) ' Distance MyArray(i, 8) = Cells(j, 11) ' Within Radius? i = i + 1 j = j + 1 k = Cells(j, 2) Loop For x = 0 To (Count - 2) For y = 1 To (Count - 1) If MyArray(x, 8) MyArray(y, 8) Then For z = 1 To 8 temp = MyArray(x, z) MyArray(x, z) = MyArray(y, z) MyArray(y, z) = temp Next z End If Next y Next x ListBox1.List = MyArray End Sub " wrote: Hi there, I've got a pretty simple array that is populating on a form. I need some way of sorting the data by 'radius' before it hits the form, can anyone help? My code is: Private Sub CommandButton1_Click() Dim i, j As Integer Dim MyArray As Variant Sheets("InsertCustomerPostcode").Range("B3") = TextBox1 Sheets("InsertCustomerPostcode").Range("B8") = Val(TextBox2) ListBox1.ColumnWidths = "100; 125; 125; 125; 100; 80; 50; 40; 50; 50" j = 2 Count = 0 k = Cells(j, 2) Do Until k = "" Count = Count + 1 j = j + 1 k = Cells(j, 2) Loop ReDim MyArray(Count, 8) i = 0 j = 2 k = Cells(j, 2) Do Until k = "" MyArray(i, 0) = Cells(j, 3) ' Supplier MyArray(i, 1) = Cells(j, 4) ' Supplier MyArray(i, 2) = Cells(j, 5) ' Supplier MyArray(i, 3) = Cells(j, 6) ' Supplier MyArray(i, 4) = Cells(j, 8) ' Supplier MyArray(i, 5) = Cells(j, 9) ' Supplier MyArray(i, 6) = Cells(j, 2) ' Postcode MyArray(i, 7) = Round(Cells(j, 10), 2) ' Distance MyArray(i, 8) = Cells(j, 11) ' Within Radius? i = i + 1 j = j + 1 k = Cells(j, 2) Loop ListBox1.List = MyArray End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autosort on an Array
On Apr 19, 5:40 pm, Joel wrote:
Private Sub CommandButton1_Click() Dim i, j As Integer Dim MyArray As Variant Sheets("InsertCustomerPostcode").Range("B3") = TextBox1 Sheets("InsertCustomerPostcode").Range("B8") = Val(TextBox2) ListBox1.ColumnWidths = _ "100; 125; 125; 125; 100; 80; 50; 40; 50;50" j = 2 Count = 0 k = Cells(j, 2) Do Until k = "" Count = Count + 1 j = j + 1 k = Cells(j, 2) Loop ReDim MyArray(Count, 8) i = 0 j = 2 k = Cells(j, 2) Do Until k = "" MyArray(i, 0) = Cells(j, 3) ' Supplier MyArray(i, 1) = Cells(j, 4) ' Supplier MyArray(i, 2) = Cells(j, 5) ' Supplier MyArray(i, 3) = Cells(j, 6) ' Supplier MyArray(i, 4) = Cells(j, 8) ' Supplier MyArray(i, 5) = Cells(j, 9) ' Supplier MyArray(i, 6) = Cells(j, 2) ' Postcode MyArray(i, 7) = Round(Cells(j, 10), 2) ' Distance MyArray(i, 8) = Cells(j, 11) ' Within Radius? i = i + 1 j = j + 1 k = Cells(j, 2) Loop For x = 0 To (Count - 2) For y = 1 To (Count - 1) If MyArray(x, 8) MyArray(y, 8) Then For z = 1 To 8 temp = MyArray(x, z) MyArray(x, z) = MyArray(y, z) MyArray(y, z) = temp Next z End If Next y Next x ListBox1.List = MyArray End Sub " wrote: Hi there, I've got a pretty simple array that is populating on a form. I need some way of sorting the data by 'radius' before it hits the form, can anyone help? My code is: Private Sub CommandButton1_Click() Dim i, j As Integer Dim MyArray As Variant Sheets("InsertCustomerPostcode").Range("B3") = TextBox1 Sheets("InsertCustomerPostcode").Range("B8") = Val(TextBox2) ListBox1.ColumnWidths = "100; 125; 125; 125; 100; 80; 50; 40; 50; 50" j = 2 Count = 0 k = Cells(j, 2) Do Until k = "" Count = Count + 1 j = j + 1 k = Cells(j, 2) Loop ReDim MyArray(Count, 8) i = 0 j = 2 k = Cells(j, 2) Do Until k = "" MyArray(i, 0) = Cells(j, 3) ' Supplier MyArray(i, 1) = Cells(j, 4) ' Supplier MyArray(i, 2) = Cells(j, 5) ' Supplier MyArray(i, 3) = Cells(j, 6) ' Supplier MyArray(i, 4) = Cells(j, 8) ' Supplier MyArray(i, 5) = Cells(j, 9) ' Supplier MyArray(i, 6) = Cells(j, 2) ' Postcode MyArray(i, 7) = Round(Cells(j, 10), 2) ' Distance MyArray(i, 8) = Cells(j, 11) ' Within Radius? i = i + 1 j = j + 1 k = Cells(j, 2) Loop ListBox1.List = MyArray End Sub- Hide quoted text - - Show quoted text - Thank you! I shall try this today! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autosort on an Array
Oh god, I've been a bit dim. I banged out that post quickly and meant
to say I want it to sort ascendingly by distance!! I've tried manipulating the code, but have it sorting descendingly (barring the first and last entires strangely) by distance.... so kind of almost there... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autosort on an Array
Right, this is sorting ascendingly. There are two failing points
though. For some reason, the LARGEST of values that the array picks up is stuck at the beginning in front of all the ascending stuff and the final value in my array is not being sorted. Will keep banging away though. For x = 0 To (Count - 2) For y = 1 To (Count - 1) If MyArray(x, 7) < MyArray(y, 7) Then For z = 1 To 8 temp = MyArray(x, z) MyArray(x, z) = MyArray(y, z) MyArray(y, z) = temp Next z End If Next y Next x ListBox1.List = MyArray |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autosort on an Array
On Apr 20, 8:56 am, wrote:
Oh god, I've been a bit dim. I banged out that post quickly and meant to say I want it to sort ascendingly by distance!! I've tried manipulating the code, but have it sorting descendingly (barring the first and last entires strangely) by distance.... so kind of almost there... Done it. Quite a lengthy way of doing it, don't know if it can be done better. Managed to place the misplaced entry at the beginning of the array at the end. Then that left a blank line for some reason, so did a further loop through to cut off the opening line. Can't quite visualise how the array was populating and why, so it ended up as a bit of a tinker fest. Got there in the end though: For x = 0 To (Count - 1) For y = 1 To (Count) If MyArray(x, 7) < MyArray(y, 7) Then For z = 1 To 8 Temp = MyArray(x, z) MyArray(x, z) = MyArray(y, z) MyArray(y, z) = Temp Next z End If Next y Next x MyArray2 = MyArray For x = 1 To (Count) y = 0 For z = 0 To 8 Temp = MyArray(x, z) MyArray(y, z) = Temp Next z y = y + 1 Next x For z = 0 To 8 Temp = MyArray2(0, z) MyArray(Count, z) = Temp Next z y = 0 For x = 1 To (Count - 2) For z = 0 To 8 Temp = MyArray(x, z) MyArray(y, z) = Temp Next z y = y + 1 Next x ListBox1.List = MyArray LetsEnd: |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autosort on an Array
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autosort on an Array
I'll see if I can ping it on over the weekend, I'll have to fudge some
test data into the spreadsheet before I can send it out. Can't send it out with actual data! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autosort in Excel | Excel Discussion (Misc queries) | |||
autosort | Excel Worksheet Functions | |||
Can I AutoSort without refreshing? | Excel Discussion (Misc queries) | |||
Can I AutoSort without refreshing? | Excel Discussion (Misc queries) | |||
AutoSort in VBA | Excel Discussion (Misc queries) |