PDA

View Full Version : Sorting order please help!



m4a1
06-22-2007, 03:07 AM
Hi everyone. I encounter a problem regarding the coding of a function which is designed to sort the (n by 3) matrix by largest value e.g

transform
CHF EUR 0.923126
CHF USD 0.154571
EUR USD 0.254522

into
CHF EUR 0.923126
EUR USD 0.254522
CHF USD 0.154571
here is my coding:

Function Sorting(vector1 As Variant)
Dim n, k, i, j As Integer
Dim temp As Variant
n = vector1.Rows.Count
ReDim temp(1 To n, 1 To 3)
For j = 1 To n
k = n
For i = 1 To n
If (Abs(vector1(j, 3))) > (Abs(vector1(i, 3))) Then
k = k - 1
End If
Next i

temp(k, 1) = vector1(j, 1)
temp(k, 2) = vector1(j, 2)
temp(k, 3) = vector1(j, 3)
Next j
Sorting = temp
End Function
I dont know where went wrong, i m an beginner, please help!!!!!

mdmackillop
06-22-2007, 05:26 AM
Hi,
Welcome to VBAX
An array doesn't have countable rows in that fashion
try

Function Sorting(vector1 As Variant)
Dim n, k, i, j As Integer
Dim temp As Variant
n = UBound(vector1)
ReDim temp(1 To n, 1 To 3)
For j = 1 To n
k = n
For i = 1 To n
If (Abs(vector1(j, 3))) > (Abs(vector1(i, 3))) Then
k = k - 1
End If
Next i
temp(k, 1) = vector1(j, 1)
temp(k, 2) = vector1(j, 2)
temp(k, 3) = vector1(j, 3)
Next j
Sorting = temp
End Function



or, If you are getting data from a range


Sub DoSort()
Dim arr As Range
Set arr = Range("Data")
Rws = arr.Rows.Count
Cells(10, 1).Resize(Rws, 3) = Sorting(arr)
End Sub
Function Sorting(vector1 As Variant)
Dim n, k, i, j As Integer
Dim temp As Variant
n = vector1.Rows.Count
ReDim temp(1 To n, 1 To 3)
For j = 1 To n
k = n
For i = 1 To n
If (Abs(vector1(j, 3))) > (Abs(vector1(i, 3))) Then
k = k - 1
End If
Next i
temp(k, 1) = vector1(j, 1)
temp(k, 2) = vector1(j, 2)
temp(k, 3) = vector1(j, 3)
Next j
Sorting = temp
End Function

m4a1
06-22-2007, 05:19 PM
Hi mdmackillop,

Thanks for your help. i found out the the original codes are ok, the function works ,there was a mistake i made when implementing the function,lol. however, according to my original coding, i spotted a problem, which is that the function doesnt work when two or matrix value are the same. i think in the if statement, i need to make modification. any suggestions?

please see the attachment.

mdmackillop
06-23-2007, 03:07 AM
Function Sorting(vector1 As Variant)
Dim n, k, i, j, m As Integer
Dim temp As Variant
n = vector1.Rows.Count
ReDim temp(1 To n, 1 To 3)

For j = 1 To n
k = n
For i = 1 To n
If (Abs(vector1(j, 3))) > (Abs(vector1(i, 3))) Then
k = k - 1
End If

Next i
'test for prior equal value; if found; enter above
For m = 1 To n
If vector1(j, 3) = temp(m, 3) Then
k = m - 1
Exit For
End If
Next
temp(k, 1) = vector1(j, 1)
temp(k, 2) = vector1(j, 2)
temp(k, 3) = vector1(j, 3)
Next j
Sorting = temp
End Function